OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 156 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
156 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.erp.core.supplychain.stock.element;
15
 
16
import org.openconcerto.erp.core.sales.product.model.ProductComponent;
17
import org.openconcerto.erp.core.sales.product.model.ProductHelper;
18
import org.openconcerto.erp.core.sales.product.model.ProductHelper.SupplierPriceField;
19
import org.openconcerto.erp.importer.ArrayTableModel;
20
import org.openconcerto.erp.importer.DataImporter;
21
import org.openconcerto.sql.Configuration;
22
import org.openconcerto.sql.model.DBRoot;
23
import org.openconcerto.sql.model.SQLRow;
24
import org.openconcerto.sql.model.SQLRowAccessor;
25
import org.openconcerto.sql.model.SQLRowValues;
26
import org.openconcerto.sql.model.SQLRowValuesCluster.StoreMode;
27
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
28
import org.openconcerto.sql.model.SQLTable;
29
import org.openconcerto.sql.utils.SQLUtils;
177 ilm 30
import org.openconcerto.utils.Tuple2;
156 ilm 31
 
32
import java.io.File;
33
import java.io.IOException;
34
import java.math.BigDecimal;
35
import java.sql.SQLException;
36
import java.util.ArrayList;
37
import java.util.Calendar;
38
import java.util.Collection;
39
import java.util.Date;
40
import java.util.HashMap;
41
import java.util.List;
42
import java.util.Map;
43
import java.util.Set;
44
 
45
import org.apache.commons.dbutils.ResultSetHandler;
46
 
