WebSVN - OpenConcerto - Diff - Rev 118 and 153 - /trunk/Modules/Module Badge/src/org/openconcerto/modules/badge/Module.java

 package org.openconcerto.modules.badge;
 import java.awt.Component;
 import java.io.File;
 import java.io.IOException;
+import java.sql.SQLException;
 import java.text.DateFormat;
+import java.text.Normalizer;
+import java.text.Normalizer.Form;
 import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Calendar;
 import java.util.Date;
+import java.util.HashMap;
 import java.util.HashSet;
 import java.util.List;
+import java.util.Map;
 import java.util.Set;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
 import javax.swing.Action;
 import javax.swing.Icon;
 import javax.swing.JFrame;
 import javax.swing.JTable;
 import org.openconcerto.erp.action.CreateFrameAbstractAction;
 import org.openconcerto.erp.action.PreferencesAction;
 import org.openconcerto.erp.config.Gestion;
 import org.openconcerto.erp.config.MainFrame;
+import org.openconcerto.erp.core.common.element.AdresseSQLElement;
 import org.openconcerto.erp.core.common.element.ComptaSQLConfElement;
 import org.openconcerto.erp.core.common.ui.ListeViewPanel;
+import org.openconcerto.erp.core.customerrelationship.customer.element.ContactSQLElement;
+import org.openconcerto.erp.core.customerrelationship.customer.element.CustomerSQLElement;
 import org.openconcerto.erp.modules.AbstractModule;
 import org.openconcerto.erp.modules.ComponentsContext;
 import org.openconcerto.erp.modules.DBContext;
 import org.openconcerto.erp.modules.MenuContext;
 import org.openconcerto.erp.modules.ModuleFactory;
 import org.openconcerto.erp.modules.ModuleManager;
 import org.openconcerto.erp.modules.ModulePackager;
 import org.openconcerto.erp.modules.ModulePreferencePanel;
 import org.openconcerto.erp.modules.ModulePreferencePanelDesc;
+import org.openconcerto.erp.modules.ModuleVersion;
 import org.openconcerto.erp.modules.RuntimeModuleFactory;
 import org.openconcerto.sql.Configuration;
 import org.openconcerto.sql.element.SQLComponent;
 import org.openconcerto.sql.element.SQLElement;
 import org.openconcerto.sql.element.SQLElementDirectory;
 import org.openconcerto.sql.element.UISQLComponent;
 import org.openconcerto.sql.model.SQLName;
+import org.openconcerto.sql.model.SQLRow;
 import org.openconcerto.sql.model.SQLRowAccessor;
+import org.openconcerto.sql.model.SQLRowValues;
+import org.openconcerto.sql.model.SQLRowValuesListFetcher;
 import org.openconcerto.sql.model.SQLSyntax;
+import org.openconcerto.sql.model.SQLTable;
 import org.openconcerto.sql.model.Where;
 import org.openconcerto.sql.utils.SQLCreateTable;
 import org.openconcerto.sql.view.FileDropHandler;
 import org.openconcerto.sql.view.IListFrame;
 import org.openconcerto.sql.view.IListPanel;
-import org.openconcerto.sql.view.ListeAddPanel;
 import org.openconcerto.sql.view.list.IListe;
 import org.openconcerto.sql.view.list.RowAction;
 import org.openconcerto.sql.view.list.SQLTableModelSourceOnline;
 import org.openconcerto.ui.PanelFrame;
+import org.openconcerto.ui.group.Group;
+import org.openconcerto.ui.group.LayoutHints;
 import org.openconcerto.utils.FileUtils;
 import org.openconcerto.utils.ListMap;
 import org.openconcerto.utils.PrefType;
