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

        }

    }

}