OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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

Rev 149 Rev 174
Line 22... Line 22...
22
import org.openconcerto.sql.element.SQLElementDirectory;
22
import org.openconcerto.sql.element.SQLElementDirectory;
23
import org.openconcerto.sql.model.AliasedField;
23
import org.openconcerto.sql.model.AliasedField;
24
import org.openconcerto.sql.model.AliasedTable;
24
import org.openconcerto.sql.model.AliasedTable;
25
import org.openconcerto.sql.model.SQLRow;
25
import org.openconcerto.sql.model.SQLRow;
26
import org.openconcerto.sql.model.SQLRowAccessor;
26
import org.openconcerto.sql.model.SQLRowAccessor;
-
 
27
import org.openconcerto.sql.model.SQLRowValues;
-
 
28
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
27
import org.openconcerto.sql.model.SQLSelect;
29
import org.openconcerto.sql.model.SQLSelect;
28
import org.openconcerto.sql.model.SQLSelectJoin;
30
import org.openconcerto.sql.model.SQLSelectJoin;
29
import org.openconcerto.sql.model.SQLTable;
31
import org.openconcerto.sql.model.SQLTable;
30
import org.openconcerto.sql.model.Where;
32
import org.openconcerto.sql.model.Where;
31
import org.openconcerto.utils.Tuple2;
33
import org.openconcerto.utils.Tuple2;
Line 60... Line 62...
60
 
62
 
61
    public static final String TEMPLATE_PROPERTY_NAME = DEFAULT_PROPERTY_NAME;
63
    public static final String TEMPLATE_PROPERTY_NAME = DEFAULT_PROPERTY_NAME;
62
 
64
 
63
    private Timestamp du, au;
65
    private Timestamp du, au;
64
    public boolean ticketCaisse = false;
66
    public boolean ticketCaisse = false;
-
 
67
    public boolean facture = false;
65
 
68
 
