OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 21 | Rev 28 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
18 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.common.element;
15
 
16
import org.openconcerto.erp.config.ComptaPropsConfiguration;
17
import org.openconcerto.erp.core.common.image.ImageIconWarning;
18
import org.openconcerto.erp.core.customerrelationship.customer.element.CourrierClientSQLElement;
19
import org.openconcerto.erp.core.customerrelationship.customer.element.RelanceSQLElement;
20
import org.openconcerto.erp.core.humanresources.payroll.element.SalarieSQLElement;
21
import org.openconcerto.erp.core.sales.credit.element.AvoirClientSQLElement;
22
import org.openconcerto.erp.core.sales.invoice.element.SaisieVenteFactureSQLElement;
23
import org.openconcerto.erp.core.sales.order.element.CommandeClientSQLElement;
24
import org.openconcerto.erp.core.sales.quote.element.DevisSQLElement;
25
import org.openconcerto.erp.core.sales.shipment.element.BonDeLivraisonSQLElement;
26
import org.openconcerto.erp.core.supplychain.order.element.CommandeSQLElement;
27
import org.openconcerto.erp.core.supplychain.receipt.element.BonReceptionSQLElement;
28
import org.openconcerto.sql.Configuration;
29
import org.openconcerto.sql.element.BaseSQLComponent;
30
import org.openconcerto.sql.element.SQLComponent;
31
import org.openconcerto.sql.element.SQLElement;
32
import org.openconcerto.sql.model.SQLRow;
33
import org.openconcerto.sql.model.SQLRowListRSH;
34
import org.openconcerto.sql.model.SQLRowValues;
35
import org.openconcerto.sql.model.SQLSelect;
36
import org.openconcerto.sql.model.SQLTable;
37
import org.openconcerto.sql.model.SQLTableEvent;
19 ilm 38
import org.openconcerto.sql.model.SQLTableEvent.Mode;
18 ilm 39
import org.openconcerto.sql.model.SQLTableModifiedListener;
40
import org.openconcerto.sql.model.Where;
41
import org.openconcerto.ui.DefaultGridBagConstraints;
25 ilm 42
import org.openconcerto.utils.StringUtils;
21 ilm 43
import org.openconcerto.utils.Tuple2;
25 ilm 44
import org.openconcerto.utils.text.SimpleDocumentListener;
18 ilm 45
 
46
import java.awt.GridBagConstraints;
47
import java.awt.GridBagLayout;
48
import java.sql.SQLException;
49
import java.text.DateFormat;
50
import java.text.DecimalFormat;
21 ilm 51
import java.text.ParseException;
18 ilm 52
import java.text.SimpleDateFormat;
53
import java.util.ArrayList;
21 ilm 54
import java.util.Arrays;
25 ilm 55
import java.util.Collections;
56
import java.util.Comparator;
18 ilm 57
import java.util.Date;
58
import java.util.HashMap;
59
import java.util.List;
60
import java.util.Map;
25 ilm 61
import java.util.Set;
18 ilm 62
 
63
import javax.swing.Icon;
64
import javax.swing.JLabel;
65
import javax.swing.JTextField;
66
import javax.swing.event.DocumentEvent;
67
import javax.swing.event.DocumentListener;
68
 
69
// FIXME bug JTextField for input bigInt
70
 
