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

import org.openconcerto.erp.importer.ArrayTableModel;

import org.openconcerto.erp.importer.DataImporter;

import org.openconcerto.sql.Configuration;

import org.openconcerto.sql.model.DBRoot;

import org.openconcerto.sql.model.SQLRow;

import org.openconcerto.sql.model.SQLRowAccessor;

import org.openconcerto.sql.model.SQLRowValues;

import org.openconcerto.sql.model.SQLRowValuesCluster.StoreMode;

import org.openconcerto.sql.model.SQLRowValuesListFetcher;

import org.openconcerto.sql.model.SQLTable;

import org.openconcerto.sql.utils.SQLUtils;

import org.openconcerto.utils.Tuple2;

import java.io.File;

import java.io.IOException;

import java.math.BigDecimal;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.Collection;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Set;

import org.apache.commons.dbutils.ResultSetHandler;

public class InventaireFromEtatStockImporter {

    private Map<String, SQLRowValues> kits = new HashMap<String, SQLRowValues>();

    private List<String> codeKits = new ArrayList<String>();

    private SQLRowAccessor depot;

    public InventaireFromEtatStockImporter(SQLRowAccessor depot) {

        this.depot = depot;

    }

    public void importArticles(File file, DBRoot root) throws IOException, SQLException {

        final SQLTable table = root.findTable("ARTICLE");

        final SQLTable tableArtElt = root.findTable("ARTICLE_ELEMENT");

        Map<String, Tuple2<SQLRowValues, SQLRowValues>> articles = getArticles();

        final DataImporter importer = new DataImporter(table) {

            @Override

            protected void customizeRowValuesToFetch(SQLRowValues vals) {

                vals.putRowValues("ID_STOCK").putNulls("ID", "QTE_REEL", "QTE_TH", "ID_DEPOT_STOCK");

            }

        };

        importer.setSkipFirstLine(true);

        ArrayTableModel m = importer.createModelFrom(file);

        Calendar c = Calendar.getInstance();

        // c.set(Calendar.DAY_OF_MONTH, 1);

        // c.set(Calendar.MONTH, Calendar.JANUARY);

        // c.set(Calendar.HOUR_OF_DAY, 0);

        Date today = c.getTime();

        // TODO ne pas vider les stocks des kits, recalculer les stocks des kits

        SQLRowValues rowVals = new SQLRowValues(table.getTable("ETAT_STOCK"));

        rowVals.put("DATE", today);

        rowVals.put("INVENTAIRE", Boolean.TRUE);

        rowVals.put("ID_DEPOT_STOCK", this.depot.getID());

        SQLRow rowEtat = rowVals.commit();

        for (int i = 1; i < m.getRowCount(); i++) {

            List<Object> o = m.getLineValuesAt(i);

            if (o.size() >= 5) {

                System.err.println(o);

                String code = o.get(1).toString();

                if (code.trim().length() > 0) {

                    final String stringQty = o.get(4).toString();

                    Double qty = stringQty.trim().length() == 0 ? 0 : Double.valueOf(stringQty);

                    final String stringQtyOld = o.get(3).toString();

                    float qtyOld = stringQtyOld.trim().length() == 0 ? 0 : Float.valueOf(stringQtyOld);

                    Tuple2<SQLRowValues, SQLRowValues> match = articles.get(code);

                    if (match != null) {

                        SQLRowAccessor stockValues = match.get1();

                        final SQLTable tableMvt = table.getTable("MOUVEMENT_STOCK");

                        SQLRowValues rowValsMvtStockClotureFermeture = new SQLRowValues(tableMvt);

                        rowValsMvtStockClotureFermeture.put("QTE", -qtyOld);

                        rowValsMvtStockClotureFermeture.put("NOM", "Clôture stock avant inventaire");

                        rowValsMvtStockClotureFermeture.put("ID_ARTICLE", match.get0().getID());

                        rowValsMvtStockClotureFermeture.put("DATE", today);

                        rowValsMvtStockClotureFermeture.put("REEL", Boolean.TRUE);

                        rowValsMvtStockClotureFermeture.put("ID_STOCK", stockValues.getID());

                        BigDecimal prc = getPRC(match.get0(), Math.round(qtyOld), today);

                        if (prc == null) {

                            prc = BigDecimal.ZERO;

                        }

                        if (tableMvt.contains("PRICE")) {

                            rowValsMvtStockClotureFermeture.put("PRICE", prc);

                        }

                        rowValsMvtStockClotureFermeture.put("CLOTURE", Boolean.TRUE);

                        rowValsMvtStockClotureFermeture.put("ID_ETAT_STOCK", rowEtat.getID());

                        rowValsMvtStockClotureFermeture.getGraph().store(StoreMode.COMMIT, false);

                        SQLRowValues rowValsItem = new SQLRowValues(table.getTable("ETAT_STOCK_ELEMENT"));

                        rowValsItem.put("ID_ETAT_STOCK", rowEtat.getID());

                        rowValsItem.put("PA", prc);

                        rowValsItem.put("PV", BigDecimal.ZERO);

                        rowValsItem.put("QTE", qtyOld);

                        rowValsItem.put("T_PA", prc.multiply(new BigDecimal(qtyOld)));

                        rowValsItem.put("T_PV", BigDecimal.ZERO);

                        rowValsItem.put("CODE", match.get0().getString("CODE"));

                        rowValsItem.put("NOM", match.get0().getString("NOM"));

                        rowValsItem.put("ID_ARTICLE", match.get0().getID());

                        rowValsItem.getGraph().store(StoreMode.COMMIT, false);

                        SQLRowValues rowValsMvtStockClotureOuverture = new SQLRowValues(tableMvt);

                        rowValsMvtStockClotureOuverture.put("QTE", qty);

                        rowValsMvtStockClotureOuverture.put("NOM", "Mise en stock inventaire");

                        rowValsMvtStockClotureOuverture.put("ID_ETAT_STOCK", rowEtat.getID());

                        rowValsMvtStockClotureOuverture.put("ID_ARTICLE", match.get0().getID());

                        rowValsMvtStockClotureOuverture.put("DATE", today);

                        rowValsMvtStockClotureOuverture.put("REEL", Boolean.TRUE);

                        rowValsMvtStockClotureOuverture.put("ID_STOCK", stockValues.getID());

                        rowValsMvtStockClotureOuverture.put("OUVERTURE", Boolean.TRUE);

                        if (tableMvt.contains("PRICE")) {

                            rowValsMvtStockClotureOuverture.put("PRICE", getPRC(match.get0(), qty.intValue(), today));

                        }

                        rowValsMvtStockClotureOuverture.getGraph().store(StoreMode.COMMIT, false);

                        // if (!match.isForeignEmpty("ID_STOCK")) {

                        // match.getForeign("ID_STOCK").createEmptyUpdateRow().put("QTE_REEL",

                        // qty).commit();

                        // } else {

                        final SQLRowValues createEmptyUpdateRow = match.get1().createEmptyUpdateRow();

                        createEmptyUpdateRow.put("QTE_REEL", qty);

                        createEmptyUpdateRow.getGraph().store(StoreMode.COMMIT, false);

                        // }

                    } else {

                        System.err.println("Aucun article correspondant au code " + code);

                    }

                }

            }

        }

        /**

         * Mise à jour des kits

         */

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

        for (String code : codeKits) {

            System.err.println(code);

            SQLRowValues rowValsKit = kits.get(code);

            StockItem item = new StockItem(rowValsKit, rowValsKit.getForeign("ID_STOCK"));

            Collection<SQLRowValues> elts = rowValsKit.getReferentRows(tableArtElt.getField("ID_ARTICLE_PARENT"));

            for (SQLRowValues sqlRowValues : elts) {

                if (sqlRowValues.getForeign("ID_ARTICLE") != null) {

                    item.addItemComponent(new StockItemComponent(new StockItem(sqlRowValues.getForeign("ID_ARTICLE"), sqlRowValues.getForeign("ID_ARTICLE").getForeign("ID_STOCK")),

                            sqlRowValues.getBigDecimal("QTE_UNITAIRE"), sqlRowValues.getInt("QTE")));

                }

            }

            item.updateQtyFromChildren();

            reqs.add(item.getUpdateRequest());

        }

        List<? extends ResultSetHandler> handlers = new ArrayList<ResultSetHandler>(reqs.size());

        for (String s : reqs) {

            handlers.add(null);

        }

        // FIXME FIRE TABLE CHANGED TO UPDATE ILISTE ??

        SQLUtils.executeMultiple(table.getDBSystemRoot(), reqs, handlers);

        /**

         * Mise à jour des prix mini

         */

        // for (SQLRowValues rowValsArt : rowValsArtNonSync) {

        // SQLRow rowArt = rowValsArt.asRow();

        // List<SQLRow> rowsPVMin =

        // rowArt.getReferentRows(tableArtElt.getTable("ARTICLE_PRIX_MIN_VENTE"));

        // List<SQLRow> rowsPA =

        // rowArt.getReferentRows(tableArtElt.getTable("ARTICLE_TARIF_FOURNISSEUR"));

        //

        // // On récupére les derniers prix min valides

        // Map<Integer, SQLRow> mapLastValidRows = new HashMap<Integer, SQLRow>();

        // for (SQLRow rowPVMin : rowsPVMin) {

        // final int qteMinPrice = rowPVMin.getInt("QTE");

        // SQLRow rowValsLastValid = mapLastValidRows.get(qteMinPrice);

        // if (rowValsLastValid == null || rowValsLastValid.getDate("DATE") == null ||

        // rowValsLastValid.getDate("DATE").before(rowPVMin.getDate("DATE"))) {

        // mapLastValidRows.put(qteMinPrice, rowPVMin);

        // }

        // }

        //

        // // On récupére les derniers Prix d'achat valide

        // Map<Integer, SQLRow> mapLastValidAchatRows = new HashMap<Integer, SQLRow>();

        // for (SQLRow rowPA : rowsPA) {

        // final int qtePRC = rowPA.getInt("QTE");

        // SQLRow rowValsLastValid = mapLastValidAchatRows.get(qtePRC);

        // if (rowValsLastValid == null || rowValsLastValid.getDate("DATE_PRIX") == null ||

        // rowValsLastValid.getDate("DATE_PRIX").before(rowPA.getDate("DATE_PRIX"))) {

        // mapLastValidAchatRows.put(qtePRC, rowPA);

        // }

        // }

        //

        // // Mise à jour, si Prix < au prix min, du PRC et des prix min

        // for (Integer qte : mapLastValidAchatRows.keySet()) {

        // SQLRow rowVals = mapLastValidAchatRows.get(qte);

        // checkMinPrice(rowVals, mapLastValidRows.get(qte));

        // }

        // }

    }

