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

/*

 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.

 *

 * Copyright 2011-2019 OpenConcerto, by ILM Informatique. All rights reserved.

 *

 * The contents of this file are subject to the terms of the GNU General Public License Version 3

 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a

 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific

 * language governing permissions and limitations under the License.

 *

 * When distributing the software, include this License Header Notice in each file.

 */

 package org.openconcerto.erp.core.finance.accounting.ui;

import org.openconcerto.erp.config.ComptaPropsConfiguration;

import org.openconcerto.erp.core.common.element.NumerotationAutoSQLElement;

import org.openconcerto.erp.core.common.ui.DeviseNiceTableCellRenderer;

import org.openconcerto.erp.core.finance.accounting.element.ComptePCESQLElement;

import org.openconcerto.erp.core.finance.accounting.element.EcritureSQLElement;

import org.openconcerto.erp.core.finance.accounting.element.JournalSQLElement;

import org.openconcerto.erp.core.finance.accounting.element.MouvementSQLElement;

import org.openconcerto.erp.core.finance.accounting.model.LettrageModel;

import org.openconcerto.erp.model.ISQLCompteSelector;

import org.openconcerto.erp.rights.ComptaUserRight;

import org.openconcerto.erp.utils.LowerCaseFormatFilter;

import org.openconcerto.erp.utils.UpperCaseFormatFilter;

import org.openconcerto.sql.Configuration;

import org.openconcerto.sql.element.SQLElement;

import org.openconcerto.sql.element.SQLElementDirectory;

import org.openconcerto.sql.model.SQLBase;

import org.openconcerto.sql.model.SQLRow;

import org.openconcerto.sql.model.SQLRowAccessor;

import org.openconcerto.sql.model.SQLRowListRSH;

import org.openconcerto.sql.model.SQLRowValues;

import org.openconcerto.sql.model.SQLSelect;

import org.openconcerto.sql.model.SQLSystem;

import org.openconcerto.sql.model.SQLTable;

import org.openconcerto.sql.model.UndefinedRowValuesCache;

import org.openconcerto.sql.model.Where;

import org.openconcerto.sql.request.ComboSQLRequest;

import org.openconcerto.sql.users.rights.UserRightsManager;

import org.openconcerto.sql.view.EditFrame;

import org.openconcerto.sql.view.EditPanel.EditMode;

import org.openconcerto.sql.view.EditPanelListener;

import org.openconcerto.sql.view.list.IListe;

import org.openconcerto.sql.view.list.IListeAction.IListeEvent;

import org.openconcerto.sql.view.list.RowAction.PredicateRowAction;

import org.openconcerto.ui.DefaultGridBagConstraints;

import org.openconcerto.ui.FontUtils;

import org.openconcerto.ui.JDate;

import org.openconcerto.ui.TitledSeparator;

import org.openconcerto.ui.warning.JLabelWarning;

import org.openconcerto.utils.ExceptionHandler;

import org.openconcerto.utils.ListMap;

import org.openconcerto.utils.text.DocumentFilterList;

import org.openconcerto.utils.text.DocumentFilterList.FilterType;

import org.openconcerto.utils.text.SimpleDocumentListener;

import java.awt.Color;

import java.awt.Component;

import java.awt.Dimension;

import java.awt.GridBagConstraints;

import java.awt.GridBagLayout;

import java.awt.Insets;

import java.awt.Window;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.awt.event.KeyAdapter;

import java.awt.event.KeyEvent;

import java.awt.event.MouseAdapter;

import java.awt.event.MouseEvent;

import java.beans.PropertyChangeEvent;

import java.beans.PropertyChangeListener;

import java.sql.SQLException;

import java.text.ParseException;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Map.Entry;

import javax.swing.AbstractAction;

import javax.swing.BorderFactory;

import javax.swing.ButtonGroup;

import javax.swing.JButton;

import javax.swing.JCheckBox;

import javax.swing.JLabel;

import javax.swing.JPanel;

import javax.swing.JRadioButton;

import javax.swing.JScrollPane;

import javax.swing.JTable;

import javax.swing.JTextField;

import javax.swing.SwingConstants;

import javax.swing.SwingUtilities;

import javax.swing.event.DocumentEvent;

import javax.swing.text.AbstractDocument;

import javax.swing.text.MaskFormatter;

public class LettragePanel extends JPanel {

    private ListPanelEcritures ecriturePanel;

    private JTextField codeLettrage, codeLettragePartiel;

    private ISQLCompteSelector selCompte;

    private JCheckBox boxValidEcriture, boxAddSousCompte;

    private JPanel warningPanel, warningSolde;

    private final SQLBase base = ((ComptaPropsConfiguration) Configuration.getInstance()).getSQLBaseSociete();

    private final SQLTable tableEcr = this.base.getTable("ECRITURE");

    private final SQLTable tableComptePCE = this.base.getTable("COMPTE_PCE");

    private final static int allEcriture = 0;

    private final static int ecritureLettree = 1;

    private final static int ecritureNonLettree = 2;

    private int modeSelect;

    private LettrageModel model;

    private JButton buttonLettrer;

    private final JButton buttonRegler = new JButton("Régler");