71
public class NumerotationAutoSQLElement extends ComptaSQLConfElement {
72
 
73
    private static final String FORMAT = "_FORMAT";
74
    private static final String START = "_START";
75
 
76
    public NumerotationAutoSQLElement() {
77
        super("NUMEROTATION_AUTO", "une numérotation automatique", "numérotations automatiques");
78
    }
79
 
80
    protected List<String> getListFields() {
81
        final List<String> list = new ArrayList<String>(2);
82
        list.add("DEVIS_FORMAT");
83
        list.add("DEVIS_START");
84
        return list;
85
    }
86
 
87
    protected List<String> getComboFields() {
88
        final List<String> list = new ArrayList<String>(2);
89
        list.add("DEVIS_FORMAT");
90
        list.add("DEVIS_START");
91
        return list;
92
    }
93
 
94
    /*
95
     * (non-Javadoc)
96
     *
97
     * @see org.openconcerto.devis.SQLElement#getComponent()
98
     */
99
    public SQLComponent createComponent() {
100
        return new BaseSQLComponent(this) {
101
 
102
            private Icon iconWarning = ImageIconWarning.getInstance();
103
 
104
            public void addViews() {
105
                this.setLayout(new GridBagLayout());
106
                final GridBagConstraints c = new DefaultGridBagConstraints();
107
 
25 ilm 108
                Set<Class<? extends SQLElement>> s = map.keySet();
18 ilm 109
 
25 ilm 110
                final ArrayList<Class<? extends SQLElement>> list = new ArrayList<Class<? extends SQLElement>>(s);
111
                Collections.sort(list, new Comparator<Class<? extends SQLElement>>() {
112
                    public int compare(Class<? extends SQLElement> o1, Class<? extends SQLElement> o2) {
113
                        return o1.toString().compareTo(o2.toString());
114
                    };
115
                });
18 ilm 116
 
25 ilm 117
                for (Class<? extends SQLElement> class1 : list) {
118
                    c.gridy++;
119
                    c.gridx = 0;
120
                    c.weightx = 0;
121
                    String prefix = map.get(class1);
122
                    SQLElement elt = Configuration.getInstance().getDirectory().getElement(class1);
123
                    // Avoir
124
                    JLabel labelAvoirFormat = new JLabel(StringUtils.firstUp(elt.getPluralName()) + " " + getLabelFor(prefix + FORMAT));
125
                    this.add(labelAvoirFormat, c);
126
                    c.gridx++;
127
                    c.weightx = 1;
128
                    final JTextField fieldFormat = new JTextField();
129
                    this.add(fieldFormat, c);
18 ilm 130
 
25 ilm 131
                    final JLabel labelAvoirStart = new JLabel(getLabelFor(prefix + START));
132
                    c.gridx++;
133
                    c.weightx = 0;
134
                    this.add(labelAvoirStart, c);
135
                    c.gridx++;
136
                    c.weightx = 1;
137
                    final JTextField fieldStart = new JTextField();
138
                    this.add(fieldStart, c);
18 ilm 139
 
25 ilm 140
                    final JLabel labelResult = new JLabel();
141
                    c.gridx++;
142
                    c.weightx = 0;
143
                    this.add(labelResult, c);
18 ilm 144
 
25 ilm 145
                    // Affichage dynamique du résultat
146
                    SimpleDocumentListener listener = new SimpleDocumentListener() {
18 ilm 147
 
25 ilm 148
                        @Override
149
                        public void update(DocumentEvent e) {
150
                            updateLabel(fieldStart, fieldFormat, labelResult);
18 ilm 151
 
25 ilm 152
                        }
153
                    };
18 ilm 154
 
25 ilm 155
                    fieldFormat.getDocument().addDocumentListener(listener);
156
                    fieldStart.getDocument().addDocumentListener(listener);
18 ilm 157
 
25 ilm 158
                    this.addRequiredSQLObject(fieldFormat, prefix + FORMAT);
159
                    this.addRequiredSQLObject(fieldStart, prefix + START);
18 ilm 160
 
25 ilm 161
                }
18 ilm 162
 
163
                // JLabel labelCodeLettrage = new JLabel(getLabelFor("CODE_LETTRAGE"));
164
                // c.gridy++;
165
                // c.gridx = 0;
166
                // c.weightx = 0;
167
                // this.add(labelCodeLettrage, c);
168
                // c.gridx++;
169
                // c.weightx = 1;
170
                // this.add(this.textCodeLettrage, c);
171
                //
172
                // c.gridx++;
173
                // c.weightx = 0;
174
                // labelNextCodeLettrage = new JLabel();
175
                // this.add(labelNextCodeLettrage, c);
176
 
177
                JLabel labelExemple = new JLabel("Exemple de format : 'Fact'yyyy0000");
178
                c.gridy++;
179
                c.gridx = 0;
180
                c.gridwidth = GridBagConstraints.REMAINDER;
181
                c.weighty = 1;
182
                c.anchor = GridBagConstraints.NORTHWEST;
183
                this.add(labelExemple, c);
184
 
185
                // this.textCodeLettrage.getDocument().addDocumentListener(this.listenText);
186
 
187
            }
188
 
189
            // private void updateLabelNextCode() {
190
            // String s = getNextCodeLetrrage(this.textCodeLettrage.getText());
191
            // this.labelNextCodeLettrage.setText(donne + " " + s);
192
            // }
193
 
194
            private void updateLabel(JTextField textStart, JTextField textFormat, JLabel label) {
195
                if (textStart.getText().trim().length() > 0) {
196
                    String numProposition = getNextNumero(textFormat.getText(), Integer.parseInt(textStart.getText()));
197
 
198
                    if (numProposition != null) {
199
                        label.setText(" --> " + numProposition);
200
                        label.setIcon(null);
201
                    } else {
202
                        label.setIcon(this.iconWarning);
203
                        label.setText("");
204
                    }
205
                } else {
206
                    label.setIcon(this.iconWarning);
207
                    label.setText("");
208
                }
209
            }
210
 
211
        };
212
    }
213
 
214
    // Format du type 'Fact'yyyy-MM-dd0000
215
    public static final String getNextNumero(Class<? extends SQLElement> clazz) {
216
        SQLRow rowNum = TABLE_NUM.getRow(2);
217
        String s = map.get(clazz);
218
        String format = rowNum.getString(s + FORMAT);
219
        int start = rowNum.getInt(s + START);
220
        return getNextNumero(format, start);
221
    }
222
 
21 ilm 223
    private static final Tuple2<String, String> getPrefixAndSuffix(String format, Date d) {
224
 
225
        String prefix = "";
226
        String suffix = "";
18 ilm 227
        int c = format.indexOf('0');
228
        if (format.trim().length() > 0) {
229
            if (c >= 0) {
21 ilm 230
                prefix = format.substring(0, c);
231
                suffix = format.substring(c, format.length());
18 ilm 232
 
233
                try {
234
                    DateFormat dateFormat = new SimpleDateFormat(prefix);
21 ilm 235
                    prefix = dateFormat.format(d);
18 ilm 236
                } catch (IllegalArgumentException e) {
237
                    System.err.println("pattern incorrect");
238
                }
239
 
240
            } else {
241
 
242
                try {
243
                    DateFormat dateFormat = new SimpleDateFormat(format);
21 ilm 244
                    prefix = dateFormat.format(d);
18 ilm 245
                } catch (IllegalArgumentException e) {
246
                    System.err.println("pattern incorrect");
247
                }
21 ilm 248
 
18 ilm 249
            }
250
        }
21 ilm 251
 
252
        return Tuple2.create(prefix, suffix);
18 ilm 253
    }
254
 
21 ilm 255
    protected static final String getNextNumero(String format, Integer start) {
256
        if (start != null && start < 0) {
257
            return null;
258
        }
259
 
260
        Tuple2<String, String> t = getPrefixAndSuffix(format, new Date());
261
        DecimalFormat decimalFormat = new DecimalFormat(t.get1());
262
        return t.get0() + decimalFormat.format(start);
263
    }
264
 
18 ilm 265
    public static final String getNextCodeLettrage() {
266
        SQLRow rowNum = TABLE_NUM.getRow(2);
267
        final String string = rowNum.getString("CODE_LETTRAGE");
268
        String s = (string == null) ? "" : string.trim().toUpperCase();
269
        return getNextCodeLetrrage(s);
270
    }
271
 
272
    public static final String getNextCodeLetrrage(String code) {
273
        code = code.trim();
274
        if (code == null || code.length() == 0) {
275
            return "AAA";
276
        } else {
277
            char[] charArray = code.toCharArray();
278
            char c = 'A';
279
            int i = charArray.length - 1;
280
            while (i >= 0 && (c = charArray[i]) == 'Z') {
281
                i--;
282
            }
283
            if (i >= 0) {
284
                c++;
285
                charArray[i] = c;
286
                for (int j = i + 1; j < charArray.length; j++) {
287
                    charArray[j] = 'A';
288
                }
289
                code = String.valueOf(charArray);
290
            } else {
291
                // On ajoute une lettre
292
                final StringBuffer buf = new StringBuffer(code.length() + 1);
293
                final int nb = code.length() + 1;
294
                for (int j = 0; j < nb; j++) {
295
                    buf.append('A');
296
                }
297
                code = buf.toString();
298
            }
299
 
300
            return code;
301
        }
302
 
303
    }
304
 
305
    private static boolean isNumeroExist(SQLElement elt, int num) {
306
        if (num < 0) {
307
            return true;
308
        }
309
        SQLSelect sel = new SQLSelect(elt.getTable().getBase());
310
        sel.addSelect(elt.getTable().getKey());
311
 
312
        sel.setWhere(new Where(elt.getTable().getField("NUMERO"), "LIKE", getPattern(elt, num)));
313
        System.err.println("NumerotationAutoSQLElement.isNumeroExist() " + sel.asString());
314
        List<SQLRow> liste = (List<SQLRow>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new SQLRowListRSH(elt.getTable(), true));
315
        return liste.size() > 0;
316
    }
317
 
21 ilm 318
    protected static final SQLTable TABLE_NUM = ((ComptaPropsConfiguration) Configuration.getInstance()).getSQLBaseSociete().getTable("NUMEROTATION_AUTO");
319
 
320
    public static void main(String[] args) {
321
 
322
        List<String> l = Arrays.asList("2011/05/001", "2011/05/002", "2011/05/003", "2011/05/004");
323
        DecimalFormat format = new DecimalFormat("'2011/05/'000");
324
        for (String string : l) {
325
            Number n;
326
            try {
327
                n = format.parse(string);
328
                System.err.println(n);
329
            } catch (ParseException exn) {
330
                // TODO Bloc catch auto-généré
331
                exn.printStackTrace();
332
            }
333
        }
334
 
335
    }
336
 
337
    public static String getNextForMonth(Class<? extends SQLElement> clazz, SQLTable table, Date d) {
338
 
339
        SQLRow rowNum = TABLE_NUM.getRow(2);
340
        String s = map.get(clazz);
341
        String pattern = rowNum.getString(s + FORMAT);
342
        Tuple2<String, String> t = getPrefixAndSuffix(pattern, d);
343
        SQLSelect sel = new SQLSelect(table.getBase());
344
        sel.addSelect(table.getField("NUMERO"));
345
        sel.addSelect(table.getKey());
346
        sel.setWhere(new Where(table.getField("NUMERO"), "LIKE", "%" + t.get0() + "%"));
347
        List<SQLRow> l = (List<SQLRow>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), SQLRowListRSH.createFromSelect(sel));
348
 
349
        DecimalFormat format = new DecimalFormat("'" + t.get0() + "'" + t.get1());
350
        int value = 0;
351
        for (SQLRow sqlRow : l) {
352
            Number n;
353
            try {
354
                n = format.parse(sqlRow.getString("NUMERO"));
355
                value = Math.max(value, n.intValue());
356
            } catch (ParseException exn) {
357
                // TODO Bloc catch auto-généré
358
                exn.printStackTrace();
359
            }
360
        }
361
        final String format2 = format.format(value + 1);
362
        System.err.println(format2);
363
        return format2;
364
    }