    private void checkMinPrice(SQLRow rowValsSuplierLastValid, SQLRow lastValidRow) {

        boolean update = false;

        final ProductHelper helper = new ProductHelper(rowValsSuplierLastValid.getTable().getDBRoot());

        BigDecimal result = helper.getEnumPrice(rowValsSuplierLastValid, SupplierPriceField.COEF_PRIX_MINI);

        if (result != null) {

            final int qteSuplier = rowValsSuplierLastValid.getInt("QTE");

            final Calendar date2 = rowValsSuplierLastValid.getDate("DATE_PRIX");

            if (date2 != null) {

                if (lastValidRow != null) {

                    final Calendar date1 = lastValidRow.getDate("DATE");

                    if ((date1.get(Calendar.YEAR) == date2.get(Calendar.YEAR) && date1.get(Calendar.MONTH) == date2.get(Calendar.MONTH)

                            && date1.get(Calendar.DAY_OF_MONTH) == date2.get(Calendar.DAY_OF_MONTH)) || date1.after(date2)) {

                        if (lastValidRow.getBigDecimal("PRIX") != null && lastValidRow.getInt("QTE") <= qteSuplier) {

                            try {

                                lastValidRow.asRowValues().put("PRIX", result).commit();

                            } catch (SQLException e) {

                                // TODO Auto-generated catch block

                                e.printStackTrace();

                            }

                            // purchaseMinPriceListTable.setPriceMin(result);

                            update = true;

                        }

                    } else {

                        if (date1.before(date2)) {

                            SQLRowValues rowValsToInsert = new SQLRowValues(lastValidRow.getTable());

                            rowValsToInsert.put("PRIX", result);

                            rowValsToInsert.put("DATE", rowValsSuplierLastValid.getObject("DATE_PRIX"));

                            rowValsToInsert.put("QTE", rowValsSuplierLastValid.getObject("QTE"));

                            rowValsToInsert.put("ID_ARTICLE", rowValsSuplierLastValid.getInt("ID_ARTICLE"));

                            try {

                                rowValsToInsert.commit();

                            } catch (SQLException e) {

                                // TODO Auto-generated catch block

                                e.printStackTrace();

                            }

                        }

                    }

                }

            }

        }

    }