    private JDate dateDeb, dateFin, dateLettrage;

    public LettragePanel() {

        this(ComptePCESQLElement.getId("4"));

    }

    public LettragePanel(int idCompte) {

        this.setLayout(new GridBagLayout());

        GridBagConstraints c = new DefaultGridBagConstraints();

        final SQLElementDirectory directory = Configuration.getInstance().getDirectory();

        this.modeSelect = allEcriture;

        // Selection du compte à lettrer

        JLabel labelPointageCompte = new JLabel("Lettrage du compte");

        labelPointageCompte.setHorizontalAlignment(SwingConstants.RIGHT);

        this.add(labelPointageCompte, c);

        this.selCompte = new ISQLCompteSelector();

        SQLElement eltCpt = directory.getElement("COMPTE_PCE");

        final ComboSQLRequest createComboRequest = eltCpt.createComboRequest();

        Where w = createTierWhere(eltCpt);

        createComboRequest.setWhere(w);

        this.selCompte.init(eltCpt, createComboRequest);

        this.selCompte.setValue(idCompte);

        c.gridx++;

        c.weightx = 1;

        c.gridwidth = GridBagConstraints.REMAINDER;

        this.add(this.selCompte, c);

        c.gridx = 0;

        c.gridy++;

        final JCheckBox boxLimitCompteTier = new JCheckBox("Limiter aux comptes de classe 4");

        boxLimitCompteTier.setSelected(true);

        boxLimitCompteTier.addActionListener(new ActionListener() {

            public void actionPerformed(ActionEvent e) {

                if (boxLimitCompteTier.isSelected()) {

                    selCompte.getRequest().setWhere(w);

                } else {

                    selCompte.getRequest().setWhere(null);

                }

            }

        });

        this.add(boxLimitCompteTier, c);

        c.gridwidth = 1;

        // Gestion du lettrage

        c.insets = new Insets(2, 2, 1, 2);

        TitledSeparator sepGestionLettrage = new TitledSeparator("Gestion du lettrage");

        c.fill = GridBagConstraints.HORIZONTAL;

        c.gridy++;

        c.gridx = 0;

        c.gridwidth = GridBagConstraints.REMAINDER;

        this.add(sepGestionLettrage, c);

        // Code de lettrage

        JLabel labelCode = new JLabel("Code lettrage");

        labelCode.setHorizontalAlignment(SwingConstants.RIGHT);

        c.gridx = 0;

        c.gridy++;

        c.gridwidth = 1;

        c.weightx = 0;

        this.add(labelCode, c);

        this.codeLettrage = new JTextField(10);

        DocumentFilterList.add((AbstractDocument) this.codeLettrage.getDocument(), new UpperCaseFormatFilter(), FilterType.SIMPLE_FILTER);

        c.gridx++;

        c.weightx = 1;

        this.add(this.codeLettrage, c);

        this.codeLettrage.setText(NumerotationAutoSQLElement.getNextCodeLettrage());

        JLabel labelCodepartiel = new JLabel("Code lettrage partiel");

        labelCodepartiel.setHorizontalAlignment(SwingConstants.RIGHT);

        c.gridx++;

        c.gridwidth = 1;

        c.weightx = 0;

        this.add(labelCodepartiel, c);

        this.codeLettragePartiel = new JTextField(10);

        DocumentFilterList.add((AbstractDocument) this.codeLettragePartiel.getDocument(), new LowerCaseFormatFilter(), FilterType.SIMPLE_FILTER);

        c.gridx++;

        c.weightx = 1;

        this.add(this.codeLettragePartiel, c);

        this.codeLettragePartiel.setText(getNextCodeLettragePartiel());

        // Warning si aucun code rentré

        createPanelWarning();

        c.gridwidth = GridBagConstraints.REMAINDER;

        c.weightx = 0;

        c.gridx++;

        this.add(this.warningPanel, c);

        // Date de lettrage

        JLabel labelDate = new JLabel("Date");

        labelDate.setHorizontalAlignment(SwingConstants.RIGHT);

        c.gridx = 0;

        c.gridy++;

        c.weightx = 0;

        c.gridwidth = 1;

        this.add(labelDate, c);

        this.dateLettrage = new JDate(true);

        c.gridx++;

        this.add(this.dateLettrage, c);

        // Warning si solde non nul

        c.gridx++;

        createPanelWarningSolde();

        c.gridwidth = GridBagConstraints.REMAINDER;

        c.weightx = 0;

        this.add(this.warningSolde, c);

        c.gridwidth = 1;

        TitledSeparator sepPeriode = new TitledSeparator("Filtre ");

        c.gridy++;

        c.gridx = 0;

        c.anchor = GridBagConstraints.WEST;

        c.gridwidth = GridBagConstraints.REMAINDER;

        c.fill = GridBagConstraints.HORIZONTAL;

        this.add(sepPeriode, c);

        JPanel panelSelectEcritures = createPanelSelectionEcritures();

        c.gridy++;

        c.gridx = 0;

        c.gridwidth = 1;

        c.weightx = 0;

        JLabel labelEcr = new JLabel("Ecritures");

        labelEcr.setHorizontalAlignment(SwingConstants.RIGHT);

        this.add(labelEcr, c);

        c.gridx++;

        c.weightx = 1;

        c.gridwidth = GridBagConstraints.REMAINDER;

        c.fill = GridBagConstraints.NONE;

        this.add(panelSelectEcritures, c);

        JPanel panelPeriode = new JPanel();

        // Date de début

        this.dateDeb = new JDate();

        c.gridy++;

        c.gridx = 0;

        c.gridwidth = 1;

        c.weightx = 0;

        JLabel labelPerio = new JLabel("Période du ");

        labelPerio.setHorizontalAlignment(SwingConstants.RIGHT);

        c.fill = GridBagConstraints.HORIZONTAL;

        this.add(labelPerio, c);

        panelPeriode.add(this.dateDeb);

        this.dateDeb.addValueListener(new PropertyChangeListener() {

            public void propertyChange(PropertyChangeEvent evt) {

                changeListRequest();

            }

        });

        // Date de fin

        this.dateFin = new JDate(true);

        panelPeriode.add(new JLabel("au"));

        this.dateFin.addValueListener(new PropertyChangeListener() {

            public void propertyChange(PropertyChangeEvent evt) {

                changeListRequest();

            }

        });

        panelPeriode.add(this.dateFin);

        c.gridx++;

        c.gridwidth = GridBagConstraints.REMAINDER;

        c.weightx = 1;

        c.fill = GridBagConstraints.NONE;

        this.add(panelPeriode, c);

        c.gridx = 0;

        c.gridy++;

        this.boxAddSousCompte = new JCheckBox("Ajouter les sous comptes");

        this.boxAddSousCompte.addActionListener(new ActionListener() {

            public void actionPerformed(ActionEvent e) {

                // TODO Auto-generated method stub

                changeListRequest();

            }

        });

        this.add(this.boxAddSousCompte, c);

        TitledSeparator sepEcriture = new TitledSeparator("Ecritures ");

        c.gridy++;

        c.gridx = 0;

        c.weightx = 1;

        c.gridwidth = GridBagConstraints.REMAINDER;

        c.fill = GridBagConstraints.HORIZONTAL;

        this.add(sepEcriture, c);

        // Liste des ecritures

        final EcritureSQLElement ecritureElem = directory.getElement(EcritureSQLElement.class);

        this.ecriturePanel = new ListPanelEcritures(ecritureElem, new IListe(ecritureElem.createLettrageTableSource()));

        c.gridx = 0;

        c.gridy++;

        c.weighty = 1;

        c.weightx = 1;

        c.fill = GridBagConstraints.BOTH;

        c.gridwidth = GridBagConstraints.REMAINDER;

        this.ecriturePanel.getListe().setPreferredSize(new Dimension(this.ecriturePanel.getListe().getPreferredSize().width, 200));

        this.add(this.ecriturePanel, c);

        // JTable Totaux

        c.gridy++;

        c.gridx = 0;

        c.weighty = 0;

        c.weightx = 1;

        c.fill = GridBagConstraints.BOTH;

        c.gridwidth = 3;

        c.gridheight = 3;

        this.model = new LettrageModel(this.selCompte.getSelectedId());

        JTable table = new JTable(this.model);

        table.setRowHeight(FontUtils.getPreferredRowHeight(table));

        // AlternateTableCellRenderer.setAllColumns(table);

        for (int i = 0; i < table.getColumnCount(); i++) {

            // if (table.getColumnClass(i) == Long.class || table.getColumnClass(i) ==

            // BigInteger.class) {

            table.getColumnModel().getColumn(i).setCellRenderer(new DeviseNiceTableCellRenderer());

            // }

        }

        JScrollPane sPane = new JScrollPane(table);

        // TODO Gerer la taille des colonnes

        Dimension d = new Dimension(table.getPreferredSize().width, table.getPreferredSize().height + table.getTableHeader().getPreferredSize().height + 4);

        sPane.setPreferredSize(d);

        this.add(sPane, c);

        // Legende

        c.gridx = 4;

        c.gridwidth = 1;

        c.anchor = GridBagConstraints.EAST;

        c.fill = GridBagConstraints.NONE;

        c.weightx = 0;

        this.add(createPanelLegende(), c);

        c.gridheight = 1;

        final JButton buttonDelettrer = new JButton("Délettrer");

        this.buttonLettrer = new JButton("Lettrer");

        // Validation des ecritures pointées

        this.boxValidEcriture = new JCheckBox("Valider les écritures lettrées");

        c.gridx++;

        c.gridwidth = GridBagConstraints.REMAINDER;

        this.add(this.boxValidEcriture, c);

        JPanel panelButton = new JPanel();

        panelButton.add(this.buttonRegler, c);

        // Boutton lettrer

        panelButton.add(this.buttonLettrer, c);

        // Boutton Delettrer

        panelButton.add(buttonDelettrer, c);

        c.gridy++;

        c.gridx = 5;

        c.gridwidth = GridBagConstraints.REMAINDER;

        c.gridheight = 1;

        c.weightx = 1;

        c.fill = GridBagConstraints.NONE;

        c.anchor = GridBagConstraints.EAST;

        this.add(panelButton, c);

        c.gridy++;

        c.anchor = GridBagConstraints.SOUTHEAST;

        JButton buttonClose = new JButton("Fermer");

        buttonClose.addActionListener(new ActionListener() {

            @Override

            public void actionPerformed(ActionEvent e) {

                ((Window) SwingUtilities.getRoot((Component) e.getSource())).dispose();

            }

        });

        this.add(buttonClose, c);

        this.buttonLettrer.addActionListener(new ActionListener() {

            public void actionPerformed(ActionEvent e) {

                try {

                    int[] rowIndex = LettragePanel.this.ecriturePanel.getListe().getJTable().getSelectedRows();

                    actionLettrage(rowIndex, false);

                } catch (SQLException e1) {

                    ExceptionHandler.handle("Erreur de lettrage", e1);

                }

            }

        });

        this.buttonRegler.addActionListener(new ActionListener() {

            public void actionPerformed(ActionEvent e) {

                final List<SQLRowValues> res = LettragePanel.this.ecriturePanel.getListe().getSelectedRows();

                final SQLTable tableKm = LettragePanel.this.ecriturePanel.getListe().getSource().getPrimaryTable().getTable("SAISIE_KM");

                SQLRowValues rowValsKm = new SQLRowValues(tableKm);

                rowValsKm.put("DATE", new Date());

                rowValsKm.put("ID_JOURNAL", JournalSQLElement.BANQUES);

                long solde = LettragePanel.this.model.getSoldeSelection();

                final SQLTable tableKmItem = tableKm.getTable("SAISIE_KM_ELEMENT");

                List<String> pieces = new ArrayList<>();

                for (SQLRowValues sqlRowValues : res) {

                    SQLRowValues rowValsKmItemTiers = new SQLRowValues(UndefinedRowValuesCache.getInstance().getDefaultRowValues(tableKmItem));

                    rowValsKmItemTiers.put("NUMERO", sqlRowValues.getForeign("ID_COMPTE_PCE").getString("NUMERO"));

                    rowValsKmItemTiers.put("NOM", sqlRowValues.getForeign("ID_COMPTE_PCE").getString("NOM"));

                    final String pieceNom;

                    if (sqlRowValues.getString("NOM_PIECE").trim().length() > 0) {

                        pieceNom = sqlRowValues.getString("NOM_PIECE");

                    } else {

                        pieceNom = sqlRowValues.getForeign("ID_MOUVEMENT").getForeign("ID_PIECE").getString("NOM");

                    }

                    rowValsKmItemTiers.put("NOM_PIECE", pieceNom);

                    if (pieceNom != null && pieceNom.trim().length() > 0) {

                        pieces.add(pieceNom);

                    }

                    rowValsKmItemTiers.put("CREDIT", sqlRowValues.getLong("DEBIT"));

                    rowValsKmItemTiers.put("DEBIT", sqlRowValues.getLong("CREDIT"));

                    if (rowValsKmItemTiers.getTable().contains("MONTANT_ECHEANCE")) {

                        rowValsKmItemTiers.put("MONTANT_ECHEANCE", sqlRowValues.getLong("CREDIT"));

                    }

                    rowValsKmItemTiers.put("ID_SAISIE_KM", rowValsKm);

                }

                SQLRowValues rowValsKmItemBq = new SQLRowValues(UndefinedRowValuesCache.getInstance().getDefaultRowValues(tableKmItem));

                boolean achat = solde < 0;

                // Compte bq

                int idPce = tableKm.getTable("TYPE_REGLEMENT").getRow(2).getInt("ID_COMPTE_PCE_" + (achat ? "FOURN" : "CLIENT"));

                if (idPce <= 1) {

                    try {

                        idPce = ComptePCESQLElement.getIdComptePceDefault("VenteCB");

                    } catch (Exception e1) {

                        // TODO Auto-generated catch block

                        e1.printStackTrace();

                    }

                }

                final SQLTable tableAccount = tableKmItem.getTable("COMPTE_PCE");

                SQLRow rowCptBq = tableAccount.getRow(idPce);

                rowValsKmItemBq.put("NUMERO", rowCptBq.getString("NUMERO"));

                rowValsKmItemBq.put("NOM", rowCptBq.getString("NOM"));

                if (!pieces.isEmpty()) {

                    StringBuilder build = new StringBuilder();

                    int nbPieces = pieces.size();

                    int i = 0;

                    for (String string : pieces) {

                        build.append(string);

                        i++;

                        if (i < nbPieces) {

                            build.append(", ");

                        }

                    }

                    rowValsKmItemBq.put("NOM_PIECE", build.toString());

                }

                if (solde > 0) {

                    rowValsKmItemBq.put("CREDIT", solde);

                    rowValsKmItemBq.put("DEBIT", 0L);

                } else {

                    rowValsKmItemBq.put("DEBIT", -solde);

                    rowValsKmItemBq.put("CREDIT", 0L);

                }

                rowValsKmItemBq.put("ID_SAISIE_KM", rowValsKm);

                EditFrame frame = new EditFrame(Configuration.getInstance().getDirectory().getElement("SAISIE_KM"), EditMode.CREATION);

                frame.getSQLComponent().select(rowValsKm);

                frame.setVisible(true);

                frame.addEditPanelListener(new EditPanelListener() {

                    @Override

                    public void modified() {

                        // TODO Auto-generated method stub

                    }

                    @Override

                    public void inserted(int id) {

                        List<SQLRow> rowsInserted = tableKm.getRow(id).getReferentRows(tableKmItem);

                        List<String> piece = new ArrayList<>();

                        List<SQLRowAccessor> rowsToLettre = new ArrayList<>();

                        long solde = 0;

                        for (SQLRowValues sqlRowValues : res) {

                            rowsToLettre.add(sqlRowValues);

                            final String nomPiece = sqlRowValues.getString("NOM_PIECE");

                            if (sqlRowValues.getForeign("ID_COMPTE_PCE").getString("NUMERO").startsWith("4") && nomPiece.trim().length() > 0) {

                                piece.add(nomPiece);

                            }

                            solde += sqlRowValues.getLong("DEBIT");

                            solde -= sqlRowValues.getLong("CREDIT");

                        }

                        for (SQLRow sqlRow : rowsInserted) {

                            SQLRow rowEcr = sqlRow.getForeign("ID_ECRITURE");

                            final String nomPiece = sqlRow.getString("NOM_PIECE");

                            if (rowEcr.getString("COMPTE_NUMERO").startsWith("4")) {

                                solde += rowEcr.getLong("DEBIT");

                                solde -= rowEcr.getLong("CREDIT");

                                if (nomPiece.trim().length() > 0) {

                                    piece.add(nomPiece);

                                }

                                rowsToLettre.add(rowEcr);

                            }

                        }

                        if (solde == 0) {

                            final String codeLettre = codeLettrage.getText().trim();

                            for (SQLRowAccessor row2 : rowsToLettre) {

                                SQLRowValues rowVals = new SQLRowValues(row2.getTable());

                                // Lettrage

                                // On lettre ou relettre la ligne avec le code saisi

                                if (codeLettre.length() > 0) {

                                    rowVals.put("LETTRAGE_PARTIEL", "");

                                    rowVals.put("LETTRAGE", codeLettre);

                                    rowVals.put("DATE_LETTRAGE", dateLettrage.getDate());

                                    try {

                                        rowVals.update(row2.getID());

                                    } catch (SQLException e1) {

                                        e1.printStackTrace();

                                    }

                                }

                            }

                            // Mise à jour du code de lettrage

                            SQLElement elt = Configuration.getInstance().getDirectory().getElement("NUMEROTATION_AUTO");

                            SQLRowValues rowVals = elt.getTable().getRow(2).createEmptyUpdateRow();

                            rowVals.put("CODE_LETTRAGE", codeLettre);

                            try {

                                rowVals.update();

                            } catch (SQLException e) {

                                e.printStackTrace();

                            }

                            codeLettrage.setText(getNextCodeLettrage());

                            model.updateTotauxCompte();

                        } else {

                            String codeLettreP = codeLettragePartiel.getText().trim();

                            String codeLettre = codeLettrage.getText().trim();

                            SQLSelect selEcr = new SQLSelect();

                            SQLTable tableEcr = tableKm.getTable("ECRITURE");

                            selEcr.addSelect(tableEcr.getKey());

                            selEcr.addSelect(tableEcr.getField("NOM_PIECE"));

                            selEcr.addSelect(tableEcr.getField("DEBIT"));

                            selEcr.addSelect(tableEcr.getField("CREDIT"));

                            selEcr.addSelect(tableEcr.getField("LETTRAGE"));

                            selEcr.addSelect(tableEcr.getField("LETTRAGE_PARTIEL"));

                            Where w2 = new Where(tableEcr.getField("NOM_PIECE"), piece);

                            w2 = w2.and(new Where(tableEcr.getField("COMPTE_NUMERO"), "LIKE", "40%").or(new Where(tableEcr.getField("COMPTE_NUMERO"), "LIKE", "41%")));

                            w2 = w2.and(new Where(tableEcr.getField("DATE_LETTRAGE"), "=", (Object) null));

                            selEcr.setWhere(w2);

                            List<SQLRow> rows = SQLRowListRSH.execute(selEcr);

                            ListMap<String, SQLRow> mapPiece = new ListMap<>();

                            Map<String, Long> soldePiece = new HashMap<>();

                            for (SQLRow sqlRow : rows) {

                                String pieceName = sqlRow.getString("NOM_PIECE");

                                mapPiece.add(pieceName, sqlRow);

                                long soldeRow = sqlRow.getLong("DEBIT") - sqlRow.getLong("CREDIT");

                                if (soldePiece.containsKey(pieceName)) {

                                    soldePiece.put(pieceName, soldePiece.get(pieceName) + soldeRow);

                                } else {

                                    soldePiece.put(pieceName, soldeRow);

                                }

                            }

                            for (Entry<String, List<SQLRow>> entry : mapPiece.entrySet()) {

                                if (soldePiece.get(entry.getKey()) == 0) {

                                    try {

                                        for (SQLRow rowEcr : entry.getValue()) {

                                            SQLRowValues rowVals = rowEcr.createEmptyUpdateRow();

                                            // Lettrage

                                            // On lettre ou relettre la ligne avec le code saisi

                                            if (codeLettre.length() > 0) {

                                                rowVals.put("LETTRAGE_PARTIEL", "");

                                                rowVals.put("LETTRAGE", codeLettre);

                                                rowVals.put("DATE_LETTRAGE", dateLettrage.getDate());

                                                rowVals.update();

                                            }

                                        }

                                        // Mise à jour du code de lettrage

                                        SQLElement elt = Configuration.getInstance().getDirectory().getElement("NUMEROTATION_AUTO");

                                        SQLRowValues rowVals = elt.getTable().getRow(2).createEmptyUpdateRow();

                                        rowVals.put("CODE_LETTRAGE", codeLettre);

                                        rowVals.update();

                                        codeLettre = getNextCodeLettrage();

                                    } catch (SQLException e) {

                                        e.printStackTrace();

                                    }

                                } else {

                                    try {

                                        for (SQLRow rowEcr : entry.getValue()) {

                                            SQLRowValues rowVals = rowEcr.createEmptyUpdateRow();

                                            // Lettrage

                                            // On lettre ou relettre la ligne avec le code saisi

                                            if (codeLettreP.length() > 0) {

                                                rowVals.put("LETTRAGE_PARTIEL", codeLettreP);

                                                rowVals.update();

                                            }

                                        }

                                        // Mise à jour du code de lettrage

                                        SQLElement elt = Configuration.getInstance().getDirectory().getElement("NUMEROTATION_AUTO");

                                        SQLRowValues rowVals = elt.getTable().getRow(2).createEmptyUpdateRow();

                                        rowVals.put("CODE_LETTRAGE_PARTIEL", codeLettreP);

                                        rowVals.update();

                                        codeLettreP = getNextCodeLettragePartiel();

                                    } catch (SQLException e) {

                                        e.printStackTrace();

                                    }

                                }

                            }

                            codeLettrage.setText(getNextCodeLettrage());

                            codeLettragePartiel.setText(getNextCodeLettragePartiel());

                            model.updateTotauxCompte();

                        }

                    }

                    @Override

                    public void deleted() {

                        // TODO Auto-generated method stub

                    }

                    @Override

                    public void cancelled() {

                        // TODO Auto-generated method stub

                    }

                });

            }

        });

        buttonDelettrer.addActionListener(new ActionListener() {

            public void actionPerformed(ActionEvent e) {

                int[] rowIndex = LettragePanel.this.ecriturePanel.getListe().getJTable().getSelectedRows();

                actionDelettrage(rowIndex, false);

            }

        });

        // Changement de compte

        this.selCompte.addModelListener("wantedID", new PropertyChangeListener() {

            public void propertyChange(PropertyChangeEvent evt) {

                changeListRequest();

            };

        });

        // Action Souris sur la IListe

        addActionMenuDroit();

        this.ecriturePanel.getListe().getJTable().addMouseListener(new MouseAdapter() {

            public void mouseReleased(MouseEvent e) {

                System.err.println("Mouse released");

                int[] selectedRows = LettragePanel.this.ecriturePanel.getListe().getJTable().getSelectedRows();

                int[] idRows = new int[selectedRows.length];

                for (int i = 0; i < idRows.length; i++) {

                    idRows[i] = LettragePanel.this.ecriturePanel.getListe().idFromIndex(selectedRows[i]);

                }

                LettragePanel.this.model.updateSelection(idRows);

                LettragePanel.this.warningSolde.setVisible(LettragePanel.this.model.getSoldeSelection() != 0);

                buttonDelettrer.setEnabled(LettragePanel.this.model.getSoldeSelection() == 0);

                LettragePanel.this.buttonLettrer.setEnabled(LettragePanel.this.model.getSoldeSelection() == 0);

                LettragePanel.this.buttonRegler.setEnabled(!LettragePanel.this.ecriturePanel.getListe().getSelectedRows().isEmpty());

            }

        });

        // action sur la IListe

        this.ecriturePanel.getListe().getJTable().addKeyListener(new KeyAdapter() {

            public void keyReleased(KeyEvent e) {

                System.err.println("Key released");

                int[] selectedRows = LettragePanel.this.ecriturePanel.getListe().getJTable().getSelectedRows();

                int[] idRows = new int[selectedRows.length];

                for (int i = 0; i < idRows.length; i++) {

                    idRows[i] = LettragePanel.this.ecriturePanel.getListe().idFromIndex(selectedRows[i]);

                }

                LettragePanel.this.model.updateSelection(idRows);

                LettragePanel.this.warningPanel.setVisible((LettragePanel.this.codeLettrage.getText().trim().length() == 0));

                LettragePanel.this.warningSolde.setVisible(LettragePanel.this.model.getSoldeSelection() != 0);

            }

        });

        // Gestion du code

        this.codeLettrage.getDocument().addDocumentListener(new SimpleDocumentListener() {

            @Override

            public void update(DocumentEvent e) {

                // TODO Auto-generated method stub

                LettragePanel.this.warningPanel.setVisible((LettragePanel.this.codeLettrage.getText().trim().length() == 0));

                LettragePanel.this.buttonLettrer.setEnabled((LettragePanel.this.codeLettrage.getText().trim().length() != 0));

            }

        });

        changeListRequest();

        this.warningPanel.setVisible((this.codeLettrage.getText().trim().length() == 0));

        this.buttonLettrer.setEnabled((this.codeLettrage.getText().trim().length() != 0));

    }