365
 
18 ilm 366
    private static String getPattern(SQLElement elt, int num) {
367
        SQLRow rowNum = TABLE_NUM.getRow(2);
368
        String s = map.get(elt.getClass());
369
        String format = rowNum.getString(s + FORMAT);
370
 
371
        format = format.replaceAll("y|d|M", "_");
372
        format = format.replaceAll("'", "");
373
        int c = format.indexOf('0');
374
 
375
        String numero = "";
376
        if (format.trim().length() > 0) {
377
            if (c >= 0) {
378
                String prefix = format.substring(0, c);
379
                String suffix = format.substring(c, format.length());
380
                String d = prefix;
381
 
382
                DecimalFormat numberFormat = new DecimalFormat(suffix);
383
                String n = numberFormat.format(num);
384
 
385
                numero = d + n;
386
            } else {
387
 
388
                String d = format;
389
 
390
                numero = d + String.valueOf(num);
391
            }
392
        } else {
393
            numero = String.valueOf(num);
394
        }
395
        return numero;
396
    }
397
 
398
    /**
399
     * Vérifie et corrige la numérotation
400
     *
401
     * @param elt
402
     */
403
    public static void fixNumerotation(SQLElement elt) {
404
 
405
        SQLRow rowNum = TABLE_NUM.getRow(2);
406
        String s = map.get(elt.getClass());
407
        int start = rowNum.getInt(s + START);
408
 
409
        // si le numero precedent n'existe pas
410
        if (!isNumeroExist(elt, start - 1)) {
411
 
412
            int i = 2;
413
 
414
            while (!isNumeroExist(elt, start - i)) {
415
                i++;
416
            }
417
 
418
            if (start - i >= 0) {
419
                SQLRowValues rowVals = rowNum.createEmptyUpdateRow();
420
                rowVals.put(s + START, start - i + 1);
421
                try {
422
                    rowVals.update();
423
                } catch (SQLException e) {
424
                    e.printStackTrace();
425
                }
426
            }
427
        }
428
    }