47
public class InventaireFromEtatStockImporter {
48
 
177 ilm 49
    private Map<String, SQLRowValues> kits = new HashMap<String, SQLRowValues>();
50
    private List<String> codeKits = new ArrayList<String>();
51
    private SQLRowAccessor depot;
156 ilm 52
 
177 ilm 53
    public InventaireFromEtatStockImporter(SQLRowAccessor depot) {
54
        this.depot = depot;
156 ilm 55
    }
56
 
57
    public void importArticles(File file, DBRoot root) throws IOException, SQLException {
58
 
59
        final SQLTable table = root.findTable("ARTICLE");
60
        final SQLTable tableArtElt = root.findTable("ARTICLE_ELEMENT");
61
 
177 ilm 62
        Map<String, Tuple2<SQLRowValues, SQLRowValues>> articles = getArticles();
156 ilm 63
 
64
        final DataImporter importer = new DataImporter(table) {
65
            @Override
66
            protected void customizeRowValuesToFetch(SQLRowValues vals) {
67
 
68
                vals.putRowValues("ID_STOCK").putNulls("ID", "QTE_REEL", "QTE_TH", "ID_DEPOT_STOCK");
69
            }
70
        };
71
        importer.setSkipFirstLine(true);
72
 
73
        ArrayTableModel m = importer.createModelFrom(file);
74
 
75
        Calendar c = Calendar.getInstance();
76
        // c.set(Calendar.DAY_OF_MONTH, 1);
77
        // c.set(Calendar.MONTH, Calendar.JANUARY);
78
        // c.set(Calendar.HOUR_OF_DAY, 0);
79
        Date today = c.getTime();
80
 
81
        // TODO ne pas vider les stocks des kits, recalculer les stocks des kits
82
 
83
        SQLRowValues rowVals = new SQLRowValues(table.getTable("ETAT_STOCK"));
84
        rowVals.put("DATE", today);
85
        rowVals.put("INVENTAIRE", Boolean.TRUE);
177 ilm 86
        rowVals.put("ID_DEPOT_STOCK", this.depot.getID());
156 ilm 87
        SQLRow rowEtat = rowVals.commit();
88
 
89
        for (int i = 1; i < m.getRowCount(); i++) {
90
            List<Object> o = m.getLineValuesAt(i);
91
            if (o.size() >= 5) {
92
                System.err.println(o);
93
                String code = o.get(1).toString();
94
                if (code.trim().length() > 0) {
95
 
96
                    final String stringQty = o.get(4).toString();
97
                    Double qty = stringQty.trim().length() == 0 ? 0 : Double.valueOf(stringQty);
98
                    final String stringQtyOld = o.get(3).toString();
99
                    float qtyOld = stringQtyOld.trim().length() == 0 ? 0 : Float.valueOf(stringQtyOld);
100
 
177 ilm 101
                    Tuple2<SQLRowValues, SQLRowValues> match = articles.get(code);
156 ilm 102
                    if (match != null) {
103
 
177 ilm 104
                        SQLRowAccessor stockValues = match.get1();
156 ilm 105
 
106
                        final SQLTable tableMvt = table.getTable("MOUVEMENT_STOCK");
107
                        SQLRowValues rowValsMvtStockClotureFermeture = new SQLRowValues(tableMvt);
108
                        rowValsMvtStockClotureFermeture.put("QTE", -qtyOld);
109
                        rowValsMvtStockClotureFermeture.put("NOM", "Clôture stock avant inventaire");
177 ilm 110
                        rowValsMvtStockClotureFermeture.put("ID_ARTICLE", match.get0().getID());
156 ilm 111
                        rowValsMvtStockClotureFermeture.put("DATE", today);
112
                        rowValsMvtStockClotureFermeture.put("REEL", Boolean.TRUE);
113
                        rowValsMvtStockClotureFermeture.put("ID_STOCK", stockValues.getID());
114
 
177 ilm 115
                        BigDecimal prc = getPRC(match.get0(), Math.round(qtyOld), today);
156 ilm 116
                        if (prc == null) {
117
                            prc = BigDecimal.ZERO;
118
                        }
119
                        if (tableMvt.contains("PRICE")) {
120
                            rowValsMvtStockClotureFermeture.put("PRICE", prc);
121
                        }
122
                        rowValsMvtStockClotureFermeture.put("CLOTURE", Boolean.TRUE);
123
                        rowValsMvtStockClotureFermeture.put("ID_ETAT_STOCK", rowEtat.getID());
124
                        rowValsMvtStockClotureFermeture.getGraph().store(StoreMode.COMMIT, false);
125
 
126
                        SQLRowValues rowValsItem = new SQLRowValues(table.getTable("ETAT_STOCK_ELEMENT"));
127
                        rowValsItem.put("ID_ETAT_STOCK", rowEtat.getID());
128
                        rowValsItem.put("PA", prc);
129
                        rowValsItem.put("PV", BigDecimal.ZERO);
130
                        rowValsItem.put("QTE", qtyOld);
131
                        rowValsItem.put("T_PA", prc.multiply(new BigDecimal(qtyOld)));
132
                        rowValsItem.put("T_PV", BigDecimal.ZERO);
177 ilm 133
                        rowValsItem.put("CODE", match.get0().getString("CODE"));
134
                        rowValsItem.put("NOM", match.get0().getString("NOM"));
135
                        rowValsItem.put("ID_ARTICLE", match.get0().getID());
156 ilm 136
                        rowValsItem.getGraph().store(StoreMode.COMMIT, false);
137
 
138
                        SQLRowValues rowValsMvtStockClotureOuverture = new SQLRowValues(tableMvt);
139
                        rowValsMvtStockClotureOuverture.put("QTE", qty);
140
                        rowValsMvtStockClotureOuverture.put("NOM", "Mise en stock inventaire");
141
                        rowValsMvtStockClotureOuverture.put("ID_ETAT_STOCK", rowEtat.getID());
177 ilm 142
                        rowValsMvtStockClotureOuverture.put("ID_ARTICLE", match.get0().getID());
156 ilm 143
                        rowValsMvtStockClotureOuverture.put("DATE", today);
144
                        rowValsMvtStockClotureOuverture.put("REEL", Boolean.TRUE);
145
                        rowValsMvtStockClotureOuverture.put("ID_STOCK", stockValues.getID());
146
                        rowValsMvtStockClotureOuverture.put("OUVERTURE", Boolean.TRUE);
147
                        if (tableMvt.contains("PRICE")) {
177 ilm 148
                            rowValsMvtStockClotureOuverture.put("PRICE", getPRC(match.get0(), qty.intValue(), today));
156 ilm 149
                        }
150
                        rowValsMvtStockClotureOuverture.getGraph().store(StoreMode.COMMIT, false);
151
 
177 ilm 152
                        // if (!match.isForeignEmpty("ID_STOCK")) {
153
                        // match.getForeign("ID_STOCK").createEmptyUpdateRow().put("QTE_REEL",
154
                        // qty).commit();
155
                        // } else {
156
                        final SQLRowValues createEmptyUpdateRow = match.get1().createEmptyUpdateRow();
157
                        createEmptyUpdateRow.put("QTE_REEL", qty);
158
                        createEmptyUpdateRow.getGraph().store(StoreMode.COMMIT, false);
156 ilm 159
 
177 ilm 160
                        // }
156 ilm 161
 
162
                    } else {
163
                        System.err.println("Aucun article correspondant au code " + code);
164
                    }
165
                }
166
            }
167
        }
168
 
169
        /**
170
         * Mise à jour des kits
171
         */
172
 
173
        List<String> reqs = new ArrayList<String>();
174
        for (String code : codeKits) {
175
            System.err.println(code);
176
            SQLRowValues rowValsKit = kits.get(code);
177
            StockItem item = new StockItem(rowValsKit, rowValsKit.getForeign("ID_STOCK"));
178
            Collection<SQLRowValues> elts = rowValsKit.getReferentRows(tableArtElt.getField("ID_ARTICLE_PARENT"));
179
            for (SQLRowValues sqlRowValues : elts) {
180
                if (sqlRowValues.getForeign("ID_ARTICLE") != null) {
181
                    item.addItemComponent(new StockItemComponent(new StockItem(sqlRowValues.getForeign("ID_ARTICLE"), sqlRowValues.getForeign("ID_ARTICLE").getForeign("ID_STOCK")),
182
                            sqlRowValues.getBigDecimal("QTE_UNITAIRE"), sqlRowValues.getInt("QTE")));
183
                }
184
            }
185
            item.updateQtyFromChildren();
186
            reqs.add(item.getUpdateRequest());
187
        }
188
 
189
        List<? extends ResultSetHandler> handlers = new ArrayList<ResultSetHandler>(reqs.size());
190
        for (String s : reqs) {
191
            handlers.add(null);
192
        }
193
        // FIXME FIRE TABLE CHANGED TO UPDATE ILISTE ??
194
        SQLUtils.executeMultiple(table.getDBSystemRoot(), reqs, handlers);
195
 
196
        /**
197
         * Mise à jour des prix mini
198
         */
199
        // for (SQLRowValues rowValsArt : rowValsArtNonSync) {
200
        // SQLRow rowArt = rowValsArt.asRow();
201
        // List<SQLRow> rowsPVMin =
202
        // rowArt.getReferentRows(tableArtElt.getTable("ARTICLE_PRIX_MIN_VENTE"));
203
        // List<SQLRow> rowsPA =
204
        // rowArt.getReferentRows(tableArtElt.getTable("ARTICLE_TARIF_FOURNISSEUR"));
205
        //
206
        // // On récupére les derniers prix min valides
207
        // Map<Integer, SQLRow> mapLastValidRows = new HashMap<Integer, SQLRow>();
208
        // for (SQLRow rowPVMin : rowsPVMin) {
209
        // final int qteMinPrice = rowPVMin.getInt("QTE");
210
        // SQLRow rowValsLastValid = mapLastValidRows.get(qteMinPrice);
211
        // if (rowValsLastValid == null || rowValsLastValid.getDate("DATE") == null ||
212
        // rowValsLastValid.getDate("DATE").before(rowPVMin.getDate("DATE"))) {
213
        // mapLastValidRows.put(qteMinPrice, rowPVMin);
214
        // }
215
        // }
216
        //
217
        // // On récupére les derniers Prix d'achat valide
218
        // Map<Integer, SQLRow> mapLastValidAchatRows = new HashMap<Integer, SQLRow>();
219
        // for (SQLRow rowPA : rowsPA) {
220
        // final int qtePRC = rowPA.getInt("QTE");
221
        // SQLRow rowValsLastValid = mapLastValidAchatRows.get(qtePRC);
222
        // if (rowValsLastValid == null || rowValsLastValid.getDate("DATE_PRIX") == null ||
223
        // rowValsLastValid.getDate("DATE_PRIX").before(rowPA.getDate("DATE_PRIX"))) {
224
        // mapLastValidAchatRows.put(qtePRC, rowPA);
225
        // }
226
        // }
227
        //
228
        // // Mise à jour, si Prix < au prix min, du PRC et des prix min
229
        // for (Integer qte : mapLastValidAchatRows.keySet()) {
230
        // SQLRow rowVals = mapLastValidAchatRows.get(qte);
231
        // checkMinPrice(rowVals, mapLastValidRows.get(qte));
232
        // }
233
        // }
234
 
235
    }
236
 
237
    private void checkMinPrice(SQLRow rowValsSuplierLastValid, SQLRow lastValidRow) {
238
        boolean update = false;
239
        final ProductHelper helper = new ProductHelper(rowValsSuplierLastValid.getTable().getDBRoot());
240
 
241
        BigDecimal result = helper.getEnumPrice(rowValsSuplierLastValid, SupplierPriceField.COEF_PRIX_MINI);
242
        if (result != null) {
243
            final int qteSuplier = rowValsSuplierLastValid.getInt("QTE");
244
 
245
            final Calendar date2 = rowValsSuplierLastValid.getDate("DATE_PRIX");
246
            if (date2 != null) {
247
                if (lastValidRow != null) {
248
                    final Calendar date1 = lastValidRow.getDate("DATE");
249
                    if ((date1.get(Calendar.YEAR) == date2.get(Calendar.YEAR) && date1.get(Calendar.MONTH) == date2.get(Calendar.MONTH)
250
                            && date1.get(Calendar.DAY_OF_MONTH) == date2.get(Calendar.DAY_OF_MONTH)) || date1.after(date2)) {
251
                        if (lastValidRow.getBigDecimal("PRIX") != null && lastValidRow.getInt("QTE") <= qteSuplier) {
252
                            try {
253
                                lastValidRow.asRowValues().put("PRIX", result).commit();
254
                            } catch (SQLException e) {
255
                                // TODO Auto-generated catch block
256
                                e.printStackTrace();
257
                            }
258
                            // purchaseMinPriceListTable.setPriceMin(result);
259
                            update = true;
260
                        }
261
                    } else {
262
                        if (date1.before(date2)) {
263
                            SQLRowValues rowValsToInsert = new SQLRowValues(lastValidRow.getTable());
264
                            rowValsToInsert.put("PRIX", result);
265
                            rowValsToInsert.put("DATE", rowValsSuplierLastValid.getObject("DATE_PRIX"));
266
                            rowValsToInsert.put("QTE", rowValsSuplierLastValid.getObject("QTE"));
267
                            rowValsToInsert.put("ID_ARTICLE", rowValsSuplierLastValid.getInt("ID_ARTICLE"));
268
                            try {
269
                                rowValsToInsert.commit();
270
                            } catch (SQLException e) {
271
                                // TODO Auto-generated catch block
272
                                e.printStackTrace();
273
                            }
274
                        }
275
                    }
276
                }
277
            }
278
        }
279
 
280
    }
281
 
282
    public BigDecimal getPRC(SQLRowValues rowVals, int qty, Date d) {
283
        // SQLTable table = rowVals.getTable().getDBRoot().getTable("ARTICLE_PRIX_REVIENT");
284
        // Collection<SQLRow> prcs = rowVals.asRow().getReferentRows(table);
285
        //
286
        // BigDecimal result = null;
287
        // final List<PriceByQty> prices = new ArrayList<PriceByQty>();
288
        //
289
        // for (SQLRow row : prcs) {
290
        // Calendar date = Calendar.getInstance();
291
        // date.set(Calendar.DAY_OF_MONTH, 1);
292
        // date.set(Calendar.MONTH, 1);
293
        // date.set(Calendar.YEAR, 2001);
294
        // if (row.getObject("DATE") != null) {
295
        // date = row.getDate("DATE");
296
        // }
297
        // prices.add(new PriceByQty(row.getLong("QTE"), row.getBigDecimal("PRIX"),
298
        // date.getTime()));
299
        // }
300
        //
301
        // result = PriceByQty.getPriceForQty(qty, prices, d);
302
        // if (result == null) {
303
        // // Can occur during editing
304
        // result = BigDecimal.ZERO;
305
        // }
306
 
307
        ProductComponent comp = new ProductComponent(rowVals, new BigDecimal(qty), null, null);
308
        return comp.getPRC(d);
309
        // return result;
310
    }
311
 
177 ilm 312
    private Map<String, Tuple2<SQLRowValues, SQLRowValues>> getArticles() throws SQLException {
156 ilm 313
        final SQLTable table = Configuration.getInstance().getRoot().findTable("ARTICLE");
314
        SQLRowValues graph = new SQLRowValues(table);
315
        graph.put("ID", null);
316
        graph.put("CODE", null);
317
        graph.put("NOM", null);
318
        graph.put("NOM", null);
177 ilm 319
        final SQLTable foreignTableStock = table.getForeignTable("ID_STOCK");
320
        SQLRowValues graphStock = new SQLRowValues(foreignTableStock);
321
        graphStock.putNulls("ID_DEPOT_STOCK", "ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");
322
        graphStock.put("ID_ARTICLE", graph);
156 ilm 323
 
324
        final SQLTable tableArtElt = table.getTable("ARTICLE_ELEMENT");
325
        SQLRowValues artElt = new SQLRowValues(tableArtElt);
326
        artElt.put("ID", null);
327
        artElt.put("QTE", null);
328
        artElt.put("QTE_UNITAIRE", null);
329
        artElt.put("ID_ARTICLE_PARENT", graph);
177 ilm 330
        final SQLRowValues articleParent = artElt.putRowValues("ID_ARTICLE");
331
        articleParent.putNulls("ID", "CODE", "NOM");
332
        SQLRowValues graphStockItem = new SQLRowValues(foreignTableStock);
333
        graphStockItem.putNulls("ID_DEPOT_STOCK", "ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");
334
        graphStockItem.put("ID_ARTICLE", articleParent);
156 ilm 335
 
336
        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(graph);
337
        List<SQLRowValues> results = fetcher.fetch();
338
 
339
        Calendar c = Calendar.getInstance();
340
        // c.set(Calendar.DAY_OF_MONTH, 1);
341
        c.add(Calendar.MONTH, -2);
342
        c.set(Calendar.DAY_OF_MONTH, 31);
343
        Date dEndYear = c.getTime();
344
 
177 ilm 345
        Map<String, Tuple2<SQLRowValues, SQLRowValues>> vals = new HashMap<String, Tuple2<SQLRowValues, SQLRowValues>>();
156 ilm 346
        for (SQLRowValues sqlRowValues : results) {
347
            final String code = sqlRowValues.getString("CODE");
348
 
177 ilm 349
            Collection<SQLRowValues> stocks = sqlRowValues.getReferentRows(foreignTableStock);
350
 
351
            SQLRowValues rowValsStock = null;
352
            for (SQLRowValues sqlRowValues2 : stocks) {
353
                if (sqlRowValues2.getForeignID("ID_DEPOT_STOCK") == depot.getID()) {
354
                    rowValsStock = sqlRowValues2;
355
                }
356
            }
357
            if (rowValsStock == null) {
358
                rowValsStock = ProductComponent.findOrCreateStock(sqlRowValues, depot).asRowValues();
359
            }
360
 
361
            vals.put(code, Tuple2.create(sqlRowValues, rowValsStock));
362
 
156 ilm 363
            final Set<SQLRowValues> referentRows = sqlRowValues.getReferentRows(tableArtElt.getField("ID_ARTICLE_PARENT"));
364
            if (referentRows.size() == 0) {
365
                // if (!sqlRowValues.isForeignEmpty("ID_STOCK")) {
366
                // SQLRowAccessor rowValsStock = sqlRowValues.getForeign("ID_STOCK");
367
                // int qteReel = rowValsStock.getInt("QTE_REEL");
368
                // {
369
                // SQLRowValues rowValsMvtStockCloture = new
370
                // SQLRowValues(table.getTable("MOUVEMENT_STOCK"));
371
                // rowValsMvtStockCloture.put("QTE", -qteReel);
372
                // rowValsMvtStockCloture.put("NOM", "Clôture du stock avant inventaire");
373
                // rowValsMvtStockCloture.put("ID_ARTICLE", sqlRowValues.getID());
374
                // rowValsMvtStockCloture.put("DATE", dEndYear);
375
                // rowValsMvtStockCloture.put("REEL", Boolean.TRUE);
376
                // rowValsMvtStockCloture.put("PRICE", getPRC(sqlRowValues, qteReel, dEndYear));
377
                // rowValsMvtStockCloture.commit();
378
                //
379
                // rowValsStock.createEmptyUpdateRow().put("QTE_REEL", 0).commit();
380
                // }
381
                //
382
                // } else {
383
                // sqlRowValues.putRowValues("ID_STOCK").commit();
384
                // }
385
            } else {
386
                boolean contains = false;
387
                for (SQLRowValues sqlRowValues2 : referentRows) {
388
                    if (!sqlRowValues2.isForeignEmpty("ID_ARTICLE") && sqlRowValues2.getForeign("ID_ARTICLE") != null && sqlRowValues2.getForeign("ID_ARTICLE").getString("CODE") != null) {
389
                        if (codeKits.contains(sqlRowValues2.getForeign("ID_ARTICLE").getString("CODE"))) {
390
                            contains = true;
391
                            break;
392
                        }
393
                    }
394
                }
395
                if (!contains) {
396
                    codeKits.add(0, code);
397
                } else {
398
                    codeKits.add(code);
399
                }
400
                kits.put(code, sqlRowValues);
401
                // if (sqlRowValues.isForeignEmpty("ID_STOCK")) {
402
                // sqlRowValues.putRowValues("ID_STOCK").commit();
403
                // }
404
            }
405
        }
406
        return vals;
407
    }
408
}