    private String getNextCodeLettragePartiel() {

        return Configuration.getInstance().getDirectory().getElement(NumerotationAutoSQLElement.class).getNextCodeLettragePartiel();

    }

    private Where createTierWhere(final SQLElement eltCpt) {

        String function = "REGEXP";

        if (eltCpt.getTable().getBase().getServer().getSQLSystem() == SQLSystem.POSTGRESQL) {

            function = "~";

        }

        Where w = new Where(eltCpt.getTable().getField("NUMERO"), function, "^4.*$");

        return w;

    }

    private String getNextCodeLettrage() {

        return Configuration.getInstance().getDirectory().getElement(NumerotationAutoSQLElement.class).getNextCodeLettrage();

    }

    /* Menu clic Droit */

    private void addActionMenuDroit() {

        // JPopupMenu menu = new JPopupMenu();

        PredicateRowAction action = new PredicateRowAction(new AbstractAction() {

            public void actionPerformed(ActionEvent e) {

                SQLRow rowEcr = LettragePanel.this.ecriturePanel.getListe().fetchSelectedRow();

                MouvementSQLElement.showSource(rowEcr.getInt("ID_MOUVEMENT"));

            }

        }, false, "financing.accouning.entries.source.show");

        action.setPredicate(IListeEvent.getSingleSelectionPredicate());

        this.ecriturePanel.getListe().addIListeAction(action);

        final AbstractAction abstractAction = new AbstractAction() {

            public void actionPerformed(ActionEvent e) {

                try {

                    int[] rowIndex = LettragePanel.this.ecriturePanel.getListe().getJTable().getSelectedRows();

                    actionLettrage(rowIndex, false);

                } catch (SQLException e1) {

                    ExceptionHandler.handle("erreur de lettrage", e1);

                }

            }

        };

        PredicateRowAction actionLettre = new PredicateRowAction(abstractAction, false, "financing.accouning.entries.match");

        actionLettre.setPredicate(IListeEvent.getNonEmptySelectionPredicate());

        this.ecriturePanel.getListe().addIListeAction(actionLettre);

        final AbstractAction abstractActionPartiel = new AbstractAction("Lettrage Partiel") {

            public void actionPerformed(ActionEvent e) {

                try {

                    int[] rowIndex = LettragePanel.this.ecriturePanel.getListe().getJTable().getSelectedRows();

                    actionLettrage(rowIndex, true);

                } catch (SQLException e1) {

                    ExceptionHandler.handle("erreur de lettrage", e1);

                }

            }

        };

        PredicateRowAction actionLettreP = new PredicateRowAction(abstractActionPartiel, false, "financing.accouning.entries.match.partial");

        actionLettreP.setPredicate(IListeEvent.getNonEmptySelectionPredicate());

        this.ecriturePanel.getListe().addIListeAction(actionLettreP);

        // }

        this.codeLettrage.getDocument().addDocumentListener(new SimpleDocumentListener() {

            @Override

            public void update(DocumentEvent e) {

                abstractAction.setEnabled(LettragePanel.this.codeLettrage.getText().trim().length() > 0);

            }

        });

        this.ecriturePanel.getListe().addRowAction(new AbstractAction("Délettrer") {

            public void actionPerformed(ActionEvent e) {

                int[] rowIndex = LettragePanel.this.ecriturePanel.getListe().getJTable().getSelectedRows();

                actionDelettrage(rowIndex, false);

            }

        }, "financing.accouning.entries.unmatch");

        this.ecriturePanel.getListe().addRowAction(new AbstractAction("Délettrer partiel") {

            public void actionPerformed(ActionEvent e) {

                int[] rowIndex = LettragePanel.this.ecriturePanel.getListe().getJTable().getSelectedRows();

                actionDelettrage(rowIndex, true);

            }

        }, "financing.accouning.entries.unmatch.partial");

    }