429
 
430
    private static Map<Class<? extends SQLElement>, String> map = new HashMap<Class<? extends SQLElement>, String>();
431
 
432
    static {
433
        map.put(AvoirClientSQLElement.class, "AVOIR");
434
        map.put(SaisieVenteFactureSQLElement.class, "FACT");
435
        map.put(AvoirClientSQLElement.class, "AVOIR");
436
        map.put(DevisSQLElement.class, "DEVIS");
437
        map.put(BonDeLivraisonSQLElement.class, "BON_L");
438
        map.put(BonReceptionSQLElement.class, "BON_R");
439
        map.put(CommandeClientSQLElement.class, "COMMANDE_CLIENT");
440
        map.put(CommandeSQLElement.class, "COMMANDE");
441
        map.put(CourrierClientSQLElement.class, "COURRIER");
442
        map.put(RelanceSQLElement.class, "RELANCE");
443
        map.put(SalarieSQLElement.class, "SALARIE");
444
 
445
    }
446
 
25 ilm 447
    /**
448
     * Permet d'ajouter la gestion de la numérotation automatique. Attention à bien créer les champs
449
     * name_FORMAT et name_START dans la table NUMEROTATION_AUTO
450
     *
451
     * @param elt
452
     * @param name
453
     */
454
    public static void addClass(Class<? extends SQLElement> elt, String name) {
455
        map.put(elt, name);
456
    }
457
 
18 ilm 458
    public static void addListeners() {
459
        for (Class<? extends SQLElement> clazz : map.keySet()) {
460
            final SQLElement elt = Configuration.getInstance().getDirectory().getElement(clazz);
461
            if (elt != null) {
462
                elt.getTable().addTableModifiedListener(new SQLTableModifiedListener() {
463
                    @Override
464
                    public void tableModified(SQLTableEvent evt) {
465
                        if (evt.getMode() == Mode.ROW_UPDATED) {
466
                            SQLRow row = evt.getRow();
467
                            if (row.isArchived()) {
468
                                fixNumerotation(elt);
469
                            }
470
                        }
471
 
472
                    }
473
                });
474
            } else {
475
                System.err.println(clazz);
476
                Thread.dumpStack();
477
            }
478
 
479
        }
480
 
481
    }
482
 
483
}