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.sales.product.model;

import org.openconcerto.erp.core.sales.product.model.ProductHelper.SupplierPriceField;
import org.openconcerto.erp.core.supplychain.stock.element.DepotStockSQLElement;
import org.openconcerto.sql.Configuration;
import org.openconcerto.sql.model.SQLField;
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.SQLTable;
import org.openconcerto.sql.model.Where;
import org.openconcerto.utils.DecimalUtils;
import org.openconcerto.utils.ExceptionHandler;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.List;

public class ProductComponent {
    private final SQLRowAccessor product;
    private final SQLRowAccessor source;
    private final SQLRowAccessor stock;
    private BigDecimal qty;
    private final ProductHelper helper;

    public ProductComponent(SQLRowAccessor product, BigDecimal qty, SQLRowAccessor source, SQLRowAccessor stock) {
        this.product = product;
        this.qty = qty;
        this.helper = new ProductHelper(product.getTable().getDBRoot());
        this.source = source;
        this.stock = stock;
    }

    public SQLRowAccessor getProduct() {
        return product;
    }

    public SQLRowAccessor getSource() {
        return source;
    }

    public SQLRowAccessor getStock() {
        return stock;
    }

    public BigDecimal getQty() {
        return qty;
    }

    public void setQty(BigDecimal qty) {
        this.qty = qty;
    }

    public void addQty(BigDecimal b) {
        this.qty = qty.add(b);
    }

    /**
     * permet de valoriser les mouvements de stocks
     * 
     * @return
     */
    public BigDecimal getPRC(Date d) {
        if (product.getTable().getDBRoot().contains("ARTICLE_PRIX_REVIENT")) {
            BigDecimal result = null;
            {
                SQLTable table = product.getTable().getDBRoot().getTable("ARTICLE_PRIX_REVIENT");
                Collection<SQLRow> prcs = product.asRow().getReferentRows(table);

                Date lastDate = null;
                final List<PriceByQty> prices = new ArrayList<PriceByQty>();
                for (SQLRow row : prcs) {
                    Calendar date = Calendar.getInstance();
                    if (row.getObject("DATE") != null) {
                        date = row.getDate("DATE");
                        lastDate = date.getTime();
                    }
                    prices.add(new PriceByQty(row.getLong("QTE"), row.getBigDecimal("PRIX"), date.getTime()));
                }

                result = PriceByQty.getPriceForQty(qty.setScale(0, RoundingMode.HALF_UP).intValue(), prices, d);
                if (result == null) {
                    result = PriceByQty.getPriceForQty(qty.setScale(0, RoundingMode.HALF_UP).intValue(), prices, lastDate);
                } else if (prices.size() > 0) {
                    result = prices.get(0).getPrice();
                }
            }
            if (result == null) {
                SQLTable tableATF = product.getTable().getDBRoot().getTable("ARTICLE_TARIF_FOURNISSEUR");
                Collection<SQLRow> atfs = product.asRow().getReferentRows(tableATF);

                Date lastDateATF = null;
                final List<PriceByQty> pricesATF = new ArrayList<PriceByQty>();
                for (SQLRow row : atfs) {
                    Calendar date = Calendar.getInstance();
                    if (row.getObject("DATE_PRIX") != null) {
                        date = row.getDate("DATE_PRIX");
                        lastDateATF = date.getTime();
                    }
                    pricesATF.add(new PriceByQty(row.getLong("QTE"), this.helper.getEnumPrice(row, SupplierPriceField.COEF_TRANSPORT_SIEGE), date.getTime()));
                }

                result = PriceByQty.getPriceForQty(qty.setScale(0, RoundingMode.HALF_UP).intValue(), pricesATF, d);
                if (result == null) {
                    result = PriceByQty.getPriceForQty(qty.setScale(0, RoundingMode.HALF_UP).intValue(), pricesATF, lastDateATF);
                    if (result == null) {
                        // Can occur during editing
                        result = BigDecimal.ZERO;
                    }
                }
            }

            return result;
        }
        return null;
    }

    public static ProductComponent createFromRowArticle(SQLRowAccessor rowArticle, SQLRowAccessor rowValsSource) {
        SQLRowAccessor rowStock = getStock(rowArticle, rowArticle, rowValsSource);

        return new ProductComponent(rowArticle, BigDecimal.ONE, rowValsSource, rowStock);
    }