    /* Panel Warning no numero releve */

    private void createPanelWarning() {

        this.warningPanel = new JPanel();

        this.warningPanel.setLayout(new GridBagLayout());

        // this.warningPanel.setBorder(BorderFactory.createTitledBorder("Warning"));

        GridBagConstraints c = new GridBagConstraints();

        c.anchor = GridBagConstraints.WEST;

        c.fill = GridBagConstraints.NONE;

        c.gridheight = 1;

        c.gridwidth = 1;

        c.gridx = 0;

        c.gridy = 0;

        c.weightx = 0;

        c.weighty = 0;

        final JLabel warningNoCodeImg = new JLabelWarning();

        warningNoCodeImg.setHorizontalAlignment(SwingConstants.RIGHT);

        this.warningPanel.add(warningNoCodeImg, c);

        final JLabel warningNoCodeText = new JLabel("Impossible de lettrer tant que le code de lettrage n'est pas saisi!");

        c.gridx++;

        this.warningPanel.add(warningNoCodeText, c);

    }

    /* Panel Warning solde invalide */

    private void createPanelWarningSolde() {

        this.warningSolde = new JPanel();

        this.warningSolde.setLayout(new GridBagLayout());

        // this.warningPanel.setBorder(BorderFactory.createTitledBorder("Warning"));

        GridBagConstraints c = new GridBagConstraints();

        c.anchor = GridBagConstraints.WEST;

        c.fill = GridBagConstraints.NONE;

        c.gridheight = 1;

        c.gridwidth = 1;

        c.gridx = 0;

        c.gridy = 0;

        c.weightx = 0;

        c.weighty = 0;

        final JLabel warningNoCodeImg = new JLabelWarning();

        warningNoCodeImg.setHorizontalAlignment(SwingConstants.RIGHT);

        this.warningSolde.add(warningNoCodeImg, c);

        final JLabel warningNoCodeText = new JLabel("Impossible de lettrer tant que le solde sélectionné n'est pas nul!");

        c.gridx++;

        this.warningSolde.add(warningNoCodeText, c);

    }

