WebSVN - OpenConcerto - Rev 185 - /trunk/OpenConcerto/src/org/openconcerto/erp/core/sales/order/element/CommandeClientElementSQLElement.java

/*
 * 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.sales.order.element;

import org.openconcerto.erp.config.ComptaPropsConfiguration;
import org.openconcerto.erp.core.common.element.ComptaSQLConfElement;
import org.openconcerto.erp.core.sales.product.element.ReferenceArticleSQLElement;
import org.openconcerto.erp.core.sales.product.element.UniteVenteArticleSQLElement;
import org.openconcerto.erp.core.supplychain.stock.element.MouvementStockSQLElement;
import org.openconcerto.erp.core.supplychain.stock.element.StockItemsUpdater;
import org.openconcerto.erp.core.supplychain.stock.element.StockItemsUpdater.TypeStockUpdate;
import org.openconcerto.erp.core.supplychain.stock.element.StockLabel;
import org.openconcerto.erp.preferences.GestionArticleGlobalPreferencePanel;
import org.openconcerto.sql.element.SQLComponent;
import org.openconcerto.sql.element.SQLElement;
import org.openconcerto.sql.element.UISQLComponent;
import org.openconcerto.sql.model.AliasedTable;
import org.openconcerto.sql.model.SQLDataSource;
import org.openconcerto.sql.model.SQLInjector;
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.SQLSelect;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.Where;
import org.openconcerto.sql.preferences.SQLPreferences;
import org.openconcerto.sql.request.ListSQLRequest;
import org.openconcerto.sql.request.UpdateBuilder;
import org.openconcerto.sql.sqlobject.ElementComboBox;
import org.openconcerto.sql.users.UserManager;
import org.openconcerto.sql.utils.SQLUtils;
import org.openconcerto.sql.view.EditFrame;
import org.openconcerto.sql.view.EditPanel.EditMode;
import org.openconcerto.sql.view.list.IListe;
import org.openconcerto.sql.view.list.IListeAction.IListeEvent;
import org.openconcerto.sql.view.list.RowAction.PredicateRowAction;
import org.openconcerto.ui.FrameUtil;
import org.openconcerto.utils.DecimalUtils;
import org.openconcerto.utils.ExceptionHandler;
import org.openconcerto.utils.ListMap;

import java.awt.event.ActionEvent;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.swing.AbstractAction;
import javax.swing.JTextField;

public class CommandeClientElementSQLElement extends ComptaSQLConfElement {

    public CommandeClientElementSQLElement() {
        super("COMMANDE_CLIENT_ELEMENT", "un element de commande", "éléments de commande");

        PredicateRowAction rowAction = new PredicateRowAction(new AbstractAction("Transfert vers commande fournisseur") {

            @Override
            public void actionPerformed(ActionEvent e) {
                final List<SQLRow> rows = SQLRowListRSH.fetch(IListe.get(e).getRequest().getPrimaryTable(), IListe.get(e).getSelection().getSelectedIDs());
                ((CommandeClientSQLElement) getForeignElement("ID_COMMANDE_CLIENT")).transfertEltToCommandeF(rows);
            }

        }, true);
        rowAction.setPredicate(IListeEvent.getNonEmptySelectionPredicate());
        getRowActions().add(rowAction);

        PredicateRowAction rowActionBL = new PredicateRowAction(new AbstractAction("Transférer la commande en BL") {

            @Override
            public void actionPerformed(ActionEvent e) {
                List<SQLRowValues> resultId = new ArrayList<>();
                CommandeClientSQLElement cmdElt = (CommandeClientSQLElement) getForeignElement("ID_COMMANDE_CLIENT");
                for (SQLRowAccessor sqlRowAccessor : IListe.get(e).getSelectedRowAccessors()) {
                    resultId.add(sqlRowAccessor.getForeign("ID_COMMANDE_CLIENT").asRowValues());
                }
                cmdElt.transfertBonLivraisonClient(resultId);
            }

        }, true);
        rowActionBL.setPredicate(IListeEvent.getNonEmptySelectionPredicate());
        getRowActions().add(rowActionBL);

        PredicateRowAction rowActionCmd = new PredicateRowAction(new AbstractAction("Modifier la commande associée") {

            @Override
            public void actionPerformed(ActionEvent e) {
                SQLRowAccessor selectedRow = IListe.get(e).getSelectedRowAccessor();
                EditFrame f = new EditFrame(getForeignElement("ID_COMMANDE_CLIENT"), EditMode.MODIFICATION);
                f.getSQLComponent().select(selectedRow.getForeignID("ID_COMMANDE_CLIENT"));
                FrameUtil.showPacked(f);
            }

        }, true);
        rowActionCmd.setPredicate(IListeEvent.getSingleSelectionPredicate());
        getRowActions().add(rowActionCmd);

        if (getTable().getForeignTable("ID_USER_COMMON_CREATE").getRow(UserManager.getUserID()).getBoolean("ADMIN")) {
            PredicateRowAction rowActionForceLivr = new PredicateRowAction(new AbstractAction("Forcer la livraison") {

                @Override
                public void actionPerformed(ActionEvent e) {
                    updateForceLivrer(e, Boolean.TRUE);

                }

            }, true);
            rowActionForceLivr.setPredicate(IListeEvent.getNonEmptySelectionPredicate());
            getRowActions().add(rowActionForceLivr);

            PredicateRowAction rowActionAnnuler = new PredicateRowAction(new AbstractAction("Annuler Forcer la livraison") {

                @Override
                public void actionPerformed(ActionEvent e) {
                    updateForceLivrer(e, Boolean.FALSE);

                }

            }, true);
            rowActionAnnuler.setPredicate(IListeEvent.getNonEmptySelectionPredicate());
            getRowActions().add(rowActionAnnuler);

            PredicateRowAction rowActionUpdateStockTh = new PredicateRowAction(new AbstractAction("Recalculer le stock théorique") {

                @Override
                public void actionPerformed(ActionEvent e) {
                    recalculStockTh(null);

                }

            }, true);
            rowActionUpdateStockTh.setPredicate(IListeEvent.createTotalRowCountPredicate(0, Integer.MAX_VALUE));
            getRowActions().add(rowActionUpdateStockTh);

        }

    }

    private void updateForceLivrer(ActionEvent e, Boolean state) {
        final List<SQLRowAccessor> selectedRows = IListe.get(e).getSelectedRowAccessors();
        final Set<Integer> ids = new HashSet<Integer>();
        final Set<Integer> idsArticle = new HashSet<Integer>();
        SQLDataSource ds = null;
        for (SQLRowAccessor sqlRowValues : selectedRows) {
            ds = sqlRowValues.getTable().getDBSystemRoot().getDataSource();
            ids.add(sqlRowValues.getID());
            if (sqlRowValues.getNonEmptyForeign("ID_ARTICLE") != null) {
                idsArticle.add(sqlRowValues.getForeignID("ID_ARTICLE"));
            }
        }
        try {
            SQLUtils.executeAtomic(ds, new SQLUtils.SQLFactory<Object>() {
                @Override
                public Object create() throws SQLException {
                    UpdateBuilder build = new UpdateBuilder(getTable());
                    build.setObject("LIVRE_FORCED", state);
                    build.setWhere(new Where(getTable().getKey(), ids));
                    getTable().getDBSystemRoot().getDataSource().execute(build.asString());

                    if (!idsArticle.isEmpty()) {
                        recalculStockTh(idsArticle);
                    }
                    for (Integer i : ids) {
                        getTable().fireTableModified(i);
                    }
                    return null;
                }

            });
        } catch (SQLException e1) {
            ExceptionHandler.handle("Erreur lors du recalcul des stocks théoriques", e1);
        }
    }

    public void recalculStockTh(Collection<Integer> articles) {
        if (articles != null && articles.isEmpty()) {
            return;
        }
        // RAZ des stocks TH --> TH = REEL
        final SQLTable tableStock = getTable().getDBRoot().findTable("STOCK");
        String req = "UPDATE " + tableStock.getSQLName().quote() + " s SET " + tableStock.getField("QTE_TH").getQuotedName() + " = " + tableStock.getField("QTE_REEL").getQuotedName() + ","
                + tableStock.getField("QTE_RECEPT_ATTENTE").getQuotedName() + " = 0," + tableStock.getField("QTE_LIV_ATTENTE").getQuotedName() + "=0";
        if (articles != null && !articles.isEmpty()) {
            req += " WHERE " + tableStock.getField("ARCHIVE").getQuotedName() + "=0  AND " + Where.inValues(new AliasedTable(tableStock, "s").getField("ID_ARTICLE"), articles).getClause();
        }
        tableStock.getDBSystemRoot().getDataSource().execute(req);

        {
            SQLSelect selCmdElt = new SQLSelect();
            final SQLTable tableCmdElt = tableStock.getTable("COMMANDE_ELEMENT");
            selCmdElt.addSelectStar(tableCmdElt);
            Where w = new Where(tableCmdElt.getField("RECU_FORCED"), "=", Boolean.FALSE).and(new Where(tableCmdElt.getField("RECU"), "=", Boolean.FALSE));
            if (articles != null && !articles.isEmpty()) {
                w = w.and(Where.inValues(tableCmdElt.getField("ID_ARTICLE"), articles));
            }
            w = w.and(Where.createRaw(
                    tableCmdElt.getField("QTE_RECUE").getQuotedName() + " < (" + tableCmdElt.getField("QTE").getQuotedName() + "*" + tableCmdElt.getField("QTE_UNITAIRE").getQuotedName() + ")",
                    tableCmdElt.getField("QTE_UNITAIRE"), tableCmdElt.getField("QTE"), tableCmdElt.getField("QTE_RECUE")));

            selCmdElt.setWhere(w);
            List<SQLRow> res = SQLRowListRSH.execute(selCmdElt);
            if (res != null && res.size() > 0) {
                StockItemsUpdater updater = new StockItemsUpdater(new StockLabel() {

                    @Override
                    public String getLabel(SQLRowAccessor rowOrigin, SQLRowAccessor rowElt) {
                        return "Commande fournisseur N°" + rowElt.getForeign("ID_COMMANDE").getString("NUMERO");
                    }
                }, res.get(0), res, TypeStockUpdate.VIRTUAL_RECEPT, false);
                try {
                    updater.update();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
        {
            SQLSelect selCmdElt = new SQLSelect();
            final SQLTable tableCmdElt = tableStock.getTable("COMMANDE_CLIENT_ELEMENT");
            selCmdElt.addSelectStar(tableCmdElt);
            Where w = new Where(tableCmdElt.getField("LIVRE_FORCED"), "=", Boolean.FALSE).and(new Where(tableCmdElt.getField("LIVRE"), "=", Boolean.FALSE));
            w = w.and(Where.createRaw(
                    tableCmdElt.getField("QTE_LIVREE").getQuotedName() + " < (" + tableCmdElt.getField("QTE").getQuotedName() + "*" + tableCmdElt.getField("QTE_UNITAIRE").getQuotedName() + ")",
                    tableCmdElt.getField("QTE_UNITAIRE"), tableCmdElt.getField("QTE"), tableCmdElt.getField("QTE_LIVREE")));
            if (articles != null && !articles.isEmpty()) {
                w = w.and(Where.inValues(tableCmdElt.getField("ID_ARTICLE"), articles));
            }
            selCmdElt.setWhere(w);
            List<SQLRow> res = SQLRowListRSH.execute(selCmdElt);
            if (res != null && res.size() > 0) {
                StockItemsUpdater updater = new StockItemsUpdater(new StockLabel() {

                    @Override
                    public String getLabel(SQLRowAccessor rowOrigin, SQLRowAccessor rowElt) {
                        return "Commande N°" + rowElt.getForeign("ID_COMMANDE_CLIENT").getString("NUMERO");
                    }
                }, res.get(0), res, TypeStockUpdate.VIRTUAL_DELIVER, false);
                try {
                    updater.update();
                } catch (

                SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }

    @Override
    protected String getParentFFName() {
        return "ID_COMMANDE_CLIENT";
    }

    protected List<String> getListFields() {
        final List<String> l = new ArrayList<String>();
        l.add("CODE");
        l.add("NOM");
        SQLPreferences prefs = new SQLPreferences(getTable().getDBRoot());
        if (prefs.getBoolean(GestionArticleGlobalPreferencePanel.ACTIVER_DECLINAISON, false)) {

            for (String fieldName : getTable().getFieldsName()) {
                if (fieldName.startsWith("ID_ARTICLE_DECLINAISON_")) {
                    l.add(fieldName);
                }
            }
        }
        l.add("ID_COMMANDE_CLIENT");
        l.add("ID_ARTICLE");
        l.add("ID_DEPOT_STOCK");
        l.add("PA_HT");
        l.add("PV_HT");
        l.add("T_PA_HT");
        l.add("T_PV_HT");
        l.add("T_PV_TTC");
        l.add("QTE");
        l.add("QTE_UNITAIRE");
        l.add("QTE_LIVREE");
        l.add("LIVRE");
        l.add("LIVRE_FORCED");
        return l;
    }

    /**
     * Transfert d'une commande en commande fournisseur
     * 
     * @param commandeID
     */
    public void transfertCommande(List<SQLRowAccessor> commandeClientEltsRows) {

        ComptaPropsConfiguration.getInstanceCompta().getNonInteractiveSQLExecutor().execute(new Runnable() {

            @Override
            public void run() {

                SQLTable tableCmdElt = getDirectory().getElement("COMMANDE_ELEMENT").getTable();
                SQLElement eltArticle = getDirectory().getElement("ARTICLE");

                final ListMap<SQLRow, SQLRowValues> map = new ListMap<SQLRow, SQLRowValues>();
                List<String> fields2copy = Arrays.asList("CODE", "NOM", "VALEUR_METRIQUE_1", "VALEUR_METRIQUE_2", "VALEUR_METRIQUE_3");

                Set<Integer> artAdded = new HashSet<Integer>();
                for (SQLRowAccessor sqlRow : commandeClientEltsRows) {
                    boolean article = false;
                    if (sqlRow.getTable().getName().equalsIgnoreCase("ARTICLE")) {
                        article = true;
                    }

                    SQLRowAccessor rowArticleFind;
                    int qte = 1;
                    BigDecimal qteUV = BigDecimal.ONE;
                    boolean gestionStock = true;
                    if (!article) {
                        SQLRowValues rowArticle;
                        if (sqlRow.contains("ID_ARTICLE") && sqlRow.getNonEmptyForeign("ID_ARTICLE") != null) {
                            // on récupére l'article qui lui correspond
                            rowArticleFind = sqlRow.asRow().getForeign("ID_ARTICLE");
                            rowArticle = rowArticleFind.asRowValues();
                        } else {
                            // on récupére l'article qui lui correspond
                            rowArticle = new SQLRowValues(eltArticle.getTable());
                            for (String field : fields2copy) {
                                rowArticle.put(field, sqlRow.asRow().getObject(field));
                            }
                            int idArticle = ReferenceArticleSQLElement.getIdForCNM(rowArticle, true);
                            rowArticleFind = eltArticle.getTable().getRow(idArticle);
                        }
                        gestionStock = rowArticleFind.getBoolean("GESTION_STOCK");
                        if (!gestionStock) {
                            qte = sqlRow.getInt("QTE");
                            qteUV = sqlRow.getBigDecimal("QTE_UNITAIRE");

                            if (sqlRow.getObject("ID_UNITE_VENTE") != null && sqlRow.getForeignID("ID_UNITE_VENTE") != UniteVenteArticleSQLElement.A_LA_PIECE) {
                                qteUV = qteUV.multiply(new BigDecimal(qte));
                                qte = 1;
                            }
                        } else {
                            if (rowArticle.getForeign("ID_STOCK") != null && !rowArticle.isForeignEmpty("ID_STOCK")) {
                                SQLRowAccessor rowStock = rowArticle.getForeign("ID_STOCK");
                                qte = -Math.round(rowStock.getFloat("QTE_TH") - rowStock.getFloat("QTE_MIN"));
                            }
                        }
                    } else {
                        rowArticleFind = sqlRow;
                        if (sqlRow.getForeign("ID_STOCK") != null && !sqlRow.isForeignEmpty("ID_STOCK")) {
                            SQLRowAccessor rowStock = sqlRow.getForeign("ID_STOCK");
                            qte = -Math.round(rowStock.getFloat("QTE_TH") - rowStock.getFloat("QTE_MIN"));
                        }
                    }
                    if (rowArticleFind != null && !rowArticleFind.isUndefined() && (!gestionStock || !artAdded.contains(rowArticleFind.getID()))) {

                        artAdded.add(rowArticleFind.getID());

                        SQLInjector inj = SQLInjector.getInjector(eltArticle.getTable(), tableCmdElt);
                        SQLRowValues rowValsElt = new SQLRowValues(inj.createRowValuesFrom(rowArticleFind.asRow()));

                        rowValsElt.put("QTE", qte);
                        rowValsElt.put("QTE_UNITAIRE", qteUV);

                        rowValsElt.put("T_POIDS", rowValsElt.getLong("POIDS") * rowValsElt.getInt("QTE"));
                        rowValsElt.put("T_PA_HT", ((BigDecimal) rowValsElt.getObject("PA_HT")).multiply(new BigDecimal(rowValsElt.getInt("QTE")), DecimalUtils.HIGH_PRECISION));
                        rowValsElt.put("T_PA_TTC", ((BigDecimal) rowValsElt.getObject("T_PA_HT")).multiply(new BigDecimal((rowValsElt.getForeign("ID_TAXE").getFloat("TAUX") / 100.0 + 1.0)),
                                DecimalUtils.HIGH_PRECISION));
                        map.add(rowArticleFind.asRow().getForeignRow("ID_FOURNISSEUR"), rowValsElt);
                    }
                }

                MouvementStockSQLElement.createCommandeF(map, null, "");
            }
        });
    }

    protected List<String> getComboFields() {
        final List<String> l = new ArrayList<String>();
        if (getTable().contains("ID_ARTICLE")) {
            l.add("ID_ARTICLE");
        }
        l.add("NOM");
        l.add("PV_HT");
        return l;
    }

    @Override
    protected void _initListRequest(ListSQLRequest req) {
        super._initListRequest(req);
        req.addToGraphToFetch("ID_DEPOT_STOCK");
    }

    @Override
    public ListMap<String, String> getShowAs() {
        final ListMap<String, String> res = new ListMap<String, String>();
        res.putCollection("ID_COMMANDE_CLIENT", "NUMERO", "DATE", "DATE_LIVRAISON_PREV", "ID_CLIENT");

        if (getTable().contains("ID_ARTICLE")) {
            res.putCollection("ID_ARTICLE", "GESTION_STOCK", "ID_FAMILLE_ARTICLE", "ID_FOURNISSEUR");
        }
        res.putCollection(null, "NOM");
        return res;
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.openconcerto.devis.SQLElement#getComponent()
     */
    public SQLComponent createComponent() {
        return new UISQLComponent(this) {
            public void addViews() {
                this.addRequiredSQLObject(new JTextField(), "NOM", "left");
                this.addRequiredSQLObject(new JTextField(), "CODE", "right");

                this.addSQLObject(new ElementComboBox(), "ID_STYLE", "left");

                this.addRequiredSQLObject(new JTextField(), "PA_HT", "left");
                this.addSQLObject(new JTextField(), "PV_HT", "right");

                this.addSQLObject(new JTextField(), "POIDS", "left");
                this.addSQLObject(new ElementComboBox(), "ID_TAXE", "right");
            }
        };
    }

    @Override
    protected String createCode() {
        return createCodeOfPackage() + ".item";
    }
}
Dépôt officiel du code source de l'ERP OpenConcerto

svn://code.openconcerto.org/openconcerto

(root)/trunk/OpenConcerto/src/org/openconcerto/erp/core/sales/order/element/CommandeClientElementSQLElement.java @ 180 - Rev 185
