OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 174 | Rev 180 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

/*
 * 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.generationEcritures;

import org.openconcerto.erp.config.ComptaPropsConfiguration;
import org.openconcerto.erp.core.common.element.BanqueSQLElement;
import org.openconcerto.erp.core.common.element.NumerotationAutoSQLElement;
import org.openconcerto.erp.core.finance.accounting.element.ComptePCESQLElement;
import org.openconcerto.erp.core.finance.accounting.element.JournalSQLElement;
import org.openconcerto.erp.core.finance.accounting.element.MouvementSQLElement;
import org.openconcerto.erp.core.finance.payment.element.ModeDeReglementSQLElement;
import org.openconcerto.erp.core.finance.payment.element.SEPAMandateSQLElement;
import org.openconcerto.erp.core.finance.payment.element.TypeReglementSQLElement;
import org.openconcerto.erp.model.PrixTTC;
import org.openconcerto.erp.preferences.GestionCommercialeGlobalPreferencePanel;
import org.openconcerto.sql.Configuration;
import org.openconcerto.sql.element.SQLElement;
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.SQLSelect;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.Where;
import org.openconcerto.sql.preferences.SQLPreferences;
import org.openconcerto.utils.cc.ITransformer;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class GenerationReglementVenteNG extends GenerationEcritures {

    private static final SQLTable tableMouvement = base.getTable("MOUVEMENT");
    private static final SQLTable tableEncaisse = base.getTable("ENCAISSER_MONTANT");
    private static final SQLTable tableEncaisseElt = base.getTable("ENCAISSER_MONTANT_ELEMENT");
    private static final SQLTable tableSaisieVenteFacture = base.getTable("SAISIE_VENTE_FACTURE");
    private static final SQLTable tablePrefCompte = base.getTable("PREFS_COMPTE");
    private static final SQLRow rowPrefsCompte = tablePrefCompte.getRow(2);

    public SQLRow ecrClient = null;

    public GenerationReglementVenteNG(String label, SQLRow rowClient, PrixTTC ttc, Date d, SQLRow modeReglement, SQLRow source, SQLRow mvtSource) throws SQLException {
        this(label, rowClient, ttc, d, modeReglement, source, mvtSource, true);
    }

    public GenerationReglementVenteNG(String label, SQLRow rowClient, PrixTTC ttc, Date d, SQLRow modeReglement, SQLRow source, SQLRow mvtSource, boolean createEncaisse) throws SQLException {
        this(label, rowClient, ttc, d, modeReglement, source, mvtSource, createEncaisse, false);
    }

    public GenerationReglementVenteNG(String label, SQLRow rowClient, PrixTTC ttc, Date d, SQLRow modeReglement, SQLRow source, SQLRow mvtSource, boolean createEncaisse, boolean avance)
            throws SQLException {
        this(label, rowClient, ttc, d, modeReglement, source, mvtSource, createEncaisse, avance, rowClient.getString("NOM"), null);
    }

    public GenerationReglementVenteNG(String label, SQLRow rowClient, PrixTTC ttc, Date d, SQLRow modeReglement, SQLRow source, SQLRow mvtSource, boolean createEncaisse, boolean avance, String tiers,
            SQLRowAccessor cptTiers) throws SQLException {

        SQLRow typeRegRow = modeReglement.getForeignRow("ID_TYPE_REGLEMENT");
        setRowAnalytiqueSource(source);
        // iniatilisation des valeurs de la map
        this.date = d;
        if (typeRegRow.getID() == TypeReglementSQLElement.CB && modeReglement.getObject("DATE") != null) {
            this.date = (Date) modeReglement.getObject("DATE");
        }

        // TODO Nommage des ecritures

        this.nom = label;

        this.putValue("DATE", this.date);
        this.putValue("NOM", this.nom);
        fillJournalBanqueFromRow(modeReglement);

        this.putValue("ID_MOUVEMENT", Integer.valueOf(this.idMvt));
        if (source.getTable().getName().equalsIgnoreCase("ENCAISSER_MONTANT")) {
            if (source.getBoolean("ACOMPTE")) {
                final String numeroDevis = source.asRow().getForeign("ID_DEVIS").getString("NUMERO");
                this.idMvt = getNewMouvement(source.getTable().getName(), source.getID(), 1, numeroDevis);
                this.putValue("NOM_PIECE", numeroDevis);
            } else {
                List<SQLRow> l = source.getReferentRows(source.getTable().getTable("ENCAISSER_MONTANT_ELEMENT"));
                for (SQLRow sqlRow : l) {
                    SQLRow mvtEch = sqlRow.getForeignRow("ID_MOUVEMENT_ECHEANCE");
                    if (mvtEch.getID() != mvtSource.getID()) {
                        getNewMouvement(source.getTable().getName(), source.getID(), mvtEch.getID(), mvtEch.getInt("ID_PIECE"));
                    }
                }
            }
        }
        // si paiement comptant
        if ((!typeRegRow.getBoolean("ECHEANCE"))
                && ((modeReglement.getBoolean("COMPTANT")) || (!modeReglement.getBoolean("DATE_FACTURE") && (modeReglement.getInt("AJOURS") == 0 && modeReglement.getInt("LENJOUR") == 0)))) {

            SQLRow rowEncaisse = source;

            SQLRow rowEncaisseElt = null;
            // On cre un encaissement
            if (createEncaisse) {
                SQLRowValues rowVals = new SQLRowValues(tableEncaisse);
                rowVals.put("MONTANT", ttc.getLongValue());
                rowVals.put("ID_CLIENT", rowClient != null ? rowClient.getID() : null);
                rowVals.put("TIERS", tiers);
                rowVals.put("DATE", this.date);
                if (typeRegRow.getID() >= TypeReglementSQLElement.TRAITE) {
                    Calendar c2 = modeReglement.getDate("DATE_VIREMENT");
                    if (c2 != null) {
                        rowVals.put("DATE", c2.getTime());
                    }
                }
                SQLRowValues rowValsRegl = new SQLRowValues(modeReglement.asRowValues());
                SQLRow copy = rowValsRegl.insert();
                rowVals.put("ID_MODE_REGLEMENT", copy.getID());
                rowVals.put("NOM", label);
                rowEncaisse = rowVals.insert();
                SQLRowValues rowValsElt = new SQLRowValues(tableEncaisseElt);
                rowValsElt.put("MONTANT_REGLE", ttc.getLongValue());
                rowValsElt.put("ID_ENCAISSER_MONTANT", rowEncaisse.getID());
                rowEncaisseElt = rowValsElt.insert();

            }
            if (!source.getTable().getName().equalsIgnoreCase("ENCAISSER_MONTANT") || !source.getBoolean("ACOMPTE")) {
                this.idMvt = getNewMouvement(rowEncaisse.getTable().getName(), rowEncaisse.getID(), mvtSource.getID(), mvtSource.getInt("ID_PIECE"));
            }
            this.putValue("ID_MOUVEMENT", Integer.valueOf(this.idMvt));

            SQLRowValues rowVals = rowEncaisse.createEmptyUpdateRow();
            rowVals.put("ID_MOUVEMENT", this.idMvt);

            rowVals.update();

            if (rowEncaisseElt != null) {
                SQLRowValues rowVals2 = rowEncaisseElt.createEmptyUpdateRow();
                rowVals2.put("ID_MOUVEMENT_ECHEANCE", this.idMvt);
                rowVals2.update();
            }

            // Cheque
            if (typeRegRow.getID() == TypeReglementSQLElement.CHEQUE) {

                Date dateTmp = this.date;
                if (modeReglement.getObject("DATE") != null) {
                    dateTmp = modeReglement.getDate("DATE").getTime();
                }
                // On fixe la date du règlement de la facture à reception du cheque
                setDateReglement(source, this.date);

                Calendar c = modeReglement.getDate("DATE_DEPOT");
                if (c != null) {
                    paiementCheque(c.getTime(), source, ttc, rowClient, modeReglement, mvtSource.getTable().getRow(idMvt), avance, tiers, cptTiers);
                } else {
                    paiementCheque(this.date, source, ttc, rowClient, modeReglement, mvtSource.getTable().getRow(idMvt), avance, tiers, cptTiers);
                }

            } else {
                // On fixe la date du règlement de la facture
                if (typeRegRow.getID() >= TypeReglementSQLElement.TRAITE) {
                    Calendar c2 = modeReglement.getDate("DATE_VIREMENT");
                    if (c2 == null) {
                        setDateReglement(source, this.date);
                    } else {
                        setDateReglement(source, c2.getTime());
                    }

                } else {
                    setDateReglement(source, this.date);
                }
                if (typeRegRow.getID() == TypeReglementSQLElement.ESPECE) {
                    this.putValue("ID_JOURNAL", JournalSQLElement.CAISSES);
                }

                if (typeRegRow.getID() == TypeReglementSQLElement.CB && this.rowPrefsCompte.getTable().contains("ID_JOURNAL_CB_ATTENTE")
                        && !this.rowPrefsCompte.isForeignEmpty("ID_JOURNAL_CB_ATTENTE")) {
                    this.putValue("ID_JOURNAL", this.rowPrefsCompte.getForeignID("ID_JOURNAL_CB_ATTENTE"));
                }

                int idCompteClient = cptTiers != null && !cptTiers.isUndefined() ? cptTiers.getID() : rowClient.getInt("ID_COMPTE_PCE");
                if (avance) {
                    idCompteClient = rowPrefsCompte.getInt("ID_COMPTE_PCE_AVANCE_CLIENT");
                    if (idCompteClient <= 1) {
                        idCompteClient = ComptePCESQLElement.getIdComptePceDefault("AvanceClients");
                    }
                } else {

                    // compte Clients

                    if (idCompteClient <= 1) {
                        idCompteClient = rowPrefsCompte.getInt("ID_COMPTE_PCE_CLIENT");
                        if (idCompteClient <= 1) {
                            idCompteClient = ComptePCESQLElement.getIdComptePceDefault("Clients");
                        }
                    }
                }
                this.putValue("ID_COMPTE_PCE", idCompteClient);
                this.putValue("DEBIT", Long.valueOf(0));
                this.putValue("CREDIT", Long.valueOf(ttc.getLongValue()));

                this.ecrClient = ajoutEcriture();

                // compte de reglement, caisse, cheque, ...
                if (typeRegRow.getID() == TypeReglementSQLElement.ESPECE) {
                    int idCompteRegl = typeRegRow.getInt("ID_COMPTE_PCE_CLIENT");
                    if (idCompteRegl <= 1) {
                        idCompteRegl = ComptePCESQLElement.getIdComptePceDefault("VenteEspece");
                    }

                    this.putValue("ID_COMPTE_PCE", Integer.valueOf(idCompteRegl));
                } else {
                    try {
                        fillCompteBanqueFromRow(modeReglement, "VenteCB", false);
                    } catch (Exception e) {
                        throw new SQLException(e);
                    }
                }
                if (typeRegRow.getID() == TypeReglementSQLElement.CB && this.rowPrefsCompte.getTable().contains("ID_COMPTE_PCE_CB_ATTENTE")
                        && !this.rowPrefsCompte.isForeignEmpty("ID_COMPTE_PCE_CB_ATTENTE")) {

                    this.putValue("ID_COMPTE_PCE", this.rowPrefsCompte.getForeignID("ID_COMPTE_PCE_CB_ATTENTE"));
                }

                this.putValue("DEBIT", Long.valueOf(ttc.getLongValue()));
                this.putValue("CREDIT", Long.valueOf(0));
                ajoutEcriture();

                List<Integer> pieceIDs = new ArrayList<Integer>();
                if (source.getTable().getName().equals("ENCAISSER_MONTANT")) {
                    List<SQLRow> l = source.getReferentRows(base.getTable("ENCAISSER_MONTANT_ELEMENT"));
                    for (SQLRow sqlRow : l) {
                        pieceIDs.add(sqlRow.getForeign("ID_MOUVEMENT_ECHEANCE").getForeignID("ID_PIECE"));
                    }
                } else {
                    pieceIDs.add(mvtSource.getForeignID("ID_PIECE"));
                }
                lettrageAuto(pieceIDs, d);
            }
        } else {

                Date dateEch = ModeDeReglementSQLElement.calculDate(modeReglement.getInt("AJOURS"), modeReglement.getInt("LENJOUR"), this.date);

                System.out.println("Echance client");

                // Ajout dans echeance
                final SQLTable tableEch = base.getTable("ECHEANCE_CLIENT");
                SQLRowValues valEcheance = new SQLRowValues(tableEch);

                this.idMvt = getNewMouvement("ECHEANCE_CLIENT", 1, mvtSource.getID(), mvtSource.getInt("ID_PIECE"));
                valEcheance.put("ID_MOUVEMENT", Integer.valueOf(this.idMvt));
                valEcheance.put("DATE", dateEch);
                valEcheance.put("MONTANT", Long.valueOf(ttc.getLongValue()));
                valEcheance.put("ID_CLIENT", rowClient == null ? null : rowClient.getID());
                valEcheance.put("TIERS", tiers);
                valEcheance.put("ID_COMPTE_PCE_TIERS", cptTiers == null || !cptTiers.isUndefined() ? null : cptTiers.getID());
                if (source.getTable().equals(tableSaisieVenteFacture)) {
                    valEcheance.put("ID_SAISIE_VENTE_FACTURE", source.getID());
                }
                if (source.getTable().getName().equals(valEcheance.getTable().getName())) {
                    valEcheance.put("ID_SAISIE_VENTE_FACTURE", source.getObject("ID_SAISIE_VENTE_FACTURE"));
                }
                if (modeReglement.getForeign("ID_TYPE_REGLEMENT").getBoolean("SEPA")) {
                    final String foreignBanqueFieldName = "ID_" + BanqueSQLElement.TABLENAME;
                    if (valEcheance.getTable().contains(foreignBanqueFieldName))
                        valEcheance.put(foreignBanqueFieldName, modeReglement.getInt(foreignBanqueFieldName));

                    valEcheance.put("ETS", modeReglement.getObject("ETS"));

                    if (!modeReglement.isForeignEmpty("ID_SEPA_MANDATE")) {
                        valEcheance.put("ID_SEPA_MANDATE", modeReglement.getForeignID("ID_SEPA_MANDATE"));
                    } else if (rowClient != null && !rowClient.isForeignEmpty("ID_SEPA_MANDATE_DEFAULT")) {
                        valEcheance.put("ID_SEPA_MANDATE", rowClient.getForeignID("ID_SEPA_MANDATE_DEFAULT"));
                    } else {
                        SEPAMandateSQLElement mandateElem = ComptaPropsConfiguration.getInstanceCompta().getDirectory().getElement(SEPAMandateSQLElement.class);
                        final String mandateID = mandateElem.generateMandateIdentification("Facturation", '0', true, true);
                        // TODO gestion si pas de client (ex : don ou cotisation association)
                        if (rowClient != null) {
                            final SQLRowValues newVals = mandateElem.createRecurrent(rowClient.getID(), mandateID, source.getDate("DATE").getTime());
                            SQLRow rowMandate = newVals.commit();
                            valEcheance.put("ID_SEPA_MANDATE", rowMandate.getID());
                            rowClient.createEmptyUpdateRow().put("ID_SEPA_MANDATE_DEFAULT", rowMandate.getID()).commit();
                        }
                    }
                }

                // ajout de l'ecriture
                SQLRow row = valEcheance.insert();
                SQLRowValues rowVals = new SQLRowValues(tableMouvement);
                rowVals.put("IDSOURCE", row.getID());
                rowVals.update(this.idMvt);

        }
    }

    private void setDateReglement(SQLRow source, Date d) throws SQLException {
        List<SQLRow> sources = new ArrayList<SQLRow>();
        if (source.getTable().getName().equalsIgnoreCase("ENCAISSER_MONTANT")) {

            List<SQLRow> rows = source.getReferentRows(source.getTable().getTable("ENCAISSER_MONTANT_ELEMENT"));
            for (SQLRow sqlRow : rows) {
                SQLRow rowEch = sqlRow.getForeignRow("ID_ECHEANCE_CLIENT");
                if (rowEch != null && rowEch.getID() > 1) {
                    SQLRow rowMvt = tableMouvement.getRow(MouvementSQLElement.getSourceId(rowEch.getInt("ID_MOUVEMENT")));
                    if (rowMvt.getString("SOURCE").equalsIgnoreCase("SAISIE_VENTE_FACTURE")) {
                        sources.add(tableSaisieVenteFacture.getRow(rowMvt.getInt("IDSOURCE")));
                    }
                }
            }

        } else {
            sources.add(source);
        }
        for (SQLRow sqlRow : sources) {
            if (sqlRow.getTable().getName().equalsIgnoreCase("SAISIE_VENTE_FACTURE")) {
                SQLRowValues rowValsUpdateVF = sqlRow.createEmptyUpdateRow();
                rowValsUpdateVF.put("DATE_REGLEMENT", new Timestamp(d.getTime()));
                rowValsUpdateVF.update();
            }
        }

    }

    public void doLettrageAuto(final SQLRowAccessor source, Date dateLettrage) {
        // A. On lettre les critures client (facture ET reglement)
        // A1. Recherche criture client de facturation

        final SQLRowValues g1 = new SQLRowValues(ecritureTable);
        g1.put("DEBIT", null);
        final SQLRowValuesListFetcher fetch1 = new SQLRowValuesListFetcher(g1);
        fetch1.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
            @Override
            public SQLSelect transformChecked(SQLSelect input) {
                input.setWhere(new Where(ecritureTable.getField("ID_MOUVEMENT"), "=", source.getForeignID("ID_MOUVEMENT")));
                input.andWhere(new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=", ecrClient.getForeignID("ID_COMPTE_PCE")));
                input.andWhere(new Where(ecritureTable.getField("JOURNAL_CODE"), "=", "VE"));
                return input;
            }

        });
        final List<SQLRowValues> rowsEcriture1 = fetch1.fetch();
        if (rowsEcriture1.size() != 1) {
            System.out.println("critures VE trouves. Erreur");
            return;
        }
        final SQLRowValues rEcriture1 = rowsEcriture1.get(0);
        System.out.println("Ecriture vente: " + rEcriture1.getID());
        System.out.println("Ecriture paiement: " + this.ecrClient);

        // Récupère lettrage
        String codeLettre = NumerotationAutoSQLElement.getNextCodeLettrage();

        // TODO: vérifier somme = 0

        // Met à  jour les 2 écritures
        SQLRowValues rowVals = new SQLRowValues(ecritureTable);
        rowVals.put("LETTRAGE", codeLettre);
        rowVals.put("DATE_LETTRAGE", dateLettrage);
        try {
            rowVals.update(rEcriture1.getID());
            rowVals.update(this.ecrClient.getID());
        } catch (SQLException e1) {
            e1.printStackTrace();
        }

        // Mise à  jour du code de lettrage
        SQLElement numElt = Configuration.getInstance().getDirectory().getElement("NUMEROTATION_AUTO");
        SQLRowValues rowVals1 = numElt.getTable().getRow(2).createEmptyUpdateRow();
        rowVals1.put("CODE_LETTRAGE", codeLettre);
        try {
            rowVals1.update();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void paiementCheque(Date dateEch, SQLRow source, PrixTTC ttc, SQLRow rowClient, SQLRow modeRegl, SQLRow mvtSource, boolean avance, String tiers, SQLRowAccessor cptTiers)
            throws SQLException {

        SQLRowValues valCheque = new SQLRowValues(base.getTable("CHEQUE_A_ENCAISSER"));
        SQLPreferences prefs = SQLPreferences.getMemCached(valCheque.getTable().getDBRoot());
        boolean createEcr = prefs.getBoolean(GestionCommercialeGlobalPreferencePanel.CREATE_ECR_CHQ, true);

        valCheque.put("ID_CLIENT", rowClient == null ? null : rowClient.getID());
        valCheque.put("ID_COMPTE_PCE_TIERS", cptTiers == null ? null : cptTiers.getID());
        valCheque.put("TIERS", tiers);
        valCheque.put("SANS_VALEUR_ENCAISSEMENT", !createEcr);
        final String foreignBanqueFieldName = "ID_" + BanqueSQLElement.TABLENAME;
        if (valCheque.getTable().contains(foreignBanqueFieldName))
            valCheque.put(foreignBanqueFieldName, modeRegl.getInt(foreignBanqueFieldName));

        valCheque.put("NUMERO", modeRegl.getObject("NUMERO"));
        valCheque.put("DATE", modeRegl.getObject("DATE"));
        valCheque.put("ETS", modeRegl.getObject("ETS"));
        valCheque.put("DATE_VENTE", this.date);
        this.idMvt = getNewMouvement("CHEQUE_A_ENCAISSER", 1, mvtSource.getID(), mvtSource.getInt("ID_PIECE"));
        valCheque.put("DATE_MIN_DEPOT", dateEch);
        valCheque.put("ID_MOUVEMENT", Integer.valueOf(this.idMvt));
        valCheque.put("MONTANT", Long.valueOf(ttc.getLongValue()));

        if (valCheque.getInvalid() == null) {
            // ajout de l'ecriture
            SQLRow row = valCheque.insert();
            SQLRowValues rowVals = new SQLRowValues(tableMouvement);
            rowVals.put("IDSOURCE", row.getID());
            rowVals.update(this.idMvt);
        }

        if (createEcr) {
            int idCompteClient = cptTiers == null || cptTiers.isUndefined() ? rowClient.getInt("ID_COMPTE_PCE") : cptTiers.getID();
            if (avance) {
                idCompteClient = rowPrefsCompte.getInt("ID_COMPTE_PCE_AVANCE_CLIENT");
                if (idCompteClient <= 1) {
                    idCompteClient = ComptePCESQLElement.getIdComptePceDefault("AvanceClients");
                }
            } else {

                // compte Clients

                if (idCompteClient <= 1) {
                    idCompteClient = rowPrefsCompte.getInt("ID_COMPTE_PCE_CLIENT");
                    if (idCompteClient <= 1) {
                        idCompteClient = ComptePCESQLElement.getIdComptePceDefault("Clients");
                    }
                }
            }

            int idJournal = JournalSQLElement.BANQUES;
            if (rowPrefsCompte.getObject("ID_JOURNAL_VALEUR_ENCAISSEMENT") != null && !rowPrefsCompte.isForeignEmpty("ID_JOURNAL_VALEUR_ENCAISSEMENT")) {
                idJournal = rowPrefsCompte.getForeignID("ID_JOURNAL_VALEUR_ENCAISSEMENT");
            }

            this.putValue("ID_JOURNAL", idJournal);
            this.putValue("ID_COMPTE_PCE", idCompteClient);
            this.putValue("DEBIT", Long.valueOf(0));
            this.putValue("CREDIT", Long.valueOf(ttc.getLongValue()));

            this.ecrClient = ajoutEcriture();

            // compte de reglement, caisse, cheque, ...
            int idCompteRegl = rowPrefsCompte.getInt("ID_COMPTE_PCE_VALEUR_ENCAISSEMENT");
            if (idCompteRegl <= 1) {
                idCompteRegl = ComptePCESQLElement.getIdComptePceDefault("ValeurEncaissement");
            }
            this.putValue("ID_COMPTE_PCE", Integer.valueOf(idCompteRegl));
            this.putValue("DEBIT", Long.valueOf(ttc.getLongValue()));
            this.putValue("CREDIT", Long.valueOf(0));

            ajoutEcriture();
        }

    }
}