    // Lettre la ligne passée en parametre

    private void actionLettrage(int[] rowIndex, boolean partiel) throws SQLException {

        String codeLettre;

        if (partiel) {

            codeLettre = this.codeLettragePartiel.getText().trim();

        } else {

            codeLettre = this.codeLettrage.getText().trim();

        }

        // FIXME : transaction

        List<SQLRow> rowsSelected = new ArrayList<SQLRow>(rowIndex.length);

        long solde = 0;

        for (int i = 0; i < rowIndex.length; i++) {

            int id = this.ecriturePanel.getListe().idFromIndex(rowIndex[i]);

            SQLRow row = this.tableEcr.getRow(id);

            rowsSelected.add(row);

            solde += ((Long) row.getObject("DEBIT")).longValue();

            solde -= ((Long) row.getObject("CREDIT")).longValue();

        }

        if (partiel || solde == 0) {

            for (SQLRow row2 : rowsSelected) {

                SQLRowValues rowVals = new SQLRowValues(this.tableEcr);

                // Lettrage

                // On lettre ou relettre la ligne avec le code saisi

                if (codeLettre.length() > 0) {

                    // Si la ligne est en brouillard on valide le mouvement associé

                    if (this.boxValidEcriture.isSelected() && (!row2.getBoolean("VALIDE"))) {

                        EcritureSQLElement.validationEcritures(row2.getInt("ID_MOUVEMENT"));

                    }

                    if (partiel) {

                        rowVals.put("LETTRAGE_PARTIEL", codeLettre);

                    } else {

                        rowVals.put("LETTRAGE", codeLettre);

                        rowVals.put("DATE_LETTRAGE", this.dateLettrage.getDate());

                    }

                    try {

                        rowVals.update(row2.getID());

                    } catch (SQLException e1) {

                        e1.printStackTrace();

                    }

                }

            }

            // Mise à jour du code de lettrage

            SQLElement elt = Configuration.getInstance().getDirectory().getElement("NUMEROTATION_AUTO");

            SQLRowValues rowVals = elt.getTable().getRow(2).createEmptyUpdateRow();

            if (partiel) {

                rowVals.put("CODE_LETTRAGE_PARTIEL", codeLettre);

            } else {

                rowVals.put("CODE_LETTRAGE", codeLettre);

            }

            try {

                rowVals.update();

            } catch (SQLException e) {

                e.printStackTrace();

            }

            this.codeLettrage.setText(NumerotationAutoSQLElement.getNextCodeLettrage());

            if (partiel) {

                this.codeLettragePartiel.setText(getNextCodeLettragePartiel());

            } else {

                this.codeLettrage.setText(getNextCodeLettrage());

            }

            this.model.updateTotauxCompte();

        }

    }

    protected MaskFormatter createFormatter() {

        MaskFormatter formatter = null;

        try {

            formatter = new MaskFormatter("UUU");

        } catch (ParseException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return formatter;

    }

    // Pointe la ligne passée en parametre

    private void actionDelettrage(int[] rowIndex, boolean partiel) {

        List<SQLRow> rowsSelected = new ArrayList<SQLRow>(rowIndex.length);

        long solde = 0;

        for (int i = 0; i < rowIndex.length; i++) {

            int id = this.ecriturePanel.getListe().idFromIndex(rowIndex[i]);

            SQLRow row = this.tableEcr.getRow(id);

            rowsSelected.add(row);

            solde += ((Long) row.getObject("DEBIT")).longValue();

            solde -= ((Long) row.getObject("CREDIT")).longValue();

        }

        if (partiel || solde == 0) {

            for (SQLRow row : rowsSelected) {

                SQLRowValues rowVals = new SQLRowValues(this.tableEcr);

                if (partiel) {

                    // Dépointage

                    if (row.getString("LETTRAGE_PARTIEL").trim().length() != 0) {

                        rowVals.put("LETTRAGE_PARTIEL", "");

                        try {

                            rowVals.update(row.getID());

                        } catch (SQLException e1) {