66
    public EtatVentesXmlSheet(Date du, Date au, boolean ticketCaisse) {
69
    public EtatVentesXmlSheet(Date du, Date au, boolean ticketCaisse, boolean facture) {
67
        super();
70
        super();
68
        this.printer = PrinterNXProps.getInstance().getStringProperty("BonPrinter");
71
        this.printer = PrinterNXProps.getInstance().getStringProperty("BonPrinter");
69
        this.ticketCaisse = ticketCaisse;
72
        this.ticketCaisse = ticketCaisse;
-
 
73
        this.facture = facture;
70
        if (du != null) {
74
        if (du != null) {
71
            final Calendar c1 = Calendar.getInstance();
75
            final Calendar c1 = Calendar.getInstance();
72
            c1.setTime(du);
76
            c1.setTime(du);
73
            c1.set(Calendar.HOUR_OF_DAY, 0);
77
            c1.set(Calendar.HOUR_OF_DAY, 0);
74
            c1.set(Calendar.MINUTE, 0);
78
            c1.set(Calendar.MINUTE, 0);
Line 126... Line 130...
126
        if (style == null) {
130
        if (style == null) {
127
            style = new HashMap<Integer, String>();
131
            style = new HashMap<Integer, String>();
128
            styleAllSheetValues.put(0, style);
132
            styleAllSheetValues.put(0, style);
129
        }
133
        }
130
 
134
 
-
 
135
        final ArrayList<Map<String, Object>> listValuesStock = new ArrayList<>();
-
 
136
        final Map<Integer, String> styleStock = new HashMap<>();
-
 
137
 
131
        // Ventes
138
        // Ventes
-
 
139
        final SQLTable foreignTableArticle = tableFactureElement.getForeignTable("ID_ARTICLE");
132
        {
140
        {
133
 
141
 
134
            final AliasedTable tableModeReglement1 = new AliasedTable(tableModeReglement, MODE1);
142
            final AliasedTable tableModeReglement1 = new AliasedTable(tableModeReglement, MODE1);
135
            final AliasedTable tableModeReglement2 = new AliasedTable(tableModeReglement, MODE2);
143
            final AliasedTable tableModeReglement2 = new AliasedTable(tableModeReglement, MODE2);
136
            final AliasedTable tableTicket = new AliasedTable(eltTicketCaisse.getTable(), "ticket");
144
            final AliasedTable tableTicket = new AliasedTable(eltTicketCaisse.getTable(), "ticket");
137
 
145
 
-
 
146
            // Stock
-
 
147
            SQLRowValues rowValsArtStock = new SQLRowValues(foreignTableArticle);
-
 
148
            rowValsArtStock.putNulls("ID", "CODE", "NOM");
-
 
149
            rowValsArtStock.putRowValues("ID_STOCK").putNulls("QTE_REEL", "QTE_TH", "QTE_MIN", "QTE_RECEPT_ATTENTE", "QTE_LIV_ATTENTE");
-
 
150
            SQLRowValuesListFetcher fetcherStock = SQLRowValuesListFetcher.create(rowValsArtStock);
-
 
151
            List<SQLRowValues> resultStock = fetcherStock.fetch();
-
 
152
            Map<Integer, SQLRowValues> mapStock = new HashMap<>();
-
 
153
            for (SQLRowValues sqlRowValues : resultStock) {
-
 
154
                mapStock.put(sqlRowValues.getID(), sqlRowValues);
-
 
155
            }
-
 
156
 
138
            // Requete Pour obtenir les quantités pour chaque type de réglement
157
            // Requete Pour obtenir les quantités pour chaque type de réglement
139
            SQLSelect sel = new SQLSelect();
158
            SQLSelect sel = new SQLSelect();
140
 
159
 
141
            sel.addSelect(tableFactureElement.getField("CODE"));
160
            sel.addSelect(tableFactureElement.getField("CODE"));
142
            sel.addSelect(tableFactureElement.getField("NOM"));
161
            sel.addSelect(tableFactureElement.getField("NOM"));
Line 191... Line 210...
191
            selQte.addSelect(tableFactureElement.getField("T_PA_HT"), "SUM");
210
            selQte.addSelect(tableFactureElement.getField("T_PA_HT"), "SUM");
192
            selQte.addSelect(tableFactureElement.getField("T_PV_HT"), "SUM");
211
            selQte.addSelect(tableFactureElement.getField("T_PV_HT"), "SUM");
193
            selQte.addSelect(tableFactureElement.getField("T_PV_TTC"), "SUM");
212
            selQte.addSelect(tableFactureElement.getField("T_PV_TTC"), "SUM");
194
            selQte.addSelect(tableFactureElement.getField("ID_TAXE"));
213
            selQte.addSelect(tableFactureElement.getField("ID_TAXE"));
195
            selQte.addSelect(tableFactureElement.getField("ID_ARTICLE"));
214
            selQte.addSelect(tableFactureElement.getField("ID_ARTICLE"));
196
            if (!this.ticketCaisse) {
215
            if (this.ticketCaisse) {
-
 
216
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(Where.FALSE);
-
 
217
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
-
 
218
            } else if (this.facture) {
197
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
219
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
-
 
220
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(Where.FALSE);
198
            } else {
221
            } else {
199
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(Where.FALSE);
222
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
200
            }
-
 
201
            selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
223
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
-
 
224
            }
202
            SQLSelectJoin joinArt2 = selQte.addJoin("LEFT", tableFactureElement.getField("ID_ARTICLE"));
225
            SQLSelectJoin joinArt2 = selQte.addJoin("LEFT", tableFactureElement.getField("ID_ARTICLE"));
203
            SQLSelectJoin joinFamArt2 = selQte.addJoin("LEFT", joinArt2.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
226
            SQLSelectJoin joinFamArt2 = selQte.addJoin("LEFT", joinArt2.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
204
            selQte.addSelect(joinFamArt2.getJoinedTable().getField("NOM"));
227
            selQte.addSelect(joinFamArt2.getJoinedTable().getField("NOM"));
205
            // FIXME traiter le cas du!=null et au==null et vice
228
            // FIXME traiter le cas du!=null et au==null et vice
206
            // versa
229
            // versa
Line 232... Line 255...
232
                } else {
255
                } else {
233
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(tvaID.intValue());
256
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(tvaID.intValue());
234
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create(t.get0().add((BigDecimal) ht), t.get1().add(ttc)));
257
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create(t.get0().add((BigDecimal) ht), t.get1().add(ttc)));
235
                }
258
                }
236
                Number articleID = (Number) sqlRow[7];
259
                Number articleID = (Number) sqlRow[7];
237
                ArticleVendu a = new ArticleVendu(code, nom, qteVendu.intValue(), (BigDecimal) ht, (BigDecimal) ha, ttc, tvaID.intValue(),
260
                ArticleVendu a = new ArticleVendu(code, nom, qteVendu.intValue(), (BigDecimal) ht, (BigDecimal) ha, ttc, tvaID.intValue(), foreignTableArticle.getRow(articleID.intValue()));
238
                        tableFactureElement.getForeignTable("ID_ARTICLE").getRow(articleID.intValue()));
-
 
-
 
261
 
239
                map.put(articleID + "##" + code + "##" + nom + "##" + tvaID, a);
262
                map.put(articleID + "##" + code + "##" + nom + "##" + tvaID, a);
240
 
263
 
241
            }
264
            }
242
 
265
 
243
            List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());
266
            List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());
