OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 156 | Show entire file | Regard whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 156 Rev 177
Line 25... Line 25...
25
import org.openconcerto.sql.model.SQLRowValues;
25
import org.openconcerto.sql.model.SQLRowValues;
26
import org.openconcerto.sql.model.SQLRowValuesCluster.StoreMode;
26
import org.openconcerto.sql.model.SQLRowValuesCluster.StoreMode;
27
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
27
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
28
import org.openconcerto.sql.model.SQLTable;
28
import org.openconcerto.sql.model.SQLTable;
29
import org.openconcerto.sql.utils.SQLUtils;
29
import org.openconcerto.sql.utils.SQLUtils;
-
 
30
import org.openconcerto.utils.Tuple2;
30
 
31
 
31
import java.io.File;
32
import java.io.File;
32
import java.io.IOException;
33
import java.io.IOException;
33
import java.math.BigDecimal;
34
import java.math.BigDecimal;
34
import java.sql.SQLException;
35
import java.sql.SQLException;
Line 43... Line 44...
43
 
44
 
44
import org.apache.commons.dbutils.ResultSetHandler;
45
import org.apache.commons.dbutils.ResultSetHandler;
45
 
46
 
46
public class InventaireFromEtatStockImporter {
47
public class InventaireFromEtatStockImporter {
47
 
48
 
48
    Map<String, SQLRowValues> kits = new HashMap<String, SQLRowValues>();
49
    private Map<String, SQLRowValues> kits = new HashMap<String, SQLRowValues>();
49
    List<String> codeKits = new ArrayList<String>();
50
    private List<String> codeKits = new ArrayList<String>();
50
 
-
 
51
    public InventaireFromEtatStockImporter() {
51
    private SQLRowAccessor depot;
52
 
52
 
-
 
53
    public InventaireFromEtatStockImporter(SQLRowAccessor depot) {
-
 
54
        this.depot = depot;
53
    }
55
    }
54
 
56
 
55
    public void importArticles(File file, DBRoot root) throws IOException, SQLException {
57
    public void importArticles(File file, DBRoot root) throws IOException, SQLException {
56
 
58
 
57
        final SQLTable table = root.findTable("ARTICLE");
59
        final SQLTable table = root.findTable("ARTICLE");
58
        final SQLTable tableArtElt = root.findTable("ARTICLE_ELEMENT");
60
        final SQLTable tableArtElt = root.findTable("ARTICLE_ELEMENT");
59
 
61
 
60
        Map<String, SQLRowValues> articles = getArticles();
62
        Map<String, Tuple2<SQLRowValues, SQLRowValues>> articles = getArticles();
61
 
63
 
62
        final DataImporter importer = new DataImporter(table) {
64
        final DataImporter importer = new DataImporter(table) {
63
            @Override
65
            @Override
64
            protected void customizeRowValuesToFetch(SQLRowValues vals) {
66
            protected void customizeRowValuesToFetch(SQLRowValues vals) {
65
 
67
 
Line 79... Line 81...
79
        // TODO ne pas vider les stocks des kits, recalculer les stocks des kits
81
        // TODO ne pas vider les stocks des kits, recalculer les stocks des kits
80
 
82
 
81
        SQLRowValues rowVals = new SQLRowValues(table.getTable("ETAT_STOCK"));
83
        SQLRowValues rowVals = new SQLRowValues(table.getTable("ETAT_STOCK"));
82
        rowVals.put("DATE", today);
84
        rowVals.put("DATE", today);
83
        rowVals.put("INVENTAIRE", Boolean.TRUE);
85
        rowVals.put("INVENTAIRE", Boolean.TRUE);
-
 
86
        rowVals.put("ID_DEPOT_STOCK", this.depot.getID());
84
        SQLRow rowEtat = rowVals.commit();
87
        SQLRow rowEtat = rowVals.commit();
85
 
88
 
86
        for (int i = 1; i < m.getRowCount(); i++) {
89
        for (int i = 1; i < m.getRowCount(); i++) {
87
            List<Object> o = m.getLineValuesAt(i);
90
            List<Object> o = m.getLineValuesAt(i);
88
            if (o.size() >= 5) {
91
            if (o.size() >= 5) {
Line 93... Line 96...
93
                    final String stringQty = o.get(4).toString();
96
                    final String stringQty = o.get(4).toString();
94
                    Double qty = stringQty.trim().length() == 0 ? 0 : Double.valueOf(stringQty);
97
                    Double qty = stringQty.trim().length() == 0 ? 0 : Double.valueOf(stringQty);
95
                    final String stringQtyOld = o.get(3).toString();
98
                    final String stringQtyOld = o.get(3).toString();
96
                    float qtyOld = stringQtyOld.trim().length() == 0 ? 0 : Float.valueOf(stringQtyOld);
99
                    float qtyOld = stringQtyOld.trim().length() == 0 ? 0 : Float.valueOf(stringQtyOld);
97
 
100
 
98
                    SQLRowValues match = articles.get(code);
101
                    Tuple2<SQLRowValues, SQLRowValues> match = articles.get(code);
99
                    if (match != null) {
102
                    if (match != null) {
100
 
103
 
101
                        SQLRowAccessor stockValues = match.getForeign("ID_STOCK");
104
                        SQLRowAccessor stockValues = match.get1();
102
 
105
 
103
                        final SQLTable tableMvt = table.getTable("MOUVEMENT_STOCK");
106
                        final SQLTable tableMvt = table.getTable("MOUVEMENT_STOCK");
104
                        SQLRowValues rowValsMvtStockClotureFermeture = new SQLRowValues(tableMvt);
107
                        SQLRowValues rowValsMvtStockClotureFermeture = new SQLRowValues(tableMvt);
105
                        rowValsMvtStockClotureFermeture.put("QTE", -qtyOld);
108
                        rowValsMvtStockClotureFermeture.put("QTE", -qtyOld);
106
                        rowValsMvtStockClotureFermeture.put("NOM", "Clôture stock avant inventaire");
109
                        rowValsMvtStockClotureFermeture.put("NOM", "Clôture stock avant inventaire");
107
                        rowValsMvtStockClotureFermeture.put("ID_ARTICLE", match.getID());
110
                        rowValsMvtStockClotureFermeture.put("ID_ARTICLE", match.get0().getID());
108
                        rowValsMvtStockClotureFermeture.put("DATE", today);
111
                        rowValsMvtStockClotureFermeture.put("DATE", today);
109
                        rowValsMvtStockClotureFermeture.put("REEL", Boolean.TRUE);
112
                        rowValsMvtStockClotureFermeture.put("REEL", Boolean.TRUE);
110
                        rowValsMvtStockClotureFermeture.put("ID_STOCK", stockValues.getID());
113
                        rowValsMvtStockClotureFermeture.put("ID_STOCK", stockValues.getID());
111
 
114
 
112
                        BigDecimal prc = getPRC(match, Math.round(qtyOld), today);
115
                        BigDecimal prc = getPRC(match.get0(), Math.round(qtyOld), today);
113
                        if (prc == null) {
116
                        if (prc == null) {
114
                            prc = BigDecimal.ZERO;
117
                            prc = BigDecimal.ZERO;
115
                        }
118
                        }
116
                        if (tableMvt.contains("PRICE")) {
119
                        if (tableMvt.contains("PRICE")) {
117
                            rowValsMvtStockClotureFermeture.put("PRICE", prc);
120
                            rowValsMvtStockClotureFermeture.put("PRICE", prc);
Line 125... Line 128...
125
                        rowValsItem.put("PA", prc);
128
                        rowValsItem.put("PA", prc);
126
                        rowValsItem.put("PV", BigDecimal.ZERO);
129
                        rowValsItem.put("PV", BigDecimal.ZERO);
127
                        rowValsItem.put("QTE", qtyOld);
130
                        rowValsItem.put("QTE", qtyOld);
128
                        rowValsItem.put("T_PA", prc.multiply(new BigDecimal(qtyOld)));
131
                        rowValsItem.put("T_PA", prc.multiply(new BigDecimal(qtyOld)));
129
                        rowValsItem.put("T_PV", BigDecimal.ZERO);
132
                        rowValsItem.put("T_PV", BigDecimal.ZERO);
130
                        rowValsItem.put("CODE", match.getString("CODE"));
133
                        rowValsItem.put("CODE", match.get0().getString("CODE"));
131
                        rowValsItem.put("NOM", match.getString("NOM"));
134
                        rowValsItem.put("NOM", match.get0().getString("NOM"));
132
                        rowValsItem.put("ID_ARTICLE", match.getID());
135
                        rowValsItem.put("ID_ARTICLE", match.get0().getID());
133
                        rowValsItem.getGraph().store(StoreMode.COMMIT, false);
136
                        rowValsItem.getGraph().store(StoreMode.COMMIT, false);
134
 
137
 
135
                        SQLRowValues rowValsMvtStockClotureOuverture = new SQLRowValues(tableMvt);
138
                        SQLRowValues rowValsMvtStockClotureOuverture = new SQLRowValues(tableMvt);
136
                        rowValsMvtStockClotureOuverture.put("QTE", qty);
139
                        rowValsMvtStockClotureOuverture.put("QTE", qty);
137
                        rowValsMvtStockClotureOuverture.put("NOM", "Mise en stock inventaire");
140
                        rowValsMvtStockClotureOuverture.put("NOM", "Mise en stock inventaire");
138
                        rowValsMvtStockClotureOuverture.put("ID_ETAT_STOCK", rowEtat.getID());
141
                        rowValsMvtStockClotureOuverture.put("ID_ETAT_STOCK", rowEtat.getID());
139
                        rowValsMvtStockClotureOuverture.put("ID_ARTICLE", match.getID());
142
                        rowValsMvtStockClotureOuverture.put("ID_ARTICLE", match.get0().getID());
140
                        rowValsMvtStockClotureOuverture.put("DATE", today);
143
                        rowValsMvtStockClotureOuverture.put("DATE", today);
141
                        rowValsMvtStockClotureOuverture.put("REEL", Boolean.TRUE);
144
                        rowValsMvtStockClotureOuverture.put("REEL", Boolean.TRUE);
142
                        rowValsMvtStockClotureOuverture.put("ID_STOCK", stockValues.getID());
145
                        rowValsMvtStockClotureOuverture.put("ID_STOCK", stockValues.getID());
143
                        rowValsMvtStockClotureOuverture.put("OUVERTURE", Boolean.TRUE);
146
                        rowValsMvtStockClotureOuverture.put("OUVERTURE", Boolean.TRUE);
144
                        if (tableMvt.contains("PRICE")) {
147
                        if (tableMvt.contains("PRICE")) {
145
                            rowValsMvtStockClotureOuverture.put("PRICE", getPRC(match, qty.intValue(), today));
148
                            rowValsMvtStockClotureOuverture.put("PRICE", getPRC(match.get0(), qty.intValue(), today));
146
                        }
149
                        }
147
                        rowValsMvtStockClotureOuverture.getGraph().store(StoreMode.COMMIT, false);
150
                        rowValsMvtStockClotureOuverture.getGraph().store(StoreMode.COMMIT, false);
148
 
151
 
149
                        if (!match.isForeignEmpty("ID_STOCK")) {
152
                        // if (!match.isForeignEmpty("ID_STOCK")) {
150
                            match.getForeign("ID_STOCK").createEmptyUpdateRow().put("QTE_REEL", qty).commit();
153
                        // match.getForeign("ID_STOCK").createEmptyUpdateRow().put("QTE_REEL",
-
 
154
                        // qty).commit();
151
                        } else {
155
                        // } else {
152
                            final SQLRowValues createEmptyUpdateRow = match.createEmptyUpdateRow();
156
                        final SQLRowValues createEmptyUpdateRow = match.get1().createEmptyUpdateRow();
153
                            createEmptyUpdateRow.putRowValues("ID_STOCK").put("QTE_REEL", qty);
157
                        createEmptyUpdateRow.put("QTE_REEL", qty);
154
                            createEmptyUpdateRow.getGraph().store(StoreMode.COMMIT, false);
158
                        createEmptyUpdateRow.getGraph().store(StoreMode.COMMIT, false);
155
 
159
 
156
                        }
160
                        // }
157
 
161
 
158
                    } else {
162
                    } else {
159
                        System.err.println("Aucun article correspondant au code " + code);
163
                        System.err.println("Aucun article correspondant au code " + code);
160
                    }
164
                    }
161
                }
165
                }
Line 303... Line 307...
303
        ProductComponent comp = new ProductComponent(rowVals, new BigDecimal(qty), null, null);
307
        ProductComponent comp = new ProductComponent(rowVals, new BigDecimal(qty), null, null);
304
        return comp.getPRC(d);
308
        return comp.getPRC(d);
305
        // return result;
309
        // return result;
306
    }
310
    }
307
 
311
 
308
    private Map<String, SQLRowValues> getArticles() throws SQLException {
312
    private Map<String, Tuple2<SQLRowValues, SQLRowValues>> getArticles() throws SQLException {
309
        final SQLTable table = Configuration.getInstance().getRoot().findTable("ARTICLE");
313
        final SQLTable table = Configuration.getInstance().getRoot().findTable("ARTICLE");
310
        SQLRowValues graph = new SQLRowValues(table);
314
        SQLRowValues graph = new SQLRowValues(table);
311
        graph.put("ID", null);
315
        graph.put("ID", null);
312
        graph.put("CODE", null);
316
        graph.put("CODE", null);
313
        graph.put("NOM", null);
317
        graph.put("NOM", null);
314
        graph.put("NOM", null);
318
        graph.put("NOM", null);
-
 
319
        final SQLTable foreignTableStock = table.getForeignTable("ID_STOCK");
-
 
320
        SQLRowValues graphStock = new SQLRowValues(foreignTableStock);
315
        graph.putRowValues("ID_STOCK").putNulls("ID_DEPOT_STOCK", "ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");
321
        graphStock.putNulls("ID_DEPOT_STOCK", "ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");
-
 
322
        graphStock.put("ID_ARTICLE", graph);
316
 
323
 
317
        final SQLTable tableArtElt = table.getTable("ARTICLE_ELEMENT");
324
        final SQLTable tableArtElt = table.getTable("ARTICLE_ELEMENT");
318
        SQLRowValues artElt = new SQLRowValues(tableArtElt);
325
        SQLRowValues artElt = new SQLRowValues(tableArtElt);
319
        artElt.put("ID", null);
326
        artElt.put("ID", null);
320
        artElt.put("QTE", null);
327
        artElt.put("QTE", null);
321
        artElt.put("QTE_UNITAIRE", null);
328
        artElt.put("QTE_UNITAIRE", null);
322
        artElt.put("ID_ARTICLE_PARENT", graph);
329
        artElt.put("ID_ARTICLE_PARENT", graph);
-
 
330
        final SQLRowValues articleParent = artElt.putRowValues("ID_ARTICLE");
-
 
331
        articleParent.putNulls("ID", "CODE", "NOM");
-
 
332
        SQLRowValues graphStockItem = new SQLRowValues(foreignTableStock);
323
        artElt.putRowValues("ID_ARTICLE").putNulls("ID", "CODE", "NOM").putRowValues("ID_STOCK").putNulls("ID_DEPOT_STOCK", "QTE_TH", "QTE_REEL", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");
333
        graphStockItem.putNulls("ID_DEPOT_STOCK", "ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");
-
 
334
        graphStockItem.put("ID_ARTICLE", articleParent);
324
 
335
 
325
        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(graph);
336
        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(graph);
326
        List<SQLRowValues> results = fetcher.fetch();
337
        List<SQLRowValues> results = fetcher.fetch();
327
 
338
 
328
        Calendar c = Calendar.getInstance();
339
        Calendar c = Calendar.getInstance();
329
        // c.set(Calendar.DAY_OF_MONTH, 1);
340
        // c.set(Calendar.DAY_OF_MONTH, 1);
330
        c.add(Calendar.MONTH, -2);
341
        c.add(Calendar.MONTH, -2);
331
        c.set(Calendar.DAY_OF_MONTH, 31);
342
        c.set(Calendar.DAY_OF_MONTH, 31);
332
        Date dEndYear = c.getTime();
343
        Date dEndYear = c.getTime();
333
 
344
 
334
        Map<String, SQLRowValues> vals = new HashMap<String, SQLRowValues>();
345
        Map<String, Tuple2<SQLRowValues, SQLRowValues>> vals = new HashMap<String, Tuple2<SQLRowValues, SQLRowValues>>();
335
        for (SQLRowValues sqlRowValues : results) {
346
        for (SQLRowValues sqlRowValues : results) {
336
            final String code = sqlRowValues.getString("CODE");
347
            final String code = sqlRowValues.getString("CODE");
-
 
348
 
-
 
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()) {
337
            vals.put(code, sqlRowValues);
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));
338
 
362
 
339
            final Set<SQLRowValues> referentRows = sqlRowValues.getReferentRows(tableArtElt.getField("ID_ARTICLE_PARENT"));
363
            final Set<SQLRowValues> referentRows = sqlRowValues.getReferentRows(tableArtElt.getField("ID_ARTICLE_PARENT"));
340
            if (referentRows.size() == 0) {
364
            if (referentRows.size() == 0) {
341
                // if (!sqlRowValues.isForeignEmpty("ID_STOCK")) {
365
                // if (!sqlRowValues.isForeignEmpty("ID_STOCK")) {
342
                // SQLRowAccessor rowValsStock = sqlRowValues.getForeign("ID_STOCK");
366
                // SQLRowAccessor rowValsStock = sqlRowValues.getForeign("ID_STOCK");