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

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 javax.swing.table.DefaultTableCellRenderer;

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.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(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 (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");

            // 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("DEBUT_1_MERCREDI", "time");
            createTablePlage.addColumn("DEBUT_1_JEUDI", "time");
            createTablePlage.addColumn("DEBUT_1_VENDREDI", "time");
            createTablePlage.addColumn("DEBUT_1_SAMEDI", "time");
            createTablePlage.addColumn("DEBUT_1_DIMANCHE", "time");

            createTablePlage.addColumn("DEBUT_2_LUNDI", "time");
            createTablePlage.addColumn("DEBUT_2_MARDI", "time");
            createTablePlage.addColumn("DEBUT_2_MERCREDI", "time");
            createTablePlage.addColumn("DEBUT_2_JEUDI", "time");
            createTablePlage.addColumn("DEBUT_2_VENDREDI", "time");
            createTablePlage.addColumn("DEBUT_2_SAMEDI", "time");
            createTablePlage.addColumn("DEBUT_2_DIMANCHE", "time");

            createTablePlage.addColumn("DEBUT_3_LUNDI", "time");
            createTablePlage.addColumn("DEBUT_3_MARDI", "time");
            createTablePlage.addColumn("DEBUT_3_MERCREDI", "time");
            createTablePlage.addColumn("DEBUT_3_JEUDI", "time");
            createTablePlage.addColumn("DEBUT_3_VENDREDI", "time");
            createTablePlage.addColumn("DEBUT_3_SAMEDI", "time");
            createTablePlage.addColumn("DEBUT_3_DIMANCHE", "time");

            createTablePlage.addColumn("FIN_1_LUNDI", "time");
            createTablePlage.addColumn("FIN_1_MARDI", "time");
            createTablePlage.addColumn("FIN_1_MERCREDI", "time");
            createTablePlage.addColumn("FIN_1_JEUDI", "time");
            createTablePlage.addColumn("FIN_1_VENDREDI", "time");
            createTablePlage.addColumn("FIN_1_SAMEDI", "time");
            createTablePlage.addColumn("FIN_1_DIMANCHE", "time");

            createTablePlage.addColumn("FIN_2_LUNDI", "time");
            createTablePlage.addColumn("FIN_2_MARDI", "time");
            createTablePlage.addColumn("FIN_2_MERCREDI", "time");
            createTablePlage.addColumn("FIN_2_JEUDI", "time");
            createTablePlage.addColumn("FIN_2_VENDREDI", "time");
            createTablePlage.addColumn("FIN_2_SAMEDI", "time");
            createTablePlage.addColumn("FIN_2_DIMANCHE", "time");

            createTablePlage.addColumn("FIN_3_LUNDI", "time");
            createTablePlage.addColumn("FIN_3_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");

            // ADHERENT
            final SQLCreateTable createTable = ctxt.getCreateTable("ADHERENT");

            createTable.addVarCharColumn("NUMERO_CARTE", 256);
            createTable.addVarCharColumn("NOM", 256);

            createTable.addVarCharColumn("TEL", 256);
            createTable.addColumn("ACTIF", "boolean default false");
            createTable.addColumn("ADMIN", "boolean default false");
            createTable.addVarCharColumn("MAIL", 256);
            createTable.addVarCharColumn("PRENOM", 256);
            createTable.addVarCharColumn("INFOS", 2048);
            createTable.addColumn("DATE_VALIDITE_INSCRIPTION", "date");
            createTable.addColumn("DATE_NAISSANCE", "date");

            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);

        final ComptaSQLConfElement entreeElement = new ComptaSQLConfElement("ENTREE", "une entrée", "entrées") {

            @Override
            protected List<String> getListFields() {
                final List<String> l = new ArrayList<String>();
                l.add("DATE");
                l.add("NUMERO_CARTE");
                l.add("ADHERENT");
                l.add("MOTIF");
                l.add("ACCEPTE");
                return l;
            }

            @Override
            public Set<String> getReadOnlyFields() {
                Set<String> s = new HashSet<String>(super.getReadOnlyFields());
                s.add("NUMERO_CARTE");
                return s;
            }

            @Override
            protected List<String> getComboFields() {
                final List<String> l = new ArrayList<String>();
                l.add("NUMERO_CARTE");
                l.add("DATE");
                return l;
            }

            @Override
            public ListMap<String, String> getShowAs() {
                return ListMap.singleton(null, getComboFields());
            }

            @Override
            public SQLComponent createComponent() {
                return new UISQLComponent(this) {
                    @Override
                    protected void addViews() {
                        this.addView("NUMERO_CARTE");
                        this.addView("DATE");
                    }
                };
            }
        };
        dir.addSQLElement(entreeElement);
        dir.addSQLElement(new PlageHoraireSQLElement());
        dir.addSQLElement(new AdherentSQLElement());
    }

    private IListPanel getPanelEntree(boolean filtered) {
        final SQLElement element = Configuration.getInstance().getDirectory().getElement("ENTREE");

        final SQLTableModelSourceOnline tableModelEntree = element.getTableSource(true);

        if (filtered) {
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.DAY_OF_MONTH, -15);
            Where wAttente = new Where(element.getTable().getField("DATE"), ">=", cal.getTime());
            tableModelEntree.getReq().setWhere(wAttente);
        }
        tableModelEntree.getColumn(element.getTable().getField("DATE")).setRenderer(new DefaultTableCellRenderer() {

            DateFormat format = new SimpleDateFormat("EEEE dd MMMM yyyy HH:mm:ss");

            @Override
            public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus, int row, int column) {
                final String format2 = format.format((Date) value);
                return super.getTableCellRendererComponent(table, format2, isSelected, hasFocus, row, column);

            }
        });

        final IListPanel panel = new ListeViewPanel(element, new IListe(tableModelEntree));

        final Action createAction = RowAction.createAction("Assigner à", (Icon) null, new IClosure<List<? extends SQLRowAccessor>>() {
            @Override
            public void executeChecked(List<? extends SQLRowAccessor> input) {
                SQLRowAccessor row = input.get(0);
                PanelFrame frame = new PanelFrame(new AssignationPanel(row.getString("NUMERO_CARTE")), "Assignation d'une carte");
                frame.setLocationRelativeTo(null);
                frame.setVisible(true);

            }
        });

        panel.getListe().addRowAction(createAction);

        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;
                try {
                    importer = new AdherentImporter(f);
                    importer.importAdherent();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                return true;
            }

            @Override
            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 entrées") {

            @Override
            public JFrame createFrame() {

                return new IListFrame(getPanelEntree(false));
            }
        }, MainFrame.LIST_MENU);
        ctxt.addMenuItem(new PreferencesAction(), MainFrame.FILE_MENU);
    }

    @Override
    protected void start() {
        MainFrame.getInstance().getTabbedPane().addTab("Liste des entrées", getPanelEntree(true));
    }

    @Override
    protected void stop() {
    }

    public static void main(String[] args) throws IOException {
        final File propsFile = new File("module.properties");

        final ModuleFactory factory = new RuntimeModuleFactory(propsFile);

        final File distDir = new File("dist");
        FileUtils.mkdir_p(distDir);
        new ModulePackager(propsFile, new File("bin/")).writeToDir(distDir);
        new ModulePackager(propsFile, new File("bin/")).writeToDir(new File("../OpenConcerto/Modules"));

        ModuleManager.getInstance().addFactoryAndStart(factory, false);

        Gestion.main(args);

    }

    public final static String ENTREE_PREF = "entreeAdmin";

    @Override
    public List<ModulePreferencePanelDesc> getPrefDescriptors() {
        return Arrays.<ModulePreferencePanelDesc> asList(new ModulePreferencePanelDesc("Gestion des entrées") {
            @Override
            protected ModulePreferencePanel createPanel() {
                return new ModulePreferencePanel("Gestion des entrées") {
                    @Override
                    protected void addViews() {

                        this.addView(new SQLPrefView<Boolean>(PrefType.BOOLEAN_TYPE, "N'autoriser que les administrateurs à entrer ", ENTREE_PREF));
                    }
                };
            }
        }.setLocal(false).setKeywords("entrée"));
    }

}