Line 254... Line 277...
254
                    famille = "Non classés";
277
                    famille = "Non classés";
255
                    Map<String, Object> mValues = new HashMap<String, Object>();
278
                    Map<String, Object> mValues = new HashMap<String, Object>();
256
                    mValues.put("NOM", famille);
279
                    mValues.put("NOM", famille);
257
                    style.put(listValues.size(), "Titre 1");
280
                    style.put(listValues.size(), "Titre 1");
258
                    listValues.add(mValues);
281
                    listValues.add(mValues);
-
 
282
                    styleStock.put(listValuesStock.size(), "Titre 1");
-
 
283
                    listValuesStock.add(mValues);
259
                } else if (valueFam != null && !valueFam.toString().equalsIgnoreCase(famille)) {
284
                } else if (valueFam != null && !valueFam.toString().equalsIgnoreCase(famille)) {
260
                    famille = valueFam.toString();
285
                    famille = valueFam.toString();
261
                    Map<String, Object> mValues = new HashMap<String, Object>();
286
                    Map<String, Object> mValues = new HashMap<String, Object>();
262
                    mValues.put("NOM", famille);
287
                    mValues.put("NOM", famille);
263
                    style.put(listValues.size(), "Titre 1");
288
                    style.put(listValues.size(), "Titre 1");
264
                    listValues.add(mValues);
289
                    listValues.add(mValues);
-
 
290
                    styleStock.put(listValuesStock.size(), "Titre 1");
-
 
291
                    listValuesStock.add(mValues);
265
                }
292
                }
266
 
293
 
267
                Map<String, Object> mValues = new HashMap<String, Object>();
294
                Map<String, Object> mValues = new HashMap<String, Object>();
268
 
295
 
269
                String code = (String) obj[0];
296
                String code = (String) obj[0];
Line 289... Line 316...
289
                    mValues.put("FAMILLE", valueFam);
316
                    mValues.put("FAMILLE", valueFam);
290
                    totalTPA = totalTPA.add(a.ha);
317
                    totalTPA = totalTPA.add(a.ha);
291
                    totalTPVTTC = totalTPVTTC.add(a.ttc);
318
                    totalTPVTTC = totalTPVTTC.add(a.ttc);
292
                    style.put(listValues.size(), "Normal");
319
                    style.put(listValues.size(), "Normal");
293
                    listValues.add(mValues);
320
                    listValues.add(mValues);
-
 
321
 
-
 
322
                    Map<String, Object> mValuesStock = new HashMap<String, Object>();
-
 
323
                    mValuesStock.put("CODE", code);
-
 
324
                    mValuesStock.put("NOM", nom);
-
 
325
                    mValuesStock.put("QTE", a.qte);
-
 
