| 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");
|
