OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 118 | Go to most recent revision | Show entire file | Regard whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 118 Rev 153
Line 1... Line 1...
1
package org.openconcerto.modules.badge;
1
package org.openconcerto.modules.badge;
2
 
2
 
3
import java.awt.Component;
3
import java.awt.Component;
4
import java.io.File;
4
import java.io.File;
5
import java.io.IOException;
5
import java.io.IOException;
-
 
6
import java.sql.SQLException;
6
import java.text.DateFormat;
7
import java.text.DateFormat;
-
 
8
import java.text.Normalizer;
-
 
9
import java.text.Normalizer.Form;
7
import java.text.SimpleDateFormat;
10
import java.text.SimpleDateFormat;
8
import java.util.ArrayList;
11
import java.util.ArrayList;
9
import java.util.Arrays;
12
import java.util.Arrays;
10
import java.util.Calendar;
13
import java.util.Calendar;
11
import java.util.Date;
14
import java.util.Date;
-
 
15
import java.util.HashMap;
12
import java.util.HashSet;
16
import java.util.HashSet;
13
import java.util.List;
17
import java.util.List;
-
 
18
import java.util.Map;
14
import java.util.Set;
19
import java.util.Set;
-
 
20
import java.util.regex.Matcher;
-
 
21
import java.util.regex.Pattern;
15
 
22
 
16
import javax.swing.Action;
23
import javax.swing.Action;
17
import javax.swing.Icon;
24
import javax.swing.Icon;
18
import javax.swing.JFrame;
25
import javax.swing.JFrame;
19
import javax.swing.JTable;
26
import javax.swing.JTable;
Line 21... Line 28...
21
 
28
 
22
import org.openconcerto.erp.action.CreateFrameAbstractAction;
29
import org.openconcerto.erp.action.CreateFrameAbstractAction;
23
import org.openconcerto.erp.action.PreferencesAction;
30
import org.openconcerto.erp.action.PreferencesAction;
24
import org.openconcerto.erp.config.Gestion;
31
import org.openconcerto.erp.config.Gestion;
25
import org.openconcerto.erp.config.MainFrame;
32
import org.openconcerto.erp.config.MainFrame;
-
 
33
import org.openconcerto.erp.core.common.element.AdresseSQLElement;
26
import org.openconcerto.erp.core.common.element.ComptaSQLConfElement;
34
import org.openconcerto.erp.core.common.element.ComptaSQLConfElement;
27
import org.openconcerto.erp.core.common.ui.ListeViewPanel;
35
import org.openconcerto.erp.core.common.ui.ListeViewPanel;
-
 
36
import org.openconcerto.erp.core.customerrelationship.customer.element.ContactSQLElement;
-
 
37
import org.openconcerto.erp.core.customerrelationship.customer.element.CustomerSQLElement;
28
import org.openconcerto.erp.modules.AbstractModule;
38
import org.openconcerto.erp.modules.AbstractModule;
29
import org.openconcerto.erp.modules.ComponentsContext;
39
import org.openconcerto.erp.modules.ComponentsContext;
30
import org.openconcerto.erp.modules.DBContext;
40
import org.openconcerto.erp.modules.DBContext;
31
import org.openconcerto.erp.modules.MenuContext;
41
import org.openconcerto.erp.modules.MenuContext;
32
import org.openconcerto.erp.modules.ModuleFactory;
42
import org.openconcerto.erp.modules.ModuleFactory;
33
import org.openconcerto.erp.modules.ModuleManager;
43
import org.openconcerto.erp.modules.ModuleManager;
34
import org.openconcerto.erp.modules.ModulePackager;
44
import org.openconcerto.erp.modules.ModulePackager;
35
import org.openconcerto.erp.modules.ModulePreferencePanel;
45
import org.openconcerto.erp.modules.ModulePreferencePanel;
36
import org.openconcerto.erp.modules.ModulePreferencePanelDesc;
46
import org.openconcerto.erp.modules.ModulePreferencePanelDesc;
-
 
