OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
185 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011-2019 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.report;
15
 
16
import org.openconcerto.erp.core.finance.accounting.report.GrandLivrePDF;
17
import org.openconcerto.sql.model.DBRoot;
18
import org.openconcerto.sql.model.SQLField;
19
import org.openconcerto.sql.model.SQLRow;
20
import org.openconcerto.sql.model.SQLRowAccessor;
21
import org.openconcerto.sql.model.SQLRowListRSH;
22
import org.openconcerto.sql.model.SQLRowValues;
23
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
24
import org.openconcerto.sql.model.SQLSelect;
25
import org.openconcerto.sql.model.SQLTable;
26
import org.openconcerto.sql.model.Where;
27
import org.openconcerto.utils.StringUtils;
28
import org.openconcerto.utils.Tuple2;
29
 
30
import java.awt.Color;
31
import java.io.ByteArrayOutputStream;
32
import java.io.File;
33
import java.io.IOException;
34
import java.io.InputStream;
35
import java.math.BigDecimal;
36
import java.text.DecimalFormat;
37
import java.text.DecimalFormatSymbols;
38
import java.util.ArrayList;
39
import java.util.Collections;
40
import java.util.Comparator;
41
import java.util.HashMap;
42
import java.util.List;
43
import java.util.Locale;
44
import java.util.Map;
45
 
46
import org.apache.pdfbox.pdmodel.PDDocument;
47
import org.apache.pdfbox.pdmodel.PDPage;
48
import org.apache.pdfbox.pdmodel.PDPageContentStream;
49
import org.apache.pdfbox.pdmodel.common.PDRectangle;
50
import org.apache.pdfbox.pdmodel.font.PDType1Font;
51
import org.apache.pdfbox.pdmodel.graphics.image.PDImageXObject;
52
 