+import org.openconcerto.utils.StringUtils;
 import org.openconcerto.utils.cc.IClosure;
 public final class Module extends AbstractModule {
+    private static final String CLIENT_ADH_FIELDNAME = "ID_ADHERENT";
+    private static final Pattern FIRST_NAME_PATTERN = Pattern.compile("([^0-9]*)([0-9]*).*");
+    public static final boolean HAS_CODE_IN_FIRST_NAME = Boolean.getBoolean("adherent.hasCodeInFirstName");
     public Module(ModuleFactory f) throws IOException {
         super(f);
+    }
     @Override
-    protected void install(DBContext ctxt) {
+    protected void install(final DBContext ctxt) throws SQLException, IOException {
         super.install(ctxt);
+        final AdresseSQLElement addrElem = ctxt.getElementDirectory().getElement(AdresseSQLElement.class);
+        final ModuleVersion dbVersion = ctxt.getLastInstalledVersion() == null ? ModuleVersion.MIN : ctxt.getLastInstalledVersion();
+        if (dbVersion.getMerged() > this.getFactory().getVersion().getMerged())
+            throw new IllegalStateException("Cannot downgrade from " + dbVersion + " to " + this.getFactory());
-        if (!ctxt.getTablesPreviouslyCreated().contains("ENTREE")) {
+        if (dbVersion.getMerged() < ModuleVersion.getMerged(1, 0)) {
             // ENTREE
             final SQLCreateTable createTableEntree = ctxt.getCreateTable("ENTREE");
             createTableEntree.addDateAndTimeColumn("DATE");
             createTableEntree.addVarCharColumn("NUMERO_CARTE", 256);
             createTableEntree.addVarCharColumn("ADHERENT", 512);
             createTableEntree.addVarCharColumn("MOTIF", 2048);
             createTableEntree.addColumn("ACCEPTE", "boolean");
-        }
-        if (!ctxt.getTablesPreviouslyCreated().contains("PLAGE_HORAIRE")) {
             // PLAGE
             final SQLCreateTable createTablePlage = ctxt.getCreateTable("PLAGE_HORAIRE");
             createTablePlage.addVarCharColumn("NOM", 256);
             createTablePlage.addColumn("DEBUT_1_LUNDI", "time");
             createTablePlage.addColumn("DEBUT_1_MARDI", "time");
             createTablePlage.addColumn("FIN_3_MERCREDI", "time");
             createTablePlage.addColumn("FIN_3_JEUDI", "time");
             createTablePlage.addColumn("FIN_3_VENDREDI", "time");
             createTablePlage.addColumn("FIN_3_SAMEDI", "time");
             createTablePlage.addColumn("FIN_3_DIMANCHE", "time");
-        }
-        if (!ctxt.getTablesPreviouslyCreated().contains("ADHERENT")) {
             // ADHERENT
             final SQLCreateTable createTable = ctxt.getCreateTable("ADHERENT");
             createTable.addVarCharColumn("NUMERO_CARTE", 256);
             createTable.addVarCharColumn("NOM", 256);
             createTable.addVarCharColumn("PRENOM", 256);
             createTable.addVarCharColumn("INFOS", 2048);
             createTable.addColumn("DATE_VALIDITE_INSCRIPTION", "date");
             createTable.addColumn("DATE_NAISSANCE", "date");
-            createTable.addForeignColumn("ID_ADRESSE", Configuration.getInstance().getRoot().findTable("ADRESSE"));
+            createTable.addForeignColumn("ID_ADRESSE", addrElem.getTable());
             createTable.addForeignColumn("ID_PLAGE_HORAIRE", new SQLName("PLAGE_HORAIRE"), SQLSyntax.ID_NAME, null);
+        }
+        // at least v1.0
+        if (dbVersion.getMerged() < ModuleVersion.getMerged(1, 1)) {
+            // migrate from non-private
+            final SQLTable adhT = ctxt.getRoot().getTable("ADHERENT");
+            final CustomerSQLElement clientElem = ctxt.getElementDirectory().getElement(CustomerSQLElement.class);
+            final SQLTable clientT = clientElem.getTable();
+            ctxt.getAlterTable(clientT.getName()).addForeignColumn(CLIENT_ADH_FIELDNAME, adhT);
+            // fill new field (no need to do anything in uninstall() since the field will be
+            // dropped and new clients shouldn't be dropped)
+            if (adhT.getRowCount(false) > 0) {
+                ctxt.executeSQL();
+                this.setupElements(ctxt.getElementDirectory());
+                // 1. fetch all clients
+                final ListMap<String, SQLRow> clientByName = new ListMap<>();
+                final ListMap<String, SQLRow> clientByCode = new ListMap<>();
+                for (final SQLRowValues clientR : SQLRowValuesListFetcher.create(new SQLRowValues(clientT).putNulls("NOM", "CODE")).fetch()) {
+                    final String normalized = normalize(clientR.getString("NOM"));
+                    clientByName.add(normalized, clientR.asRow());
+                    clientByCode.add(clientR.getString("CODE"), clientR.asRow());
+                }
+                // 2. for each ADHERENT, link it to a single matching CLIENT, or create a
+                // new one. Also fill CLIENT fields with duplicates from ADHERENT.
+                // créer plutôt plus de clients : si besoin, fusion après coup.
+                final SQLRowValues adhToFetch = new SQLRowValues(adhT).putNulls("NOM", "PRENOM", "MAIL", "TEL", "DATE_NAISSANCE");
+                adhToFetch.putRowValues("ID_ADRESSE").setAllToNull();
+                final Map<SQLRow, SQLRow> updatedClientsAndAdh = new HashMap<>();
+                for (final SQLRowValues adhR : SQLRowValuesListFetcher.create(adhToFetch).fetch(null, true)) {
+                    final String firstName;
+                    final String code;
+                    SQLRow existingClient = null;
+                    if (HAS_CODE_IN_FIRST_NAME) {
+                        final Matcher matcher = FIRST_NAME_PATTERN.matcher(adhR.getString("PRENOM"));
+                        if (!matcher.matches())
+                            throw new IllegalStateException("Couldn't match " + adhR);
+                        firstName = matcher.group(1).trim();
+                        code = matcher.group(2);
+                    } else {
+                        firstName = adhR.getString("PRENOM");
+                        code = null;
+                    }
+                    if (!StringUtils.isEmpty(code)) {
+                        existingClient = getUnique(clientByCode.get(code));
+                    }
+                    final String firstLastName = firstName + " " + adhR.getString("NOM");
+                    if (existingClient == null) {
+                        existingClient = getUnique(clientByName.get(normalize(firstLastName)));
+                    }
+                    final SQLRow newClient;
+                    if (existingClient != null && !updatedClientsAndAdh.containsKey(existingClient)) {
+                        updatedClientsAndAdh.put(existingClient, adhR.asRow());
+                        // TODO use SQLElement.createFetcher()
+                        final SQLRowValues clientToUpdateVals = SQLRowValuesListFetcher.create(clientElem.createGraph()).fetchOne(existingClient.getIDNumber(), true);
+                        // TODO use new UpdateAction(SQLElement.createContext())
+                        final SQLRowValues newVals = fillFields(clientToUpdateVals.deepCopy(), adhR, false, addrElem);
+                        newClient = clientElem.update(clientToUpdateVals, newVals).exec();
+                    } else {
+                        // TODO use Logger
+                        if (existingClient != null) {
+                            System.err.println("While matching " + adhT + ", existing client " + existingClient + " already updated with " + updatedClientsAndAdh.get(existingClient)
+                                    + " so creating new client for " + adhR);
+                        } else {
+                            System.err.println("While matching " + adhT + ", no client found for " + adhR);
+                        }
+                        // create new client row
+                        // TODO use new InsertAction(SQLElement.createContext()) or at least
+                        // SQLElement.insert()
+                        final SQLRowValues vals = fillFields(new SQLRowValues(clientT).put("NOM", firstLastName), adhR, true, addrElem);
+                        if (!StringUtils.isEmpty(code))
+                            vals.put("CODE", code);
+                        newClient = vals.insert();
+                    }
+                    final SQLRowValues contactVals = new SQLRowValues(ctxt.getElementDirectory().getElement(ContactSQLElement.class).getTable());
+                    contactVals.putForeignID("ID_CLIENT", newClient);
+                    contactVals.load(adhR.asRow(), Arrays.asList("NOM", "DATE_NAISSANCE"));
+                    contactVals.put("PRENOM", firstName);
+                    contactVals.put("EMAIL", adhR.getString("MAIL"));
+                    contactVals.insert();
+                }
+                ctxt.getAlterTable(adhT.getName()).dropColumn("ID_ADRESSE");
+            }
+        }
+    }
+    protected SQLRow getUnique(final List<SQLRow> clientRows) {
+        return clientRows != null && clientRows.size() == 1 ? clientRows.get(0) : null;
+    }
+    private final SQLRowValues fillFields(final SQLRowValues clientVals, final SQLRowValues adhVals, final boolean useAdhID, final SQLElement addrElem) {
+        clientVals.put(CLIENT_ADH_FIELDNAME, useAdhID ? adhVals.getIDNumber() : adhVals.deepCopy());
+        concatField(clientVals, adhVals, "MAIL");
+        concatField(clientVals, adhVals, "TEL");
+        final SQLRowValues adhAddr = (SQLRowValues) adhVals.getNonEmptyForeign("ID_ADRESSE");
+        if (adhAddr != null && !StringUtils.isEmpty(adhAddr.getString("VILLE"), true)) {
+            clientVals.put("ID_ADRESSE", addrElem.createCopy(adhAddr, true, null));
+        }
+        return clientVals;
+    }
+    private final void concatField(final SQLRowValues clientVals, final SQLRowValues adhVals, final String fieldName) {
+        final String adhStr = adhVals.getString(fieldName);
+        if (StringUtils.isEmpty(adhStr, true))
+            return;
+        final String clientStr = clientVals.getString(fieldName);
+        if (StringUtils.isEmpty(clientStr, true)) {
+            clientVals.put(fieldName, adhStr);
+        } else {
+            clientVals.put(fieldName, clientStr + ", " + adhStr);
+        }
+    }
+    static private final Pattern diacriticalPattern = Pattern.compile("\\p{InCombiningDiacriticalMarks}+");
+    static private final Pattern punctPattern = Pattern.compile("\\p{Punct}+");
+    static private final Pattern spacePattern = Pattern.compile("\\p{Space}+");
+    // remove punctation, multiple spaces and accents
+    static private String normalize(final String s) {
+        final String noMarks = diacriticalPattern.matcher(Normalizer.normalize(s, Form.NFD)).replaceAll("");
+        final String noPunct = punctPattern.matcher(noMarks).replaceAll("_");
+        return spacePattern.matcher(noPunct.trim()).replaceAll(" ").toLowerCase();
+    }
     @Override
     protected void setupElements(SQLElementDirectory dir) {
         super.setupElements(dir);
         return panel;
+    }
     @Override
     protected void setupComponents(ComponentsContext ctxt) {
+        final Group g = new Group("customerrelationship.customer.adherent", LayoutHints.DEFAULT_SEPARATED_GROUP_HINTS);
+        ctxt.getElement("CLIENT").getDefaultGroup().add(g);
+        g.addItem(CLIENT_ADH_FIELDNAME, new LayoutHints(true, true, true, true, true, true));
         ctxt.addFileDropHandler("ADHERENT", new FileDropHandler() {
             @Override
             public boolean handle(File f, Component source) {
                 AdherentImporter importer;
             public boolean canHandle(File f) {
                 String n = f.getName().toLowerCase();
                 return n.endsWith(".xls") || n.endsWith(".ods");
+            }
         });
+    }
     @Override
     protected void setupMenu(MenuContext ctxt) {
-        ctxt.addMenuItem(new CreateFrameAbstractAction("Liste des adhérents") {
-            @Override
-            public JFrame createFrame() {
-                IListFrame frameAdh = new IListFrame(new ListeAddPanel(Configuration.getInstance().getDirectory().getElement("ADHERENT")));
-                return frameAdh;
-            }
-        }, MainFrame.LIST_MENU);
         ctxt.addMenuItem(new CreateFrameAbstractAction("Liste des entrées") {
             @Override
             public JFrame createFrame() {

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

svn://code.openconcerto.org/openconcerto

(root)/trunk/Modules/Module Badge/src/org/openconcerto/modules/badge/Module.java @ 167 - Rev 118 → 153