326
                    if (mapStock.containsKey(articleID)) {
-
 
327
                        SQLRowValues rowValsArt = mapStock.get(articleID);
-
 
328
                        if (rowValsArt.getObject("ID_STOCK") != null && !rowValsArt.isForeignEmpty("ID_STOCK")) {
-
 
329
                            SQLRowAccessor rowValsStock = rowValsArt.getForeign("ID_STOCK");
-
 
330
                            mValuesStock.put("QTE_TH", rowValsStock.getObject("QTE_TH"));
-
 
331
                            mValuesStock.put("QTE_REEL", rowValsStock.getObject("QTE_REEL"));
-
 
332
                            mValuesStock.put("QTE_MIN", rowValsStock.getObject("QTE_MIN"));
-
 
333
                            mValuesStock.put("QTE_RECEPT_ATTENTE", rowValsStock.getObject("QTE_RECEPT_ATTENTE"));
-
 
334
                            mValuesStock.put("QTE_LIV_ATTENTE", rowValsStock.getObject("QTE_LIV_ATTENTE"));
-
 
335
                        }
-
 
336
                        styleStock.put(listValuesStock.size(), "Normal");
-
 
337
                        listValuesStock.add(mValuesStock);
-
 
338
                    }
-
 
339
 
294
                }
340
                }
295
                // System.out.println("EtatVentesXmlSheet.createListeValues():" + listValues);
341
                // System.out.println("EtatVentesXmlSheet.createListeValues():" + listValues);
296
            }
342
            }
297
        }
343
        }
298
        // Liste des ventes comptoirs
344
        // Liste des ventes comptoirs
Line 438... Line 484...
438
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(tvaID.intValue());
484
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(tvaID.intValue());
439
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create(t.get0().subtract((BigDecimal) ht), t.get1().subtract(ttc)));
485
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create(t.get0().subtract((BigDecimal) ht), t.get1().subtract(ttc)));
440
                }
486
                }
441
                Number articleID = (Number) sqlRow[7];
487
                Number articleID = (Number) sqlRow[7];
442
                ArticleVendu a = new ArticleVendu(code, nom, -qteVendu.intValue(), ((BigDecimal) ht).negate(), ((BigDecimal) ha).negate(), ttc.negate(), tvaID.intValue(),
488
                ArticleVendu a = new ArticleVendu(code, nom, -qteVendu.intValue(), ((BigDecimal) ht).negate(), ((BigDecimal) ha).negate(), ttc.negate(), tvaID.intValue(),
443
                        tableFactureElement.getForeignTable("ID_ARTICLE").getRow(articleID.intValue()));
489
                        foreignTableArticle.getRow(articleID.intValue()));
444
                map.put(articleID + "##" + code + "##" + nom + "##" + tvaID, a);
490
                map.put(articleID + "##" + code + "##" + nom + "##" + tvaID, a);
445
 
491
 
446
            }
492
            }
447
 
493
 
448
            List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());
494
            List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());
Line 711... Line 757...
711
        Map<String, Object> valuesTVA = new HashMap<String, Object>();
757
        Map<String, Object> valuesTVA = new HashMap<String, Object>();
712
        valuesTVA.put("TOTAL_TVA", totalTTC.subtract(totalHT).subtract(totalTTCHA.subtract(totalHTHA)));
758
        valuesTVA.put("TOTAL_TVA", totalTTC.subtract(totalHT).subtract(totalTTCHA.subtract(totalHTHA)));
713
        this.listAllSheetValues.put(3, listValuesTVA);
759
        this.listAllSheetValues.put(3, listValuesTVA);
714
        valuesTVA.put("DATE", periode);
760
        valuesTVA.put("DATE", periode);
715
        this.mapAllSheetValues.put(3, valuesTVA);
761
        this.mapAllSheetValues.put(3, valuesTVA);
-
 
762
 
-
 
763
        this.listAllSheetValues.put(4, listValuesStock);
-
 
764
        this.styleAllSheetValues.put(4, styleStock);
-
 
765
        this.mapAllSheetValues.put(4, values);
716
    }
766
    }
717
 
767
 
718
    public static SQLRow rowDefaultCptService, rowDefaultCptProduit;
768
    public static SQLRow rowDefaultCptService, rowDefaultCptProduit;
719
    static {
769
    static {
720
        final SQLTable tablePrefCompte = Configuration.getInstance().getRoot().findTable("PREFS_COMPTE");
770
        final SQLTable tablePrefCompte = Configuration.getInstance().getRoot().findTable("PREFS_COMPTE");