53
public class StockReportPDF {
54
 
55
    private static final int LINE_HEIGHT = 10;
56
 
57
    private static final float COL_1_SIZE = 80;
58
    private static final float COL_2_SIZE = 300;
59
    private static final float COL_3_SIZE = 30;
60
    private static final float COL_4_SIZE = 40;
61
    private static final float COL_5_SIZE = 50;
62
    private static final float COL_1_X = 50;
63
 
64
    private static final float COL_2_X = COL_1_X + COL_1_SIZE;
65
    private static final float COL_3_X = COL_2_X + COL_2_SIZE;
66
    private static final float COL_4_X = COL_3_X + COL_3_SIZE;
67
    private static final float COL_5_X = COL_4_X + COL_4_SIZE;
68
 
69
    private final PDDocument doc;
70
    private final List<SQLRow> etatsStock = new ArrayList<>();
71
    private final String companyName;
72
    private final String title;
73
    private int y = 0;
74
    private PDPageContentStream content;
75
 
76
    private final DecimalFormat decimalFormat = new DecimalFormat("#,##0.00", DecimalFormatSymbols.getInstance(Locale.FRANCE));
77
    private final DecimalFormat decimalFormatQuantity = new DecimalFormat("#,##0.##", DecimalFormatSymbols.getInstance(Locale.FRANCE));
78
 
79
    private Map<SQLRow, BigDecimal> totaux = new HashMap<>();
80
 
81
    public StockReportPDF(String companyName, String title) throws IOException {
82
        this.companyName = companyName;
83
        this.title = title;
84
 
85
        this.doc = new PDDocument();
86
        final PDPage page = new PDPage(PDRectangle.A4);
87
        this.doc.addPage(page);
88
 
89
        this.content = new PDPageContentStream(this.doc, page);
90
        this.y = drawHeader(true, true);
91
 
92
    }
93
 
94
    private int drawHeader(boolean full, boolean columns) throws IOException {
95
        this.y = 795;
96
        this.content.beginText();
97
        this.content.setFont(PDType1Font.HELVETICA_BOLD, 14);
98
        this.content.newLineAtOffset(40, this.y);
99
        this.content.showText("INVENTAIRE    " + StringUtils.cleanPDFString(this.companyName.toUpperCase()));
100
        this.content.endText();
101
        this.y -= 17;
102
        if (full) {
103
            this.content.beginText();
104
            this.content.setFont(PDType1Font.HELVETICA, 12);
105
            this.content.newLineAtOffset(40, this.y);
106
            this.content.showText(StringUtils.cleanPDFString(this.title));
107
            this.content.endText();
108
 
109
            this.y -= 20;
110
        }
111
        this.content.setFont(PDType1Font.HELVETICA_BOLD, 7);
112
        if (columns) {
113
            this.content.beginText();
114
            this.content.newLineAtOffset(COL_1_X + 5, this.y);
115
            this.content.showText("CODE");
116
            this.content.endText();
117
 
118
            this.content.beginText();
119
            this.content.newLineAtOffset(COL_2_X, this.y);
120
            this.content.showText("ARTICLE");
121
            this.content.endText();
122
 
123
            drawRightAlign(this.content, COL_3_X, this.y, COL_3_SIZE, "QUANTITE");
124
            drawRightAlign(this.content, COL_4_X, this.y, COL_4_SIZE, "PRIX U.");
125
            drawRightAlign(this.content, COL_5_X, this.y, COL_5_SIZE, "TOTAL");
126
        }
127
        this.y -= 4;
128
        this.content.setStrokingColor(Color.BLACK);
129
        this.content.setLineWidth(1f);
130
        this.content.moveTo(COL_1_X - 4, this.y);
131
        this.content.lineTo(COL_5_X + COL_5_SIZE + 4, this.y);
132
        this.content.stroke();
133
 
134
        return this.y;
135
    }
136
 
137
    private static void drawRightAlign(PDPageContentStream content, float x, float y, float width, String text) throws IOException {
138
        content.beginText();
139
        final float w = PDType1Font.HELVETICA.getStringWidth(StringUtils.cleanPDFString(text)) / 1000.0f * 7f;
140
        content.newLineAtOffset(x + width - w, y);
141
        content.showText(StringUtils.cleanPDFString(text));
142
        content.endText();
143
    }
144
 
145
    private void drawFamilleName(String name) throws IOException {
146
        this.y -= LINE_HEIGHT;
147
 
148
        this.content.setFont(PDType1Font.HELVETICA_BOLD, 7);
149
 
150
        this.content.beginText();
151
        this.content.newLineAtOffset(COL_1_X, this.y);
152
        this.content.showText(StringUtils.cleanPDFString(name));
153
        this.content.endText();
154
 
155
    }
156
 
157
    /**
158
     *
159
     * @param root
160
     * @param mapDeclinaison map de ID_ARTICLE_DECLINAISON_... vers Tuple2<Nom, Ordre>
161
     */
162
 
163
    private void fillDeclinaisons(DBRoot root, Map<String, Map<Integer, Tuple2<String, BigDecimal>>> mapDeclinaisons) {
164
        List<String> tablesDeclinaisons = new ArrayList<>();
165
        List<String> declinaisonsFieldNames = new ArrayList<>();
166
        SQLTable tableArticle = root.getTable("ARTICLE");
167
 
168
        for (SQLField f : tableArticle.getFields()) {
169
            if (f.getName().startsWith("ID_ARTICLE_DECLINAISON_")) {
170
                declinaisonsFieldNames.add(f.getName());
171
                tablesDeclinaisons.add(f.getName().substring("ID_".length()));
172
            }
173
        }
174
 
175
        for (String table : tablesDeclinaisons) {
176
            SQLTable t = tableArticle.getTable(table);
177
            final SQLSelect selDecl = new SQLSelect();
178
            selDecl.addSelect(t.getKey());
179
            selDecl.addSelect(t.getField("NOM"));
180
            selDecl.addSelect(t.getField("ORDRE"));
181
            Map<Integer, Tuple2<String, BigDecimal>> m = new HashMap<>();
182
            mapDeclinaisons.put("ID_" + table, m);
183
            for (SQLRow row : SQLRowListRSH.execute(selDecl)) {
184
                m.put(row.getID(), Tuple2.create(row.getString("NOM"), row.getBigDecimal("ORDRE")));
185
            }
186
        }
187
    }
188
 
189
    private void drawLine(String code, String article, BigDecimal qte, BigDecimal prixUnitaire, BigDecimal total) throws IOException {
190
        String s = StringUtils.splitString(article, 70);
191
        final List<String> parts = StringUtils.fastSplitTrimmed(s, '\n');
192
        this.y -= LINE_HEIGHT;
193
 
194
        if (this.y - LINE_HEIGHT * parts.size() < 50) {
195
            final PDPage page = new PDPage(PDRectangle.A4);
196
            this.doc.addPage(page);
197
            if (this.content != null) {
198
                this.content.close();
199
            }
200
            this.content = new PDPageContentStream(this.doc, page);
201
            this.y = drawHeader(false, true);
202
            this.y -= LINE_HEIGHT;
203
            this.y -= LINE_HEIGHT;
204
        }
205
 
206
        this.content.setFont(PDType1Font.HELVETICA, 7);
207
        if (code != null) {
208
            this.content.beginText();
209
            this.content.newLineAtOffset(COL_1_X, this.y);
210
            this.content.showText(StringUtils.cleanPDFString(code));
211
            this.content.endText();
212
        }
213
 
214
        drawRightAlign(this.content, COL_3_X, this.y, COL_3_SIZE, this.decimalFormatQuantity.format(qte));
215
        drawRightAlign(this.content, COL_4_X, this.y, COL_4_SIZE, this.decimalFormat.format(prixUnitaire));
216
        drawRightAlign(this.content, COL_5_X, this.y, COL_5_SIZE, this.decimalFormat.format(total));
217
 
218
        for (String part : parts) {
219
            this.content.beginText();
220
            this.content.newLineAtOffset(COL_2_X, this.y);
221
            this.content.showText(StringUtils.cleanPDFString(part));
222
            this.content.endText();
223
            this.y -= LINE_HEIGHT;
224
        }
225
        this.y += LINE_HEIGHT;
226
 
227
        this.content.setLineWidth(0.5f);
228
        this.content.setStrokingColor(Color.LIGHT_GRAY);
229
        this.content.moveTo(COL_1_X - 4, this.y - 2f);
230
        this.content.lineTo(COL_5_X + 4 + COL_5_SIZE, this.y - 2f);
231
        this.content.stroke();
232
 
233
    }
234
 
235
    public void addStockDepot(SQLRow rowEtatStock) throws IOException {
236
 
237
        this.etatsStock.add(rowEtatStock);
238
 
239
        final DBRoot root = rowEtatStock.getTable().getDBRoot();
240
        // Nouvelle page si besoin
241
        if (this.etatsStock.size() > 1) {
242
            final PDPage page = new PDPage(PDRectangle.A4);
243
            this.doc.addPage(page);
244
            if (this.content != null) {
245
                this.content.close();
246
            }
247
            this.content = new PDPageContentStream(this.doc, page);
248
            this.y = drawHeader(false, true);
249
        }
250
        final SQLTable tDepot = root.getTable("DEPOT_STOCK");
251
        final SQLTable tFamilleArtice = root.getTable("FAMILLE_ARTICLE");
252
        final SQLRow rDepot = tDepot.getRow(rowEtatStock.getInt("ID_DEPOT_STOCK"));
253
 
254
        this.y -= LINE_HEIGHT * 2;
255
 
256
        // Nom du déport
257
        this.content.setFont(PDType1Font.HELVETICA, 10);
258
        this.content.beginText();
259
        this.content.newLineAtOffset(COL_1_X, this.y);
260
        this.content.showText(StringUtils.cleanPDFString("DÉPÔT : " + rDepot.getString("NOM")));
261
        this.content.endText();
262
        this.y -= LINE_HEIGHT;
263
        // Depot Element
264
        final SQLTable tEtatStockElement = root.getTable("ETAT_STOCK_ELEMENT");
265
        final SQLRowValues rElements = new SQLRowValues(tEtatStockElement);
266
        final SQLRowValues putRowValues = rElements.putRowValues("ID_ARTICLE");
267
        putRowValues.putNulls("ID_FAMILLE_ARTICLE");
268
        for (SQLField field : putRowValues.getTable().getContentFields()) {
269
            if (field.getName().startsWith("ID_ARTICLE_DECLINAISON_")) {
270
                putRowValues.put(field.getName(), null);
271
            }
272
        }
273
        rElements.putNulls("PA", "T_PA", "QTE", "CODE", "NOM");
274
        final SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(rElements);
275
        final List<SQLRowValues> elements = fetcher
276
                .fetch(new Where(tEtatStockElement.getField("ID_ETAT_STOCK"), "=", rowEtatStock.getID()).and(new Where(tEtatStockElement.getField("QTE"), ">", BigDecimal.ZERO)));
277
 
278
        Collections.sort(elements, new Comparator<SQLRowValues>() {
279
 
280
            @Override
281
            public int compare(SQLRowValues o1, SQLRowValues o2) {
282
                final BigDecimal qte1 = o1.getBigDecimal("PA");
283
                final BigDecimal qte2 = o2.getBigDecimal("PA");
284
                if (qte1.signum() == 0 && qte2.signum() == 0 || qte1.signum() != 0 && qte2.signum() != 0) {
285
                    return o1.getString("NOM").compareTo(o2.getString("NOM"));
286
                } else if (qte1.signum() == 0) {
287
                    return 1;
288
                } else {
289
                    return -1;
290
                }
291
            }
292
        });
293
        final Map<Number, List<SQLRowValues>> mapFamilleElement = new HashMap<>();
294
 
295
        for (final SQLRowValues e : elements) {
296
            final Number idFamille = e.getForeign("ID_ARTICLE").getForeignIDNumber("ID_FAMILLE_ARTICLE");
297
            List<SQLRowValues> l = mapFamilleElement.get(idFamille);
298
            if (l == null) {
299
                l = new ArrayList<>();
300
                mapFamilleElement.put(idFamille, l);
301
            }
302
            l.add(e);
303
        }
304
 
305
        // On recupere tous les familles
306
        final SQLRowValues rFamille = new SQLRowValues(root.getTable("FAMILLE_ARTICLE"));
307
        rFamille.putNulls("NOM", "ID_FAMILLE_ARTICLE_PERE");
308
        List<SQLRowValues> familles = SQLRowValuesListFetcher.create(rFamille).fetch();
309
        System.out.println("StockReportPDF.addStockDepot()" + familles.size() + " familles");
310
        SQLRowValuesTree tree = new SQLRowValuesTree(familles, "ID_FAMILLE_ARTICLE_PERE");
311
        tree.dump(System.out);
312
        // On récupère les declinaisons
313
        Map<String, Map<Integer, Tuple2<String, BigDecimal>>> mapDeclinaison = new HashMap<>();
314
        fillDeclinaisons(tDepot.getDBRoot(), mapDeclinaison);
315
 
316
        //// On groupe par Famille
317
 
318
        // D'abord les articles sans familles
319
        BigDecimal t = BigDecimal.ZERO;
320
        for (final SQLRowValues e : elements) {
321
            final Number idFamille = e.getForeign("ID_ARTICLE").getForeignIDNumber("ID_FAMILLE_ARTICLE");
322
            if (idFamille.intValue() == tFamilleArtice.getUndefinedID()) {
323
                drawEtatStockElement(e, mapDeclinaison);
324
                t = t.add(e.getBigDecimal("T_PA"));
325
 
326
            }
327
        }
328
        this.totaux.put(rowEtatStock, t);
329
 
330
        // Les familles selons l'arbre
331
        List<SQLRowValuesTreeNode> roots = tree.getChildren();
332
        final Comparator<SQLRowValuesTreeNode> comparatorFamille = new Comparator<SQLRowValuesTreeNode>() {
333
 
334
            @Override
335
            public int compare(SQLRowValuesTreeNode o1, SQLRowValuesTreeNode o2) {
336
                return o1.getSQLRowValues().getString("NOM").compareToIgnoreCase(o2.getSQLRowValues().getString("NOM"));
337
            }
338
        };
339
        Collections.sort(roots, comparatorFamille);
340
        for (SQLRowValuesTreeNode f : roots) {
341
            drawFamilleNode(mapFamilleElement, f, mapDeclinaison);
342
        }
343
 
344
    }
345
 
346
    private void drawFamilleNode(Map<Number, List<SQLRowValues>> mapFamilleElement, SQLRowValuesTreeNode f, Map<String, Map<Integer, Tuple2<String, BigDecimal>>> mapDeclinaison) throws IOException {
347
        if (f.getChildrenCount() > 0) {
348
            drawFamilleName(f.getSQLRowValues().getString("NOM").toUpperCase());
349
        }
350
        // EtatStockElement
351
        List<SQLRowValues> list = mapFamilleElement.get(f.getSQLRowValues().getIDNumber());
352
        if (list != null) {
353
            for (SQLRowValues e : list) {
354
                drawEtatStockElement(e, mapDeclinaison);
355
            }
356
        }
357
        // Sous familles
358
        for (SQLRowValuesTreeNode n : f.getChildren()) {
359
            drawFamilleNode(mapFamilleElement, n, mapDeclinaison);
360
        }
361
 
362
    }
363
 
364
    private void drawEtatStockElement(SQLRowValues e, Map<String, Map<Integer, Tuple2<String, BigDecimal>>> mapDeclinaison) throws IOException {
365
        String code = e.getString("CODE");
366
        String article = e.getString("NOM");
367
 
368
        StringBuilder detailsDeclinaison = new StringBuilder();
369
        final SQLRowAccessor nonEmptyForeignArticle = e.getNonEmptyForeign("ID_ARTICLE");
370
        if (nonEmptyForeignArticle != null) {
371
            for (String fieldDecl : mapDeclinaison.keySet()) {
372
                if (nonEmptyForeignArticle.getObject(fieldDecl) != null) {
373
                    final Number nonEmptyForeignDecl = nonEmptyForeignArticle.getNonEmptyForeignIDNumber(fieldDecl);
374
                    if (nonEmptyForeignDecl != null) {
375
                        final String declinaisonName = mapDeclinaison.get(fieldDecl).get(nonEmptyForeignDecl).get0();
376
                        if (declinaisonName.trim().length() > 0) {
377
                            detailsDeclinaison.append(fieldDecl.substring("ID_ARTICLE_DECLINAISON_".length()));
378
                            detailsDeclinaison.append(" : ");
379
                            detailsDeclinaison.append(declinaisonName);
380
                            detailsDeclinaison.append("     ");
381
                        }
382
                    }
383
                }
384
            }
385
        }
386
        String detailsDeclString = detailsDeclinaison.toString().trim();
387
        if (detailsDeclString.length() > 0) {
388
            article += "\n" + detailsDeclString;
389
        }
390
 
391
        BigDecimal qte = e.getBigDecimal("QTE");
392
        BigDecimal prixUnitaire = e.getBigDecimal("PA");
393
        BigDecimal total = e.getBigDecimal("T_PA");
394
        drawLine(code, article, qte, prixUnitaire, total);
395
    }
396
 
397
    public void savePDF(File f) throws IOException {
398
        final PDPage page = new PDPage(PDRectangle.A4);
399
        this.doc.addPage(page);
400
        if (this.content != null) {
401
            this.content.close();
402
        }
403
        this.content = new PDPageContentStream(this.doc, page);
404
        // Totaux
405
        drawHeader(false, false);
406
 
407
        this.y -= LINE_HEIGHT;
408
 
409
        this.content.setFont(PDType1Font.HELVETICA, 10);
410
 
411
        for (SQLRow r : this.etatsStock) {
412
            BigDecimal total = this.totaux.get(r);
413
            final SQLTable tDepot = r.getTable().getTable("DEPOT_STOCK");
414
 
415
            final SQLRow rDepot = tDepot.getRow(r.getInt("ID_DEPOT_STOCK"));
416
 
417
            this.y -= LINE_HEIGHT * 2;
418
 
419
            // Nom du déport
420
            this.content.setFont(PDType1Font.HELVETICA, 10);
421
            this.content.beginText();
422
            this.content.newLineAtOffset(COL_1_X, this.y);
423
            this.content.showText(StringUtils.cleanPDFString("TOTAL DÉPÔT : " + rDepot.getString("NOM") + " : " + this.decimalFormat.format(total) + " EUR"));
424
            this.content.endText();
425
 
426
        }
427
 
428
        this.content.close();
429
        // Header avec numéro de page
430
        final InputStream sImage = GrandLivrePDF.class.getResourceAsStream("OpenConcerto_2000px.png");
431
        final ByteArrayOutputStream bOut = new ByteArrayOutputStream();
432
        final byte[] buf = new byte[8192];
433
        int length;
434
        while ((length = sImage.read(buf)) > 0) {
435
            bOut.write(buf, 0, length);
436
        }
437
        sImage.close();
438
        bOut.close();
439
 
440
        final float ratio = 20;
441
        final int pageCount = this.doc.getNumberOfPages();
442
        for (int index = 0; index < pageCount; index++) {
443
            final PDPage currentPage = this.doc.getPage(index);
444
            this.content = new PDPageContentStream(this.doc, currentPage, PDPageContentStream.AppendMode.APPEND, true, true);
445
            if (index == 0 || index == pageCount - 1) {
446
                // Logo sur 1ere page
447
                final PDImageXObject pdImage = PDImageXObject.createFromByteArray(this.doc, bOut.toByteArray(), "openconcerto.png");
448
                final float h = pdImage.getHeight() / ratio;
449
                this.content.drawImage(pdImage, 40, 10, pdImage.getWidth() / ratio, h);
450
            }
451
 
452
            this.content.beginText();
453
            this.content.setFont(PDType1Font.HELVETICA, 10);
454
            this.content.newLineAtOffset(500, 20);
455
            this.content.showText("Page " + (index + 1) + " / " + pageCount);
456
            this.content.endText();
457
            this.content.close();
458
        }
459
 
460
        this.doc.save(f);
461
    }
462
 
463
}