OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 142 | Rev 174 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

/*
 * 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.supplychain.stock.element;

import org.openconcerto.erp.core.sales.product.model.ProductComponent;
import org.openconcerto.erp.core.sales.product.model.ProductHelper;
import org.openconcerto.erp.core.supplychain.stock.element.StockItem.TypeStockMouvement;
import org.openconcerto.sql.model.ConnectionHandlerNoSetup;
import org.openconcerto.sql.model.DBRoot;
import org.openconcerto.sql.model.SQLDataSource;
import org.openconcerto.sql.model.SQLRow;
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.sql.utils.SQLUtils;
import org.openconcerto.utils.DecimalUtils;
import org.openconcerto.utils.ExceptionHandler;
import org.openconcerto.utils.ListMap;
import org.openconcerto.utils.RTInterruptedException;
import org.openconcerto.utils.Tuple3;
import org.openconcerto.utils.cc.ITransformer;

import java.awt.GraphicsEnvironment;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.swing.JOptionPane;
import javax.swing.SwingUtilities;

import org.apache.commons.dbutils.ResultSetHandler;

public class StockItemsUpdater {

    private final StockLabel label;
    private final List<? extends SQLRowAccessor> items;
    private final TypeStockUpdate type;
    private final boolean createMouvementStock;
    private final SQLRowAccessor rowSource;
    private boolean resetStockTH = false;

    private boolean headless = false;

    public static enum TypeStockUpdate {

        VIRTUAL_RECEPT(true, TypeStockMouvement.THEORIQUE), REAL_RECEPT(true, TypeStockMouvement.REEL), VIRTUAL_DELIVER(false, TypeStockMouvement.THEORIQUE), REAL_DELIVER(false,
                TypeStockMouvement.REEL), REAL_VIRTUAL_RECEPT(true,
                        TypeStockMouvement.REEL_THEORIQUE), RETOUR_AVOIR_CLIENT(true, TypeStockMouvement.RETOUR), REAL_VIRTUAL_DELIVER(false, TypeStockMouvement.REEL_THEORIQUE);

        private final boolean entry;
        private final TypeStockMouvement type;

        /**
         * 
         * @param entry
         */
        TypeStockUpdate(boolean entry, TypeStockMouvement type) {
            this.entry = entry;
            this.type = type;
        }

        public boolean isEntry() {
            return entry;
        }

        public TypeStockMouvement getType() {
            return type;
        }
    };

    public StockItemsUpdater(StockLabel label, SQLRowAccessor rowSource, List<? extends SQLRowAccessor> items, TypeStockUpdate t) {
        this(label, rowSource, items, t, true);
    }

    public StockItemsUpdater(StockLabel label, SQLRowAccessor rowSource, List<? extends SQLRowAccessor> items, TypeStockUpdate t, boolean createMouvementStock) {
        this.label = label;
        this.items = items;
        this.type = t;
        this.createMouvementStock = createMouvementStock;
        this.rowSource = rowSource;
        this.headless = GraphicsEnvironment.isHeadless();
    }

    public void setHeadless(boolean headless) {
        this.headless = headless;
    }

    List<Tuple3<SQLRowAccessor, Integer, BigDecimal>> reliquat = new ArrayList<Tuple3<SQLRowAccessor, Integer, BigDecimal>>();

    public void addReliquat(SQLRowAccessor article, int qte, BigDecimal qteUnit) {
        reliquat.add(Tuple3.create(article, qte, qteUnit));
    }

    List<String> requests = new ArrayList<String>();

    public void update() throws SQLException {
        final SQLTable stockTable = this.rowSource.getTable().getTable("STOCK");

        if (this.createMouvementStock) {
            clearExistingMvt(this.rowSource);
        }

        // Mise à jour des stocks des articles non composés
        List<StockItem> stockItems = fetch();

        final ListMap<SQLRow, SQLRowValues> cmd = new ListMap<SQLRow, SQLRowValues>();

        for (StockItem stockItem : stockItems) {

            if (stockItem.isStockInit()) {
                requests.add(stockItem.getUpdateRequest());
            } else {
                SQLRowValues rowVals = new SQLRowValues(stockTable);
                rowVals.put("QTE_REEL", stockItem.getRealQty());
                rowVals.put("QTE_TH", stockItem.getVirtualQty());
                rowVals.put("QTE_LIV_ATTENTE", stockItem.getDeliverQty());
                rowVals.put("QTE_RECEPT_ATTENTE", stockItem.getReceiptQty());
                rowVals.put("ID_ARTICLE", stockItem.getArticle().getID());
                rowVals.put("ID_DEPOT_STOCK", stockItem.stock.getForeignID("ID_DEPOT_STOCK"));
                rowVals.commit();
                if (stockItem.getArticle().getForeignID("ID_DEPOT_STOCK") == stockItem.stock.getForeignID("ID_DEPOT_STOCK")) {
                    SQLRowValues rowValsArt = stockItem.getArticle().createEmptyUpdateRow();
                    rowValsArt.put("ID_STOCK", rowVals);
                    rowValsArt.commit();
                }
            }
            if (!this.type.isEntry()) {
                stockItem.fillCommandeFournisseur(cmd);
            }
        }

        final List<? extends ResultSetHandler> handlers = new ArrayList<ResultSetHandler>(requests.size());
        for (String s : requests) {
            handlers.add(null);
        }
        // FIXME FIRE TABLE CHANGED TO UPDATE ILISTE ??
        try {
            SQLUtils.executeAtomic(stockTable.getDBSystemRoot().getDataSource(), new ConnectionHandlerNoSetup<Object, IOException>() {
                @Override
                public Object handle(SQLDataSource ds) throws SQLException, IOException {
                    SQLUtils.executeMultiple(stockTable.getDBSystemRoot(), requests, handlers);
                    return null;
                }
            });
        } catch (IOException e) {
            ExceptionHandler.handle("Erreur de la mise à jour des stocks!", e);
        }

        final DBRoot root = this.rowSource.getTable().getDBRoot();
        if (root.contains("ARTICLE_ELEMENT")) {
            // Mise à jour des stocks des nomenclatures
            ComposedItemStockUpdater comp = new ComposedItemStockUpdater(root, stockItems);
            comp.update();
        }

        // FIXME Créer une interface de saisie de commande article en dessous du seuil mini de stock
        if (!headless && cmd.size() > 0) {
            String msg = "Les articles suivants sont inférieurs au stock minimum : \n";
            for (SQLRow row : cmd.keySet()) {
                for (SQLRowValues rowVals : cmd.get(row)) {
                    msg += rowVals.getString("CODE") + " " + rowVals.getString("NOM") + "\n";
                }
            }
            final String msgFinal = msg;
            SwingUtilities.invokeLater(new Runnable() {

                @Override
                public void run() {
                    JOptionPane.showMessageDialog(null, msgFinal, "Alerte de stock minimum", JOptionPane.WARNING_MESSAGE);
                }
            });
        }

    }

    /**
     * Suppression des anciens mouvements
     * 
     * @param rowSource
     * @throws SQLException
     * @throws RTInterruptedException
     */
    private void clearExistingMvt(SQLRowAccessor rowSource) throws RTInterruptedException, SQLException {

        List<String> multipleRequests = new ArrayList<String>();

        final SQLTable table = this.rowSource.getTable().getTable("MOUVEMENT_STOCK");
        SQLRowValues rowVals = new SQLRowValues(table);
        rowVals.put("QTE", null);
        rowVals.put("REEL", null);
        SQLRowValues rowValsArt = new SQLRowValues(this.rowSource.getTable().getTable("ARTICLE"));
        SQLRowValues rowValsStock = new SQLRowValues(this.rowSource.getTable().getTable("STOCK"));
        rowValsStock.put("QTE_REEL", null);
        rowValsStock.put("QTE_TH", null);
        rowValsStock.put("QTE_RECEPT_ATTENTE", null);
        rowValsStock.put("QTE_LIV_ATTENTE", null);

        rowVals.put("ID_STOCK", rowValsStock);
        rowVals.put("ID_ARTICLE", rowValsArt);

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

            @Override
            public SQLSelect transformChecked(SQLSelect input) {
                Where w = new Where(table.getField("SOURCE"), "=", StockItemsUpdater.this.rowSource.getTable().getName());
                w = w.and(new Where(table.getField("IDSOURCE"), "=", StockItemsUpdater.this.rowSource.getID()));
                input.setWhere(w);
                return input;
            }
        });

        // On stocke les items pour le calcul total des stocks (sinon le calcul est faux si
        // l'article apparait plusieurs fois
        // ou si
        // on archive un mvt reel et theorique)
        Map<Number, StockItem> items = new HashMap<Number, StockItem>();
        List<SQLRowValues> result = fetcher.fetch();
        for (SQLRowValues sqlRowValues : result) {
            final StockItem item;
            if (!items.containsKey(sqlRowValues.getForeignIDNumber("ID_STOCK"))) {
                item = new StockItem(sqlRowValues.getForeign("ID_ARTICLE"), sqlRowValues.getForeign("ID_STOCK"));
                items.put(sqlRowValues.getForeignIDNumber("ID_STOCK"), item);
            } else {
                item = items.get(sqlRowValues.getForeignIDNumber("ID_STOCK"));
            }
            final TypeStockMouvement t;
            if (sqlRowValues.getBoolean("REEL")) {
                t = TypeStockMouvement.REEL;
            } else {
                t = TypeStockMouvement.THEORIQUE;
            }
            item.updateQty(sqlRowValues.getFloat("QTE"), t, true);
            String req = "UPDATE " + sqlRowValues.getTable().getSQLName().quote() + " SET \"ARCHIVE\"=1 WHERE \"ID\"=" + sqlRowValues.getID();
            multipleRequests.add(req);
            multipleRequests.add(item.getUpdateRequest());
        }

        List<? extends ResultSetHandler> handlers = new ArrayList<ResultSetHandler>(multipleRequests.size());
        for (String s : multipleRequests) {
            handlers.add(null);
        }
        SQLUtils.executeMultiple(table.getDBSystemRoot(), multipleRequests, handlers);
    }

    private void fillProductComponent(List<ProductComponent> productComponents, int qte, int index, int level) {
        if (level > 0) {
            for (int i = index; i < items.size(); i++) {
                SQLRowAccessor r = items.get(i);

                if (!r.getTable().contains("NIVEAU") || r.getInt("NIVEAU") >= level) {
                    // On ne calcul pas les stocks pour les éléments ayant des fils (le mouvement de
                    // stock
                    // des fils impactera les stocks automatiquement)
                    if (r.getTable().contains("NIVEAU")) {
                        if (i + 1 < items.size()) {
                            SQLRowAccessor rNext = items.get(i + 1);
                            if (rNext.getInt("NIVEAU") > r.getInt("NIVEAU")) {
                                fillProductComponent(productComponents, qte * r.getInt("QTE"), i + 1, rNext.getInt("NIVEAU"));
                                continue;
                            }
                        }
                    }
                    if ((!r.getTable().contains("NIVEAU") || r.getInt("NIVEAU") == level) && !r.isForeignEmpty("ID_ARTICLE") && r.getForeign("ID_ARTICLE") != null) {
                        productComponents.add(ProductComponent.createFrom(r, qte, r));
                    }
                } else if (r.getInt("NIVEAU") < level) {
                    // BREAK si on sort de l'article composé
                    break;
                }
            }
        }
    }

    /**
     * Récupére les stocks associés aux articles non composés (inclus les fils des nomenclatures) et
     * les met à jour
     * 
     * @return la liste des stocks à jour
     */
    private List<StockItem> fetch() {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Map<Number, StockItem> stockItems = new HashMap<Number, StockItem>();

        String mvtStockTableQuoted = rowSource.getTable().getTable("MOUVEMENT_STOCK").getSQLName().quote();

        // Liste des éléments à mettre à jour
        List<ProductComponent> productComponents = new ArrayList<ProductComponent>();
        fillProductComponent(productComponents, 1, 0, 1);
        // for (int i = 0; i < items.size(); i++) {
        // SQLRowAccessor r = items.get(i);
        //
        // // On ne calcul pas les stocks pour les éléments ayant des fils (le mouvement de stock
        // // des fils impactera les stocks automatiquement)
        // if (r.getTable().contains("NIVEAU")) {
        // if (i + 1 < items.size()) {
        // SQLRowAccessor rNext = items.get(i + 1);
        // if (rNext.getInt("NIVEAU") > r.getInt("NIVEAU")) {
        // continue;
        // }
        // }
        // }
        // if (!r.isForeignEmpty("ID_ARTICLE")) {
        // productComponents.add(ProductComponent.createFrom(r));
        // }
        // }

        // Liste des articles non composés à mettre à jour (avec les fils des nomenclatures)
        ProductHelper helper = new ProductHelper(rowSource.getTable().getDBRoot());
        List<ProductComponent> boms = helper.getChildWithQtyFrom(productComponents);

        for (ProductComponent productComp : boms) {

            if (productComp.getProduct().getBoolean("GESTION_STOCK") && productComp.getQty().signum() != 0) {
                final StockItem stockItem;
                if (!stockItems.containsKey(productComp.getStock().getID())) {
                    stockItem = new StockItem(productComp.getProduct(), productComp.getStock());
                    stockItems.put(productComp.getStock().getID(), stockItem);
                } else {
                    stockItem = stockItems.get(productComp.getStock().getID());
                }
                double qteFinal = productComp.getQty().doubleValue();

                // reliquat
                for (Tuple3<SQLRowAccessor, Integer, BigDecimal> t : reliquat) {
                    if (stockItem.getArticle() != null && stockItem.getArticle().equalsAsRow(t.get0())) {
                        double qteFinalReliquat = t.get2().multiply(new BigDecimal(t.get1()), DecimalUtils.HIGH_PRECISION).doubleValue();
                        qteFinal -= qteFinalReliquat;
                    }
                }
                if (!this.type.isEntry()) {
                    qteFinal = -qteFinal;
                }

                stockItem.updateQty(qteFinal, this.type.getType());

                if (this.createMouvementStock) {
                    final Date time = this.rowSource.getDate("DATE").getTime();
                    BigDecimal prc = productComp.getPRC(time);
                    if (this.type.getType() == TypeStockMouvement.REEL || this.type.getType() == TypeStockMouvement.REEL_THEORIQUE || this.type.getType() == TypeStockMouvement.RETOUR) {
                        String mvtStockQuery = "INSERT INTO " + mvtStockTableQuoted + " (\"QTE\",\"DATE\",\"ID_ARTICLE\",\"ID_STOCK\",\"SOURCE\",\"IDSOURCE\",\"NOM\",\"REEL\",\"ORDRE\"";

                        if (prc != null) {
                            mvtStockQuery += ",\"PRICE\"";
                        }

                        mvtStockQuery += ") VALUES(" + qteFinal + ",'" + dateFormat.format(time) + "'," + productComp.getProduct().getID() + "," + productComp.getStock().getID() + ",'"
                                + this.rowSource.getTable().getName() + "'," + this.rowSource.getID() + ",'" + this.label.getLabel(this.rowSource, productComp.getProduct())
                                + "',true, (SELECT (MAX(\"ORDRE\")+1) FROM " + mvtStockTableQuoted + ")";
                        if (prc != null) {
                            mvtStockQuery += "," + prc.setScale(6, RoundingMode.HALF_UP).toString();
                        }
                        mvtStockQuery += ")";
                        this.requests.add(mvtStockQuery);
                    }
                    if (this.type.getType() == TypeStockMouvement.THEORIQUE || this.type.getType() == TypeStockMouvement.REEL_THEORIQUE || this.type.getType() == TypeStockMouvement.RETOUR) {
                        String mvtStockQuery = "INSERT INTO " + mvtStockTableQuoted + " (\"QTE\",\"DATE\",\"ID_ARTICLE\",\"ID_STOCK\",\"SOURCE\",\"IDSOURCE\",\"NOM\",\"REEL\",\"ORDRE\"";
                        if (prc != null) {
                            mvtStockQuery += ",\"PRICE\"";
                        }

                        mvtStockQuery += ") VALUES(" + qteFinal + ",'" + dateFormat.format(time) + "'," + productComp.getProduct().getID() + "," + productComp.getStock().getID() + ",'"
                                + this.rowSource.getTable().getName() + "'," + this.rowSource.getID() + ",'" + this.label.getLabel(this.rowSource, productComp.getProduct())
                                + "',false, (SELECT (MAX(\"ORDRE\")+1) FROM " + mvtStockTableQuoted + ")";
                        if (prc != null) {
                            mvtStockQuery += "," + prc.setScale(6, RoundingMode.HALF_UP).toString();
                        }
                        mvtStockQuery += ")";
                        this.requests.add(mvtStockQuery);
                    }
                }
            }
        }

        return new ArrayList<StockItem>(stockItems.values());
    }

    public void setResetStockTH(boolean resetStockTH) {
        this.resetStockTH = resetStockTH;
    }
}