    public BigDecimal getPRC(SQLRowValues rowVals, int qty, Date d) {

        // SQLTable table = rowVals.getTable().getDBRoot().getTable("ARTICLE_PRIX_REVIENT");

        // Collection<SQLRow> prcs = rowVals.asRow().getReferentRows(table);

        //

        // BigDecimal result = null;

        // final List<PriceByQty> prices = new ArrayList<PriceByQty>();

        //

        // for (SQLRow row : prcs) {

        // Calendar date = Calendar.getInstance();

        // date.set(Calendar.DAY_OF_MONTH, 1);

        // date.set(Calendar.MONTH, 1);

        // date.set(Calendar.YEAR, 2001);

        // if (row.getObject("DATE") != null) {

        // date = row.getDate("DATE");

        // }

        // prices.add(new PriceByQty(row.getLong("QTE"), row.getBigDecimal("PRIX"),

        // date.getTime()));

        // }

        //

        // result = PriceByQty.getPriceForQty(qty, prices, d);

        // if (result == null) {

        // // Can occur during editing

        // result = BigDecimal.ZERO;

        // }

        ProductComponent comp = new ProductComponent(rowVals, new BigDecimal(qty), null, null);

        return comp.getPRC(d);

        // return result;

    }

    private Map<String, Tuple2<SQLRowValues, SQLRowValues>> getArticles() throws SQLException {

        final SQLTable table = Configuration.getInstance().getRoot().findTable("ARTICLE");

        SQLRowValues graph = new SQLRowValues(table);

        graph.put("ID", null);

        graph.put("CODE", null);

        graph.put("NOM", null);

        graph.put("NOM", null);

        final SQLTable foreignTableStock = table.getForeignTable("ID_STOCK");

        SQLRowValues graphStock = new SQLRowValues(foreignTableStock);

        graphStock.putNulls("ID_DEPOT_STOCK", "ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");

        graphStock.put("ID_ARTICLE", graph);

        final SQLTable tableArtElt = table.getTable("ARTICLE_ELEMENT");

        SQLRowValues artElt = new SQLRowValues(tableArtElt);

        artElt.put("ID", null);

        artElt.put("QTE", null);

        artElt.put("QTE_UNITAIRE", null);

        artElt.put("ID_ARTICLE_PARENT", graph);

        final SQLRowValues articleParent = artElt.putRowValues("ID_ARTICLE");

        articleParent.putNulls("ID", "CODE", "NOM");

        SQLRowValues graphStockItem = new SQLRowValues(foreignTableStock);

        graphStockItem.putNulls("ID_DEPOT_STOCK", "ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");

        graphStockItem.put("ID_ARTICLE", articleParent);

        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(graph);

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

        Calendar c = Calendar.getInstance();

        // c.set(Calendar.DAY_OF_MONTH, 1);

        c.add(Calendar.MONTH, -2);

        c.set(Calendar.DAY_OF_MONTH, 31);

        Date dEndYear = c.getTime();

        Map<String, Tuple2<SQLRowValues, SQLRowValues>> vals = new HashMap<String, Tuple2<SQLRowValues, SQLRowValues>>();

        for (SQLRowValues sqlRowValues : results) {

            final String code = sqlRowValues.getString("CODE");

            Collection<SQLRowValues> stocks = sqlRowValues.getReferentRows(foreignTableStock);

            SQLRowValues rowValsStock = null;

            for (SQLRowValues sqlRowValues2 : stocks) {

                if (sqlRowValues2.getForeignID("ID_DEPOT_STOCK") == depot.getID()) {

                    rowValsStock = sqlRowValues2;

                }

            }

            if (rowValsStock == null) {

                rowValsStock = ProductComponent.findOrCreateStock(sqlRowValues, depot).asRowValues();

            }

            vals.put(code, Tuple2.create(sqlRowValues, rowValsStock));

            final Set<SQLRowValues> referentRows = sqlRowValues.getReferentRows(tableArtElt.getField("ID_ARTICLE_PARENT"));

            if (referentRows.size() == 0) {

                // if (!sqlRowValues.isForeignEmpty("ID_STOCK")) {

                // SQLRowAccessor rowValsStock = sqlRowValues.getForeign("ID_STOCK");

                // int qteReel = rowValsStock.getInt("QTE_REEL");

                // {

                // SQLRowValues rowValsMvtStockCloture = new

                // SQLRowValues(table.getTable("MOUVEMENT_STOCK"));

                // rowValsMvtStockCloture.put("QTE", -qteReel);

                // rowValsMvtStockCloture.put("NOM", "Clôture du stock avant inventaire");

                // rowValsMvtStockCloture.put("ID_ARTICLE", sqlRowValues.getID());

                // rowValsMvtStockCloture.put("DATE", dEndYear);

                // rowValsMvtStockCloture.put("REEL", Boolean.TRUE);

                // rowValsMvtStockCloture.put("PRICE", getPRC(sqlRowValues, qteReel, dEndYear));

                // rowValsMvtStockCloture.commit();

                //

                // rowValsStock.createEmptyUpdateRow().put("QTE_REEL", 0).commit();

                // }

                //

                // } else {

                // sqlRowValues.putRowValues("ID_STOCK").commit();

                // }

            } else {

                boolean contains = false;

                for (SQLRowValues sqlRowValues2 : referentRows) {

                    if (!sqlRowValues2.isForeignEmpty("ID_ARTICLE") && sqlRowValues2.getForeign("ID_ARTICLE") != null && sqlRowValues2.getForeign("ID_ARTICLE").getString("CODE") != null) {

                        if (codeKits.contains(sqlRowValues2.getForeign("ID_ARTICLE").getString("CODE"))) {

                            contains = true;

                            break;

                        }

                    }

                }

                if (!contains) {

                    codeKits.add(0, code);

                } else {

                    codeKits.add(code);

                }

                kits.put(code, sqlRowValues);

                // if (sqlRowValues.isForeignEmpty("ID_STOCK")) {

                // sqlRowValues.putRowValues("ID_STOCK").commit();

                // }

            }

        }

        return vals;

    }

}