    public static ProductComponent createFrom(SQLRowAccessor rowVals) {
        return createFrom(rowVals, 1, rowVals);
    }

    public static ProductComponent createFrom(SQLRowAccessor rowVals, SQLRowAccessor rowValsSource) {
        return createFrom(rowVals, 1, rowValsSource);
    }

    public static ProductComponent createFrom(SQLRowAccessor rowVals, int qteMultiple, SQLRowAccessor rowValsSource) {

        if (rowVals.getForeign("ID_ARTICLE") == null || rowVals.isForeignEmpty("ID_ARTICLE")) {
            throw new IllegalArgumentException("Aucun article associé à la row " + rowVals.getTable().getName() + " " + rowVals.getID());
        }
        final int qteMult = (rowVals.getTable().getName().equalsIgnoreCase("BON_DE_LIVRAISON_ELEMENT") ? rowVals.getInt("QTE_LIVREE") : rowVals.getInt("QTE"));
        final int qte = qteMult * qteMultiple;
        final BigDecimal qteUV = rowVals.getBigDecimal("QTE_UNITAIRE");
        BigDecimal qteFinal = qteUV.multiply(new BigDecimal(qte), DecimalUtils.HIGH_PRECISION);
        SQLRowAccessor rowStock = getStock(rowVals.getForeign("ID_ARTICLE"), rowVals, rowValsSource);

        // }
        // else {
        // rowStock = rowVals.getForeign("ID_ARTICLE").getForeign("ID_STOCK");
        // }
        return new ProductComponent(rowVals.getForeign("ID_ARTICLE"), qteFinal, rowValsSource, rowStock);
        // return new ProductComponent(rowVals.getForeign("ID_ARTICLE"), qteFinal);
    }

    private static SQLRowAccessor getStock(SQLRowAccessor rowValsProduct, SQLRowAccessor rowValsElt, SQLRowAccessor rowValsSource) {
        SQLRowAccessor rowStock = null;
        int idDepot = -1;

        if (rowValsProduct.getTable().getDBRoot().contains("AFFAIRE")) {
            SQLTable tableAff = rowValsProduct.getTable().getTable("AFFAIRE");
            if (tableAff.contains("ID_DEPOT_STOCK")) {
                if (rowValsSource.getFields().contains("ID_DEPOT_STOCK") && !rowValsSource.isForeignEmpty("ID_DEPOT_STOCK")) {
                    idDepot = rowValsSource.getForeignID("ID_DEPOT_STOCK");
                } else if (rowValsSource.getFields().contains("ID_AFFAIRE") && !rowValsSource.isForeignEmpty("ID_AFFAIRE")) {
                    SQLRowAccessor rowAff = rowValsSource.getForeign("ID_AFFAIRE");
                    if (rowAff.getFields().contains("ID_DEPOT_STOCK") && !rowAff.isForeignEmpty("ID_DEPOT_STOCK")) {
                        idDepot = rowAff.getForeignID("ID_DEPOT_STOCK");
                    }
                } else {
                    SQLField fieldParent = Configuration.getInstance().getDirectory().getElement(rowValsSource.getTable()).getParentForeignField();
                    if (fieldParent != null) {
                        SQLRowAccessor parent = rowValsSource.getForeign(fieldParent.getName());

                        if (parent.getFields().contains("ID_AFFAIRE") && !parent.isForeignEmpty("ID_AFFAIRE")) {
                            SQLRowAccessor rowAff = parent.getForeign("ID_AFFAIRE");
                            if (rowAff.getFields().contains("ID_DEPOT_STOCK") && !rowAff.isForeignEmpty("ID_DEPOT_STOCK")) {
                                idDepot = rowAff.getForeignID("ID_DEPOT_STOCK");
                            }
                        }
                    }
                }
            }
        }

        if (idDepot == -1) {

            if (rowValsSource.getFields().contains("ID_DEPOT_STOCK") && !rowValsSource.isForeignEmpty("ID_DEPOT_STOCK")) {
                idDepot = rowValsSource.getForeignID("ID_DEPOT_STOCK");
            } else {
                if (rowValsElt.getForeign("ID_DEPOT_STOCK") != null && !rowValsElt.isForeignEmpty("ID_DEPOT_STOCK")) {
                    idDepot = rowValsElt.getForeignID("ID_DEPOT_STOCK");
                } else {
                    idDepot = DepotStockSQLElement.DEFAULT_ID;
                    try {
                        rowValsElt.createEmptyUpdateRow().put("ID_DEPOT_STOCK", idDepot).commit();
                    } catch (SQLException e) {
                        ExceptionHandler.handle("Erreur lors de l'initialisation du stock!", e);
                    }

                }
            }
        }
        SQLTable stockTable = rowValsElt.getTable().getTable("STOCK");
        SQLRowValues putRowValuesStock = new SQLRowValues(stockTable);
        putRowValuesStock.putNulls(stockTable.getTable().getFieldsName());

        SQLRowValuesListFetcher fetch = SQLRowValuesListFetcher.create(putRowValuesStock);
        Where w = new Where(putRowValuesStock.getTable().getField("ID_DEPOT_STOCK"), "=", idDepot);
        Where w2 = new Where(putRowValuesStock.getTable().getField("ID_ARTICLE"), "=", rowValsProduct.getID());
        Collection<SQLRowValues> rowValsResult = fetch.fetch(w.and(w2));
        if (rowValsResult.size() == 0) {
            SQLRowValues rowValsStock = new SQLRowValues(stockTable);
            rowValsStock.put("ID_ARTICLE", rowValsProduct.getID());
            rowValsStock.put("ID_DEPOT_STOCK", idDepot);
            rowValsStock.put("QTE_TH", 0F);
            rowValsStock.put("QTE_REEL", 0F);
            rowValsStock.put("QTE_RECEPT_ATTENTE", 0F);
            rowValsStock.put("QTE_LIV_ATTENTE", 0F);
            try {
                rowStock = rowValsStock.insert();
                if (idDepot == DepotStockSQLElement.DEFAULT_ID) {
                    rowValsProduct.createEmptyUpdateRow().put("ID_STOCK", rowStock.getID()).commit();
                }
            } catch (SQLException e) {
                ExceptionHandler.handle("Erreur lors la création du stock!", e);
            }
        } else if (rowValsResult.size() == 1) {
            rowStock = rowValsResult.iterator().next();
        } else if (rowValsResult.size() > 1) {
            throw new IllegalStateException("2 lignes de stocks pour le même dépôt! Article " + rowValsProduct.getID() + " Depot " + rowValsElt.getForeignID("ID_DEPOT_STOCK"));
        }
        return rowStock;
    }