47
import org.openconcerto.erp.modules.ModuleVersion;
37
import org.openconcerto.erp.modules.RuntimeModuleFactory;
48
import org.openconcerto.erp.modules.RuntimeModuleFactory;
38
import org.openconcerto.sql.Configuration;
49
import org.openconcerto.sql.Configuration;
39
import org.openconcerto.sql.element.SQLComponent;
50
import org.openconcerto.sql.element.SQLComponent;
40
import org.openconcerto.sql.element.SQLElement;
51
import org.openconcerto.sql.element.SQLElement;
41
import org.openconcerto.sql.element.SQLElementDirectory;
52
import org.openconcerto.sql.element.SQLElementDirectory;
42
import org.openconcerto.sql.element.UISQLComponent;
53
import org.openconcerto.sql.element.UISQLComponent;
43
import org.openconcerto.sql.model.SQLName;
54
import org.openconcerto.sql.model.SQLName;
-
 
55
import org.openconcerto.sql.model.SQLRow;
44
import org.openconcerto.sql.model.SQLRowAccessor;
56
import org.openconcerto.sql.model.SQLRowAccessor;
-
 
57
import org.openconcerto.sql.model.SQLRowValues;
-
 
58
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
45
import org.openconcerto.sql.model.SQLSyntax;
59
import org.openconcerto.sql.model.SQLSyntax;
-
 
60
import org.openconcerto.sql.model.SQLTable;
46
import org.openconcerto.sql.model.Where;
61
import org.openconcerto.sql.model.Where;
47
import org.openconcerto.sql.utils.SQLCreateTable;
62
import org.openconcerto.sql.utils.SQLCreateTable;
48
import org.openconcerto.sql.view.FileDropHandler;
63
import org.openconcerto.sql.view.FileDropHandler;
49
import org.openconcerto.sql.view.IListFrame;
64
import org.openconcerto.sql.view.IListFrame;
50
import org.openconcerto.sql.view.IListPanel;
65
import org.openconcerto.sql.view.IListPanel;
51
import org.openconcerto.sql.view.ListeAddPanel;
-
 
52
import org.openconcerto.sql.view.list.IListe;
66
import org.openconcerto.sql.view.list.IListe;
53
import org.openconcerto.sql.view.list.RowAction;
67
import org.openconcerto.sql.view.list.RowAction;
54
import org.openconcerto.sql.view.list.SQLTableModelSourceOnline;
68
import org.openconcerto.sql.view.list.SQLTableModelSourceOnline;
55
import org.openconcerto.ui.PanelFrame;
69
import org.openconcerto.ui.PanelFrame;
-
 
70
import org.openconcerto.ui.group.Group;
-
 
71
import org.openconcerto.ui.group.LayoutHints;
56
import org.openconcerto.utils.FileUtils;
72
import org.openconcerto.utils.FileUtils;
57
import org.openconcerto.utils.ListMap;
73
import org.openconcerto.utils.ListMap;
58
import org.openconcerto.utils.PrefType;
74
import org.openconcerto.utils.PrefType;
-
 
75
import org.openconcerto.utils.StringUtils;
59
import org.openconcerto.utils.cc.IClosure;
76
import org.openconcerto.utils.cc.IClosure;
60
 
77
 
