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

/*

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

 *

 * Copyright 2011 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.element;

import org.openconcerto.erp.config.ComptaPropsConfiguration;

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

import org.openconcerto.sql.Configuration;

import org.openconcerto.sql.element.BaseSQLComponent;

import org.openconcerto.sql.element.SQLComponent;

import org.openconcerto.sql.element.TreesOfSQLRows;

import org.openconcerto.sql.model.DBRoot;

import org.openconcerto.sql.model.DBSystemRoot;

import org.openconcerto.sql.model.SQLBackgroundTableCache;

import org.openconcerto.sql.model.SQLBackgroundTableCacheItem;

import org.openconcerto.sql.model.SQLBase;

import org.openconcerto.sql.model.SQLField;

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.SQLTable;

import org.openconcerto.sql.model.Where;

import org.openconcerto.sql.request.ComboSQLRequest;

import org.openconcerto.sql.request.UpdateBuilder;

import org.openconcerto.sql.sqlobject.JUniqueTextField;

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.component.ITextArea;

import org.openconcerto.utils.ExceptionHandler;

import org.openconcerto.utils.checks.ValidState;

import org.openconcerto.utils.text.SimpleDocumentListener;

import java.awt.GridBagConstraints;

import java.awt.GridBagLayout;

import java.awt.event.ActionEvent;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.regex.Pattern;

import javax.swing.AbstractAction;

import javax.swing.JCheckBox;

import javax.swing.JLabel;

import javax.swing.JOptionPane;

import javax.swing.JTextField;

import javax.swing.event.DocumentEvent;

import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class ComptePCESQLElement extends ComptaSQLConfElement {

    private static Map<String, String> mapCompteDefault = new HashMap<String, String>();

    static {

        // "Fournisseurs"

        // "Achats"

        // "Clients"

        // "VentesProduits"

        // "VentesServices"

        // "TVACollectee"

        // "TVADeductible"

        // "PayeAcompte"

        // "PayeReglementAcompte"

        // "PayeRemunerationPersonnel"

        // "PayeReglement"

        // "AchatCheque"

        // "AchatCB"

        // "AchatTraite"

        // "AchatEspece"

        // "VenteCheque"

        // "VenteCB"

        // "VenteTraite"

        // "VenteEspece"

        // "TVAIntraComm"

        // "TVAImmo"

        // "BilanOuverture"

        // "BilanFermeture"

        // "Resultat"

        // "AchatsIntra"

        // "ResultatPerte"

        // "Factor"

        // "PortVenteSoumisTVA"

        // "PortVenteNonSoumisTVA"

        // "AvanceClients"

        // "ValeurEncaissement"

        mapCompteDefault.put("FOURNISSEURS", "40");

        mapCompteDefault.put("ACHATS", "60");

        mapCompteDefault.put("CLIENTS", "41");

        mapCompteDefault.put("VENTESPRODUITS", "701");

        mapCompteDefault.put("VENTESSERVICES", "706");

        mapCompteDefault.put("TVACOLLECTEE", "4457");

        mapCompteDefault.put("TVADEDUCTIBLE", "4456");

        mapCompteDefault.put("PAYEACOMPTE", "425");

        mapCompteDefault.put("PAYEREGLEMENTACOMPTE", "511");

        mapCompteDefault.put("PAYEREMUNERATIONPERSONNEL", "641");

        mapCompteDefault.put("PAYEREGLEMENT", "421");

        mapCompteDefault.put("ACHATCHEQUE", "512");

        mapCompteDefault.put("ACHATCB", "512");

        mapCompteDefault.put("ACHATTRAITE", "512");

        mapCompteDefault.put("ACHATESPECE", "53");

        mapCompteDefault.put("VENTECHEQUE", "512");

        mapCompteDefault.put("VENTECB", "512");

        mapCompteDefault.put("VENTETRAITE", "512");

        mapCompteDefault.put("VENTEESPECE", "53");

        mapCompteDefault.put("TVAINTRACOMM", "4452");

        mapCompteDefault.put("TVAIMMO", "445620");

        mapCompteDefault.put("BILANOUVERTURE", "890");

        mapCompteDefault.put("BILANFERMETURE", "891");

        mapCompteDefault.put("RESULTAT", "120");

        mapCompteDefault.put("ACHATSINTRA", "607120");

        mapCompteDefault.put("RESULTATPERTE", "129");

        mapCompteDefault.put("FACTOR", "467000");

        mapCompteDefault.put("PORTVENTESOUMISTVA", "708510");

        mapCompteDefault.put("PORTVENTENONSOUMISTVA", "708520");

        mapCompteDefault.put("AVANCECLIENTS", "4191");

        mapCompteDefault.put("VALEURENCAISSEMENT", "511");

    }

    public ComptePCESQLElement() {

        super("COMPTE_PCE", "un compte", "comptes");

        PredicateRowAction action = new PredicateRowAction(new AbstractAction("Consulter le compte") {

            public void actionPerformed(ActionEvent e) {

                final SQLRowAccessor row = IListe.get(e).getSelectedRow();

                ((EcritureSQLElement) getDirectory().getElement("ECRITURE")).consultationCompte(row);

                // ConsultationCompteFrame f = new ConsultationCompteFrame(table.getValueAt(row,

                // 0).toString());

            }

        }, true);

        action.setPredicate(IListeEvent.getSingleSelectionPredicate());

        getRowActions().add(action);

    }

    @Override

    protected void _initComboRequest(ComboSQLRequest req) {

        super._initComboRequest(req);

        req.setWhere(new Where(getTable().getField("OBSOLETE"), "=", Boolean.FALSE));

        // Ne pas écraser l'ordre de la base (equals, startwith, etc..)

        req.setItemsOrder(null);

    }

    protected List<String> getListFields() {

        final List<String> list = new ArrayList<String>(3);

        list.add("NUMERO");

        list.add("NOM");

        list.add("INFOS");

        list.add("OBSOLETE");

        return list;

    }

    protected List<String> getComboFields() {

        final List<String> list = new ArrayList<String>(2);

        list.add("NUMERO");

        list.add("NOM");

        return list;

    }

    public SQLComponent createComponent() {

        return new BaseSQLComponent(this) {

            // private boolean isCompteValid = false;

            private ValidState compteNumeroValidState = ValidState.getTrueInstance();

            private JUniqueTextField textNumero = new JUniqueTextField(7);

            @Override

            public void update() {

                final int id = getSelectedID();

                super.update();

                final DBSystemRoot sysRoot = getTable().getDBSystemRoot();

                final SQLTable ecrT = sysRoot.getGraph().findReferentTable(getTable(), "ECRITURE");

                final UpdateBuilder updateBuilder = new UpdateBuilder(ecrT);

                updateBuilder.addForwardVirtualJoin(getTable(), "ID_COMPTE_PCE");

                updateBuilder.setFromVirtualJoinField("COMPTE_NUMERO", getTable().getAlias(), "NUMERO");

                updateBuilder.setFromVirtualJoinField("COMPTE_NOM", getTable().getAlias(), "NOM");

                updateBuilder.setWhere(new Where(ecrT.getField("ID_COMPTE_PCE"), "=", id));

                sysRoot.getDataSource().execute(updateBuilder.asString());

            }

            public void addViews() {

                this.setLayout(new GridBagLayout());

                final GridBagConstraints c = new DefaultGridBagConstraints();

                // Numero

                JLabel labelNumero = new JLabel("Numéro ");

                this.add(labelNumero, c);

                c.gridx++;

                c.weightx = 0;

                c.weightx = 0.5;

                this.add(this.textNumero, c);

                this.textNumero.getTextField().getDocument().addDocumentListener(new SimpleDocumentListener() {

                    @Override

                    public void update(DocumentEvent e) {

                        // On verifie que le numero est correct

                        compteNumeroValidState = getCompteNumeroValidState(textNumero.getText());

                        fireValidChange();

                    }

                });

                /*

                 * textNumero.addKeyListener(new KeyAdapter(){

                 *

                 * public void keyPressed(KeyEvent e) { } });

                 */

                // Libellé

                JLabel labelNom = new JLabel("Libellé ");

                c.gridx++;

                c.weightx = 0;

                this.add(labelNom, c);

                JTextField textNom = new JTextField(30);

                c.gridx++;

                c.weightx = 1;

                this.add(textNom, c);

                // c.gridy++;

                c.gridx++;

                c.weightx = 0;

                JCheckBox checkRacine = new JCheckBox("Racine");

                this.add(checkRacine, c);

                c.gridy++;

                c.gridx++;

                c.weightx = 0;

                JCheckBox checkObs = new JCheckBox("Obsolète");

                this.add(checkObs, c);

                // Infos

                JLabel labelInfos = new JLabel(getLabelFor("INFOS"));

                c.gridy++;

                c.gridx = 0;

                c.weightx = 0;

                this.add(labelInfos, c);

                ITextArea textInfos = new ITextArea();

                c.gridx++;

                c.weightx = 1;

                c.weighty = 1;

                c.gridwidth = GridBagConstraints.REMAINDER;

                c.gridheight = GridBagConstraints.REMAINDER;

                c.fill = GridBagConstraints.BOTH;

                this.add(textInfos, c);

                this.addRequiredSQLObject(this.textNumero, "NUMERO");

                this.addRequiredSQLObject(textNom, "NOM");

                this.addSQLObject(textInfos, "INFOS");

                this.addSQLObject(checkRacine, "RACINE");

                this.addSQLObject(checkObs, "OBSOLETE");

            }

            @Override

            public synchronized ValidState getValidState() {

                return super.getValidState().and(this.compteNumeroValidState);

            }

        };

    }

    public ValidState getCompteNumeroValidState(String text) {

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

            if ((text.trim().charAt(0) < '1') || (text.trim().charAt(0) > '8')) {

                // System.err.println("Numero de compte incorrect");

                return ValidState.create(false, "Le numéro de compte ne commence pas par un chiffre entre 1 et 8");

            } else if (text.endsWith(" ")) {

                return ValidState.create(false, "Le numéro de compte ne doit pas se terminer par un espace");

            } else {

                Pattern p = Pattern.compile("^\\d(\\w)+$");

                return ValidState.create(p.matcher(text).matches(), "Le numéro de compte n'est pas correct.");

            }

        }

        return ValidState.getTrueInstance();

    }

    @Override

    protected void archive(TreesOfSQLRows trees, boolean cutLinks) throws SQLException {

        for (SQLRow row : trees.getRows()) {

            // on verifie qu'aucune ecriture n'est asssociée à ce compte

            SQLBase base = getTable().getBase();

            SQLTable ecritureTable = base.getTable("ECRITURE");

            SQLSelect selEcr = new SQLSelect();

            selEcr.addSelect(ecritureTable.getField("ID_COMPTE_PCE"));

            selEcr.setWhere(new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=", row.getID()));

            String reqEcriture = selEcr.asString();

            Object obEcriture = base.getDataSource().execute(reqEcriture, new ArrayListHandler());

            List myListEcriture = (List) obEcriture;

            if (myListEcriture.size() != 0) {

                JOptionPane.showMessageDialog(null, "Impossible de supprimer un compte mouvementé!");

            } else {

                super.archive(new TreesOfSQLRows(this, row), cutLinks);

            }

        }

    }

    public static int getId(String numero) {

        return getId(numero, "Création automatique");

    }

    public static SQLRow getRow(String numero, String nom) {

        return getRow(numero, nom, ((ComptaPropsConfiguration) Configuration.getInstance()).getRootSociete());

    }

    public static SQLRow getRow(String numero, String nom, DBRoot root) {

        SQLTable compteTable = root.getTable("COMPTE_PCE");

        final SQLBackgroundTableCacheItem cacheForTable = SQLBackgroundTableCache.getInstance().getCacheForTable(compteTable);

        if (numero == null) {

            throw new IllegalArgumentException("null numero");

        }

        final SQLField field = compteTable.getField("NUMERO");

        if (field == null) {

            throw new IllegalArgumentException("no field NUMERO in table " + compteTable.getName());

        }

        numero = numero.trim();

        SQLRow res = cacheForTable.getFirstRowContains(numero, field);

        if (res != null) {

            return res;

        } else {

            // Verification en BD

            SQLSelect sel = new SQLSelect();

            sel.addSelectStar(compteTable);

            sel.setWhere(new Where(compteTable.getField("NUMERO"), "=", numero));

            List<SQLRow> resultInDb = SQLRowListRSH.execute(sel);

            if (!resultInDb.isEmpty()) {

                return cacheForTable.getRows().get(0);

            } else {

                SQLRowValues rowVals = new SQLRowValues(compteTable);

                rowVals.put("NUMERO", numero);

                rowVals.put("NOM", nom);

                try {

                    return rowVals.insert();

                } catch (SQLException e) {

                    ExceptionHandler.handle("Erreur lors de la création du compte numéro : " + numero, e);

                    return null;

                }

            }

        }

    }

    /**

     * retourne l'id d'un compte en fonction de son numero, si le compte n'existe pas il sera créé

     * automatiquement

     *

     * @param numero du compte

     * @param nom nom du compte

     * @return id du compte

     */

    public static int getId(String numero, String nom) {

        return getRow(numero, nom).getID();

    }

    public static boolean isExist(String account) {

        if (account.trim().length() == 0) {

            return false;

        }

        final SQLTable tableAccount = Configuration.getInstance().getDirectory().getElement("COMPTE_PCE").getTable();

        final SQLBackgroundTableCacheItem item = SQLBackgroundTableCache.getInstance().getCacheForTable(tableAccount);

        return (item.getFirstRowContains(account, tableAccount.getField("NUMERO")) != null);

    }

    /**

     * @param name

     * @return Retourne le numero par défaut d'un compte

     * @throws IllegalArgumentException

     */

    public static String getComptePceDefault(final String name) throws IllegalArgumentException {

        // final SQLBase base = Configuration.getInstance().getBase();

        // final SQLTable tableDefault = base.getTable("COMPTE_PCE_DEFAULT");

        // final SQLSelect sel = new SQLSelect();

        // sel.addSelect(tableDefault.getField("NUMERO_DEFAULT"));

        //

        // sel.setWhere(Where.quote("UPPER(%n) = %s", tableDefault.getField("NOM"),

        // name.toUpperCase()));

        //

        // String numero = (String) base.getDataSource().executeScalar(sel.asString());

        String numero = mapCompteDefault.get(name.toUpperCase());

        if (numero == null) {

            throw new IllegalArgumentException("Impossible de trouver le compte PCE par défaut " + name);

        } else {

            return numero;

        }

    }

    public static void setComptePceDefault(final String name, final String numero) {

        mapCompteDefault.put(name, numero);

    }

    public static int getIdComptePceDefault(final String name) throws IllegalArgumentException {

        final String numeroDefault = getComptePceDefault(name);

        return getId(numeroDefault);

    }

    public static SQLRow getRowComptePceDefault(final String name) throws IllegalArgumentException {

        final String numeroDefault = getComptePceDefault(name);

        return getRow(numeroDefault, "création automatique");

    }

    @Override

    protected String createCode() {

        return createCodeOfPackage() + ".code.enterprise";

    }

}