    public static SQLRowAccessor findOrCreateStock(SQLRowAccessor article, SQLRowAccessor depot) {

        SQLRowAccessor rowStock = null;
        SQLTable stockTable = article.getTable().getTable("STOCK");
        SQLRowValues putRowValuesStock = new SQLRowValues(stockTable);
        putRowValuesStock.putNulls(stockTable.getTable().getFieldsName());

        SQLRowValuesListFetcher fetch = SQLRowValuesListFetcher.create(putRowValuesStock);
        Where w = new Where(putRowValuesStock.getTable().getField("ID_DEPOT_STOCK"), "=", depot.getID());
        Where w2 = new Where(putRowValuesStock.getTable().getField("ID_ARTICLE"), "=", article.getID());
        Collection<SQLRowValues> rowValsResult = fetch.fetch(w.and(w2));
        if (rowValsResult.size() == 0) {
            SQLRowValues rowValsStock = new SQLRowValues(stockTable);
            rowValsStock.put("ID_ARTICLE", article.getID());
            rowValsStock.put("ID_DEPOT_STOCK", depot.getID());
            rowValsStock.put("QTE_TH", 0F);
            rowValsStock.put("QTE_REEL", 0F);
            rowValsStock.put("QTE_RECEPT_ATTENTE", 0F);
            rowValsStock.put("QTE_LIV_ATTENTE", 0F);
            try {
                rowStock = rowValsStock.insert();
            } catch (SQLException e) {
                ExceptionHandler.handle("Erreur lors la création du stock!", e);
            }
        } else if (rowValsResult.size() == 1) {
            rowStock = rowValsResult.iterator().next();
        } else if (rowValsResult.size() > 1) {
            throw new IllegalStateException("2 lignes de stocks pour le même dépôt! Article " + article.getID() + " Depot " + depot.getID());
        }
        return rowStock;

    }
}