61
public final class Module extends AbstractModule {
78
public final class Module extends AbstractModule {
62
 
79
 
-
 
80
    private static final String CLIENT_ADH_FIELDNAME = "ID_ADHERENT";
-
 
81
    private static final Pattern FIRST_NAME_PATTERN = Pattern.compile("([^0-9]*)([0-9]*).*");
-
 
82
    public static final boolean HAS_CODE_IN_FIRST_NAME = Boolean.getBoolean("adherent.hasCodeInFirstName");
-
 
83
 
63
    public Module(ModuleFactory f) throws IOException {
84
    public Module(ModuleFactory f) throws IOException {
64
        super(f);
85
        super(f);
65
 
86
 
66
    }
87
    }
67
 
88
 
68
    @Override
89
    @Override
69
    protected void install(DBContext ctxt) {
90
    protected void install(final DBContext ctxt) throws SQLException, IOException {
70
        super.install(ctxt);
91
        super.install(ctxt);
-
 
92
        final AdresseSQLElement addrElem = ctxt.getElementDirectory().getElement(AdresseSQLElement.class);
71
 
93
 
-
 
94
        final ModuleVersion dbVersion = ctxt.getLastInstalledVersion() == null ? ModuleVersion.MIN : ctxt.getLastInstalledVersion();
-
 
95
        if (dbVersion.getMerged() > this.getFactory().getVersion().getMerged())
-
 
96
            throw new IllegalStateException("Cannot downgrade from " + dbVersion + " to " + this.getFactory());
72
        if (!ctxt.getTablesPreviouslyCreated().contains("ENTREE")) {
97
        if (dbVersion.getMerged() < ModuleVersion.getMerged(1, 0)) {
73
            // ENTREE
98
            // ENTREE
74
            final SQLCreateTable createTableEntree = ctxt.getCreateTable("ENTREE");
99
            final SQLCreateTable createTableEntree = ctxt.getCreateTable("ENTREE");
75
            createTableEntree.addDateAndTimeColumn("DATE");
100
            createTableEntree.addDateAndTimeColumn("DATE");
76
            createTableEntree.addVarCharColumn("NUMERO_CARTE", 256);
101
            createTableEntree.addVarCharColumn("NUMERO_CARTE", 256);
77
            createTableEntree.addVarCharColumn("ADHERENT", 512);
102
            createTableEntree.addVarCharColumn("ADHERENT", 512);
78
            createTableEntree.addVarCharColumn("MOTIF", 2048);
103
            createTableEntree.addVarCharColumn("MOTIF", 2048);
79
            createTableEntree.addColumn("ACCEPTE", "boolean");
104
            createTableEntree.addColumn("ACCEPTE", "boolean");
80
        }
105
 
81
        if (!ctxt.getTablesPreviouslyCreated().contains("PLAGE_HORAIRE")) {
-
 
82
            // PLAGE
106
            // PLAGE
83
            final SQLCreateTable createTablePlage = ctxt.getCreateTable("PLAGE_HORAIRE");
107
            final SQLCreateTable createTablePlage = ctxt.getCreateTable("PLAGE_HORAIRE");
84
            createTablePlage.addVarCharColumn("NOM", 256);
108
            createTablePlage.addVarCharColumn("NOM", 256);
85
            createTablePlage.addColumn("DEBUT_1_LUNDI", "time");
109
            createTablePlage.addColumn("DEBUT_1_LUNDI", "time");
86
            createTablePlage.addColumn("DEBUT_1_MARDI", "time");
110
            createTablePlage.addColumn("DEBUT_1_MARDI", "time");
Line 127... Line 151...
127
            createTablePlage.addColumn("FIN_3_MERCREDI", "time");
151
            createTablePlage.addColumn("FIN_3_MERCREDI", "time");
128
            createTablePlage.addColumn("FIN_3_JEUDI", "time");
152
            createTablePlage.addColumn("FIN_3_JEUDI", "time");
129
            createTablePlage.addColumn("FIN_3_VENDREDI", "time");
153
            createTablePlage.addColumn("FIN_3_VENDREDI", "time");
130
            createTablePlage.addColumn("FIN_3_SAMEDI", "time");
154
            createTablePlage.addColumn("FIN_3_SAMEDI", "time");
131
            createTablePlage.addColumn("FIN_3_DIMANCHE", "time");
155
            createTablePlage.addColumn("FIN_3_DIMANCHE", "time");
132
        }
-
 
133
 
156
 
134
        if (!ctxt.getTablesPreviouslyCreated().contains("ADHERENT")) {
-
 
135
            // ADHERENT
157
            // ADHERENT
136
            final SQLCreateTable createTable = ctxt.getCreateTable("ADHERENT");
158
            final SQLCreateTable createTable = ctxt.getCreateTable("ADHERENT");
137
 
159
 
138
            createTable.addVarCharColumn("NUMERO_CARTE", 256);
160
            createTable.addVarCharColumn("NUMERO_CARTE", 256);
139
            createTable.addVarCharColumn("NOM", 256);
161
            createTable.addVarCharColumn("NOM", 256);
Line 145... Line 167...
145
            createTable.addVarCharColumn("PRENOM", 256);
167
            createTable.addVarCharColumn("PRENOM", 256);
146
            createTable.addVarCharColumn("INFOS", 2048);
168
            createTable.addVarCharColumn("INFOS", 2048);
147
            createTable.addColumn("DATE_VALIDITE_INSCRIPTION", "date");
169
            createTable.addColumn("DATE_VALIDITE_INSCRIPTION", "date");
148
            createTable.addColumn("DATE_NAISSANCE", "date");
170
            createTable.addColumn("DATE_NAISSANCE", "date");
149
 
171
 
150
            createTable.addForeignColumn("ID_ADRESSE", Configuration.getInstance().getRoot().findTable("ADRESSE"));
172
            createTable.addForeignColumn("ID_ADRESSE", addrElem.getTable());
151
            createTable.addForeignColumn("ID_PLAGE_HORAIRE", new SQLName("PLAGE_HORAIRE"), SQLSyntax.ID_NAME, null);
173
            createTable.addForeignColumn("ID_PLAGE_HORAIRE", new SQLName("PLAGE_HORAIRE"), SQLSyntax.ID_NAME, null);
152
        }
174
        }
-
 
175
        // at least v1.0
-
 
176
 
-
 
177
        if (dbVersion.getMerged() < ModuleVersion.getMerged(1, 1)) {
-
 
178
            // migrate from non-private
-
 
179
            final SQLTable adhT = ctxt.getRoot().getTable("ADHERENT");
-
 
180
            final CustomerSQLElement clientElem = ctxt.getElementDirectory().getElement(CustomerSQLElement.class);
-
 
181
            final SQLTable clientT = clientElem.getTable();
-
 
182
            ctxt.getAlterTable(clientT.getName()).addForeignColumn(CLIENT_ADH_FIELDNAME, adhT);
-
 
183
            // fill new field (no need to do anything in uninstall() since the field will be
-
 
184
            // dropped and new clients shouldn't be dropped)
-
 
185
            if (adhT.getRowCount(false) > 0) {
-
 
186
                ctxt.executeSQL();
-
 
187
                this.setupElements(ctxt.getElementDirectory());
-
 
188
                // 1. fetch all clients
-
 
189
                final ListMap<String, SQLRow> clientByName = new ListMap<>();
-
 
190
                final ListMap<String, SQLRow> clientByCode = new ListMap<>();
-
 
191
                for (final SQLRowValues clientR : SQLRowValuesListFetcher.create(new SQLRowValues(clientT).putNulls("NOM", "CODE")).fetch()) {
-
 
192
                    final String normalized = normalize(clientR.getString("NOM"));
-
 
193
                    clientByName.add(normalized, clientR.asRow());
-
 
194
                    clientByCode.add(clientR.getString("CODE"), clientR.asRow());
-
 
195
                }
-
 
196
                // 2. for each ADHERENT, link it to a single matching CLIENT, or create a
-
 
197
                // new one. Also fill CLIENT fields with duplicates from ADHERENT.
-
 
198
                // créer plutôt plus de clients : si besoin, fusion après coup.
-
 
199
                final SQLRowValues adhToFetch = new SQLRowValues(adhT).putNulls("NOM", "PRENOM", "MAIL", "TEL", "DATE_NAISSANCE");
-
 
200
                adhToFetch.putRowValues("ID_ADRESSE").setAllToNull();
-
 
201
                final Map<SQLRow, SQLRow> updatedClientsAndAdh = new HashMap<>();
-
 
202
 
-
 
203
                for (final SQLRowValues adhR : SQLRowValuesListFetcher.create(adhToFetch).fetch(null, true)) {
-
 
204
                    final String firstName;
-
 
205
                    final String code;
-
 
206
                    SQLRow existingClient = null;
-
 
207
                    if (HAS_CODE_IN_FIRST_NAME) {
-
 
208
                        final Matcher matcher = FIRST_NAME_PATTERN.matcher(adhR.getString("PRENOM"));
-
 
209
                        if (!matcher.matches())
-
 
210
                            throw new IllegalStateException("Couldn't match " + adhR);
-
 
211
                        firstName = matcher.group(1).trim();
-
 
212
                        code = matcher.group(2);
-
 
213
                    } else {
-
 
214
                        firstName = adhR.getString("PRENOM");
-
 
215
                        code = null;
-
 
216
                    }
-
 
217
                    if (!StringUtils.isEmpty(code)) {
-
 
218
                        existingClient = getUnique(clientByCode.get(code));
-
 
219
                    }
-
 
220
                    final String firstLastName = firstName + " " + adhR.getString("NOM");
-
 
221
                    if (existingClient == null) {
-
 
222
                        existingClient = getUnique(clientByName.get(normalize(firstLastName)));
-
 
223
                    }
-
 
224
                    final SQLRow newClient;
-
 
225
                    if (existingClient != null && !updatedClientsAndAdh.containsKey(existingClient)) {
-
 
226
                        updatedClientsAndAdh.put(existingClient, adhR.asRow());
-
 
227
                        // TODO use SQLElement.createFetcher()
-
 
228
                        final SQLRowValues clientToUpdateVals = SQLRowValuesListFetcher.create(clientElem.createGraph()).fetchOne(existingClient.getIDNumber(), true);
-
 
229
                        // TODO use new UpdateAction(SQLElement.createContext())
-
 
230
                        final SQLRowValues newVals = fillFields(clientToUpdateVals.deepCopy(), adhR, false, addrElem);
-
 
231
                        newClient = clientElem.update(clientToUpdateVals, newVals).exec();
-
 
232
                    } else {
-
 
233
                        // TODO use Logger
-
 
234
                        if (existingClient != null) {
-
 
235
                            System.err.println("While matching " + adhT + ", existing client " + existingClient + " already updated with " + updatedClientsAndAdh.get(existingClient)
-
 
236
                                    + " so creating new client for " + adhR);
-
 
237
                        } else {
-
 
238
                            System.err.println("While matching " + adhT + ", no client found for " + adhR);
-
 
239
                        }
-
 
240
                        // create new client row
-
 
241
                        // TODO use new InsertAction(SQLElement.createContext()) or at least
-
 
242
                        // SQLElement.insert()
-
 
243
                        final SQLRowValues vals = fillFields(new SQLRowValues(clientT).put("NOM", firstLastName), adhR, true, addrElem);
-
 
244
                        if (!StringUtils.isEmpty(code))
-
 
245
                            vals.put("CODE", code);
-
 
246
                        newClient = vals.insert();
-
 
247
                    }
-
 
248
                    final SQLRowValues contactVals = new SQLRowValues(ctxt.getElementDirectory().getElement(ContactSQLElement.class).getTable());
-
 
249
                    contactVals.putForeignID("ID_CLIENT", newClient);
-
 
250
                    contactVals.load(adhR.asRow(), Arrays.asList("NOM", "DATE_NAISSANCE"));
-
 
251
                    contactVals.put("PRENOM", firstName);
-
 
252
                    contactVals.put("EMAIL", adhR.getString("MAIL"));
-
 
253
                    contactVals.insert();
-
 
254
                }
-
 
255
 
-
 
256
                ctxt.getAlterTable(adhT.getName()).dropColumn("ID_ADRESSE");
-
 
257
            }
-
 
258
        }
-
 
259
    }
-
 
260
 
-
 
261
    protected SQLRow getUnique(final List<SQLRow> clientRows) {
-
 
262
        return clientRows != null && clientRows.size() == 1 ? clientRows.get(0) : null;
-
 
263
    }
-
 
264
 
-
 
265
    private final SQLRowValues fillFields(final SQLRowValues clientVals, final SQLRowValues adhVals, final boolean useAdhID, final SQLElement addrElem) {
-
 
266
        clientVals.put(CLIENT_ADH_FIELDNAME, useAdhID ? adhVals.getIDNumber() : adhVals.deepCopy());
-
 
267
        concatField(clientVals, adhVals, "MAIL");
-
 
268
        concatField(clientVals, adhVals, "TEL");
-
 
269
        final SQLRowValues adhAddr = (SQLRowValues) adhVals.getNonEmptyForeign("ID_ADRESSE");
-
 
270
        if (adhAddr != null && !StringUtils.isEmpty(adhAddr.getString("VILLE"), true)) {
-
 
271
            clientVals.put("ID_ADRESSE", addrElem.createCopy(adhAddr, true, null));
-
 
272
        }
-
 
273
        return clientVals;
-
 
274
    }
-
 
275
 
-
 
276
    private final void concatField(final SQLRowValues clientVals, final SQLRowValues adhVals, final String fieldName) {
-
 
277
        final String adhStr = adhVals.getString(fieldName);
-
 
278
        if (StringUtils.isEmpty(adhStr, true))
-
 
279
            return;
-
 
280
        final String clientStr = clientVals.getString(fieldName);
-
 
281
        if (StringUtils.isEmpty(clientStr, true)) {
-
 
282
            clientVals.put(fieldName, adhStr);
-
 
283
        } else {
-
 
284
            clientVals.put(fieldName, clientStr + ", " + adhStr);
-
 
285
        }
-
 
286
    }
-
 
287
 
-
 
288
    static private final Pattern diacriticalPattern = Pattern.compile("\\p{InCombiningDiacriticalMarks}+");
-
 
289
    static private final Pattern punctPattern = Pattern.compile("\\p{Punct}+");
-
 
290
    static private final Pattern spacePattern = Pattern.compile("\\p{Space}+");
-
 
291
 
-
 
292
    // remove punctation, multiple spaces and accents
-
 
293
    static private String normalize(final String s) {
-
 
294
        final String noMarks = diacriticalPattern.matcher(Normalizer.normalize(s, Form.NFD)).replaceAll("");
-
 
295
        final String noPunct = punctPattern.matcher(noMarks).replaceAll("_");
-
 
296
        return spacePattern.matcher(noPunct.trim()).replaceAll(" ").toLowerCase();
153
    }
297
    }
154
 
298
 
155
    @Override
299
    @Override
156
    protected void setupElements(SQLElementDirectory dir) {
300
    protected void setupElements(SQLElementDirectory dir) {
157
        super.setupElements(dir);
301
        super.setupElements(dir);
Line 246... Line 390...
246
        return panel;
390
        return panel;
247
    }
391
    }
248
 
392
 
249
    @Override
393
    @Override
250
    protected void setupComponents(ComponentsContext ctxt) {
394
    protected void setupComponents(ComponentsContext ctxt) {
-
 
395
        final Group g = new Group("customerrelationship.customer.adherent", LayoutHints.DEFAULT_SEPARATED_GROUP_HINTS);
-
 
396
        ctxt.getElement("CLIENT").getDefaultGroup().add(g);
-
 
397
        g.addItem(CLIENT_ADH_FIELDNAME, new LayoutHints(true, true, true, true, true, true));
251
        ctxt.addFileDropHandler("ADHERENT", new FileDropHandler() {
398
        ctxt.addFileDropHandler("ADHERENT", new FileDropHandler() {
252
 
399
 
253
            @Override
400
            @Override
254
            public boolean handle(File f, Component source) {
401
            public boolean handle(File f, Component source) {
255
                AdherentImporter importer;
402
                AdherentImporter importer;
Line 266... Line 413...
266
            public boolean canHandle(File f) {
413
            public boolean canHandle(File f) {
267
                String n = f.getName().toLowerCase();
414
                String n = f.getName().toLowerCase();
268
                return n.endsWith(".xls") || n.endsWith(".ods");
415
                return n.endsWith(".xls") || n.endsWith(".ods");
269
            }
416
            }
270
        });
417
        });
271
 
-
 
272
    }
418
    }
273
 
419
 
274
    @Override
420
    @Override
275
    protected void setupMenu(MenuContext ctxt) {
421
    protected void setupMenu(MenuContext ctxt) {
276
        ctxt.addMenuItem(new CreateFrameAbstractAction("Liste des adhérents") {
-
 
277
 
-
 
278
            @Override
-
 
279
            public JFrame createFrame() {
-
 
280
                IListFrame frameAdh = new IListFrame(new ListeAddPanel(Configuration.getInstance().getDirectory().getElement("ADHERENT")));
-
 
281
                return frameAdh;
-
 
282
            }
-
 
283
        }, MainFrame.LIST_MENU);
-
 
284
 
-
 
285
        ctxt.addMenuItem(new CreateFrameAbstractAction("Liste des entrées") {
422
        ctxt.addMenuItem(new CreateFrameAbstractAction("Liste des entrées") {
286
 
423
 
287
            @Override
424
            @Override
288
            public JFrame createFrame() {
425
            public JFrame createFrame() {
289
 
426