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.sales.invoice.report;

import org.openconcerto.erp.config.ComptaPropsConfiguration;

import org.openconcerto.erp.generationDoc.AbstractListeSheetXml;

import org.openconcerto.erp.preferences.PrinterNXProps;

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.utils.cc.ITransformer;

import java.math.BigDecimal;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.Collections;

import java.util.Comparator;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class SituationCompteXmlSheet extends AbstractListeSheetXml {

    private final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");

    private SQLElement eltEch;

    private SQLElement eltVf;

    private ComptaPropsConfiguration conf;

    public SituationCompteXmlSheet(ComptaPropsConfiguration conf, SQLRow rowClient) {

        super(rowClient);

        this.printer = PrinterNXProps.getInstance().getStringProperty("BonPrinter");

        this.eltEch = conf.getDirectory().getElement("ECHEANCE_CLIENT");

        this.conf = conf;

    }

    @Override

    protected String getStoragePathP() {

        return "Autres";

    }

    public String getDefaultTemplateId() {

        return "SituationCompte";

    }

    private Date d;

    @Override

    public String getName() {

        if (this.d == null) {

            this.d = new Date();

        }

        return "SituationCompte-" + this.d.getTime();

    }

    protected void createListeValues() {

        // On récupére les échéances en cours

        final SQLTable echTable = eltEch.getTable();

        SQLRowValues rowVals = new SQLRowValues(echTable);

        rowVals.putNulls("DATE", "MONTANT");

        rowVals.putRowValues("ID_SAISIE_VENTE_FACTURE").putNulls("NUMERO", "NET_A_PAYER", "DATE", "NOM");

        Where w = new Where(echTable.getField("REGLE"), "=", Boolean.FALSE);

        w = w.and(new Where(echTable.getField("REG_COMPTA"), "=", Boolean.FALSE));

        w = w.and(new Where(echTable.getField("ID_CLIENT"), "=", row.getID()));

        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(rowVals);

        List<SQLRowValues> result = fetcher.fetch(w);

        Collections.sort(result, new Comparator<SQLRowValues>() {

            @Override

            public int compare(SQLRowValues o1, SQLRowValues o2) {

                return o1.getDate("DATE").compareTo(o2.getDate("DATE"));

            }

        });

        List<Map<String, Object>> listValues = new ArrayList<Map<String, Object>>();

        Map<Integer, String> styleValues = new HashMap<Integer, String>();

        BigDecimal totalRegle = BigDecimal.ZERO;

        BigDecimal totalEch = BigDecimal.ZERO;

        BigDecimal totalEchPasse = BigDecimal.ZERO;

        Calendar today = Calendar.getInstance();

        for (SQLRowValues sqlRow : result) {

            Map<String, Object> mValues = new HashMap<String, Object>();

            final Calendar dateEch = sqlRow.getDate("DATE");

            mValues.put("DATE_ECHEANCE", dateFormat.format(dateEch.getTime()));

            final BigDecimal montantDu = new BigDecimal(sqlRow.getLong("MONTANT"));

            mValues.put("DU", montantDu.movePointLeft(2));

            totalEch = totalEch.add(montantDu);

            SQLRowAccessor rowFact = sqlRow.getNonEmptyForeign("ID_SAISIE_VENTE_FACTURE");

            final BigDecimal regle;

            if (rowFact != null) {

                mValues.put("PIECE", rowFact.getString("NUMERO"));

                mValues.put("LIBELLE", rowFact.getString("NOM"));

                mValues.put("DATE", dateFormat.format(rowFact.getDate("DATE").getTime()));

                final BigDecimal montantFact = new BigDecimal(rowFact.getLong("NET_A_PAYER"));

                regle = montantFact.subtract(montantDu);

                mValues.put("DU", montantFact.movePointLeft(2));

                mValues.put("REGLE", montantFact.subtract(montantDu).movePointLeft(2));

                mValues.put("SOLDE", montantDu.movePointLeft(2));

            } else {

                regle = BigDecimal.ZERO;

                mValues.put("SOLDE", montantDu.movePointLeft(2));

            }

            if (dateEch.before(today)) {

                totalEchPasse = totalEchPasse.add(montantDu);

                styleValues.put(listValues.size(), "Normal");

            } else {

                styleValues.put(listValues.size(), "Titre 1");

            }

            totalRegle = totalRegle.add(regle);

            // SQLRow rowClient = sqlRow.getForeignRow("ID_CLIENT");

            listValues.add(mValues);

            // List<SQLRow> enc = sqlRow.getReferentRows(eltEncElt.getTable());

            //

            // for (SQLRow sqlRow2 : enc) {

            // Map<String, Object> mValuesEnc = new HashMap<String, Object>();

            // SQLRow rowEnc = sqlRow2.getForeignRow("ID_ENCAISSER_MONTANT");

            // SQLRow rowMdr = rowEnc.getForeignRow("ID_MODE_REGLEMENT");

            // mValuesEnc.put("NUMERO_FACTURE", "");

            // mValuesEnc.put("REFERENCE", rowMdr.getString("NOM"));

            // mValuesEnc.put("DATE", dateFormat.format(rowEnc.getDate("DATE").getTime()));

            // mValuesEnc.put("NOM_CLIENT", "");

            // mValuesEnc.put("CODE_CLIENT", "");

            // mValuesEnc.put("TELEPHONE", "");

            // mValuesEnc.put("MODE_REGLEMENT",

            // rowMdr.getForeignRow("ID_TYPE_REGLEMENT").getString("NOM"));

            // mValuesEnc.put("MONTANT",

            // GestionDevise.currencyToString(sqlRow2.getLong("MONTANT_REGLE")));

            // styleValues.put(listValues.size(), "Titre 1");

            // listValues.add(mValuesEnc);

            //

            // }

            // if (enc != null && enc.size() > 0) {

            // Map<String, Object> mValuesEnc = new HashMap<String, Object>();

            // mValuesEnc.put("DATE", dateFormat.format(sqlRow.getDate("DATE").getTime()));

            // mValuesEnc.put("MODE_REGLEMENT", "Restant à régler");

            // mValuesEnc.put("MONTANT", GestionDevise.currencyToString(sqlRow.getLong("MONTANT")));

            // styleValues.put(listValues.size(), "Titre 1");

            // listValues.add(mValuesEnc);

            // }

        }

        // Avoir

        final SQLTable avoirTable = echTable.getTable("AVOIR_CLIENT");

        SQLRowValues rowValsAvoir = new SQLRowValues(avoirTable);

        rowValsAvoir.putNulls("NUMERO", "MONTANT_TTC", "MONTANT_SOLDE", "MONTANT_RESTANT", "DATE", "NOM");

        Where wA = new Where(avoirTable.getField("SOLDE"), "=", Boolean.FALSE);

        wA = wA.and(new Where(avoirTable.getField("ID_CLIENT"), "=", row.getID()));

        wA = wA.and(new Where(avoirTable.getField("MONTANT_RESTANT"), ">", 0));

        SQLRowValuesListFetcher fetcherA = SQLRowValuesListFetcher.create(rowValsAvoir);

        List<SQLRowValues> resultA = fetcherA.fetch(wA);

        Collections.sort(resultA, new Comparator<SQLRowValues>() {

            @Override

            public int compare(SQLRowValues o1, SQLRowValues o2) {

                return o1.getDate("DATE").compareTo(o2.getDate("DATE"));

            }

        });

        if (!resultA.isEmpty()) {

            listValues.add(new HashMap<String, Object>());

        }

        for (SQLRowValues sqlRow : resultA) {

            Map<String, Object> mValues = new HashMap<String, Object>();

            final Calendar dateEch = sqlRow.getDate("DATE");

            mValues.put("DATE_ECHEANCE", dateFormat.format(dateEch.getTime()));

            final BigDecimal montantDu = new BigDecimal(sqlRow.getLong("MONTANT_RESTANT")).movePointLeft(2).negate();

            mValues.put("DU", montantDu);

            totalEch = totalEch.add(montantDu.movePointRight(2));

            totalEchPasse = totalEchPasse.add(montantDu.movePointRight(2));

            mValues.put("PIECE", sqlRow.getString("NUMERO"));

            mValues.put("LIBELLE", sqlRow.getString("NOM"));

            mValues.put("DATE", dateFormat.format(sqlRow.getDate("DATE").getTime()));

            styleValues.put(listValues.size(), "Normal");

            listValues.add(mValues);

        }

        SQLRow rowSoc = this.conf.getRowSociete();

        SQLRow adrSoc = rowSoc.getForeign("ID_ADRESSE_COMMON");

        Map<String, Object> sheetVals = new HashMap<String, Object>();

        sheetVals.put("TOTAL_REGLE", totalRegle.movePointLeft(2));

        sheetVals.put("TOTAL_ECHEANCE", totalEch.movePointLeft(2));

        sheetVals.put("TOTAL_FUTUR", totalEch.subtract(totalEchPasse).movePointLeft(2));

        sheetVals.put("TOTAL_PASSE", totalEchPasse.movePointLeft(2));

        sheetVals.put("NOM_CLIENT", row.getString("NOM"));

        sheetVals.put("DATE", dateFormat.format(new Date()));

        sheetVals.put("CODE_CLIENT", row.getString("CODE"));

        sheetVals.put("TELEPHONE", row.getString("TEL"));

        SQLRow rowAdrClient = row.getForeign("ID_ADRESSE");

        sheetVals.put("ADRESSE", rowAdrClient.getString("RUE"));

        sheetVals.put("VILLE", rowAdrClient.getString("CODE_POSTAL") + " " + rowAdrClient.getString("VILLE"));

        sheetVals.put("SOCIETE_NOM", rowSoc.getString("NOM"));

        sheetVals.put("SOCIETE_TEL", rowSoc.getString("NUM_TEL"));

        sheetVals.put("SOCIETE_SIRET", rowSoc.getString("NUM_SIRET"));

        sheetVals.put("SOCIETE_APE", rowSoc.getString("NUM_APE"));

        sheetVals.put("SOCIETE_NII", rowSoc.getString("NUM_NII"));

        sheetVals.put("SOCIETE_BIC", rowSoc.getString("BIC"));

        sheetVals.put("SOCIETE_IBAN", rowSoc.getString("IBAN"));

        sheetVals.put("SOCIETE_MAIL", rowSoc.getString("MAIL"));

        sheetVals.put("SOCIETE_ADRESSE", adrSoc.getString("RUE"));

        sheetVals.put("SOCIETE_VILLE", adrSoc.getString("CODE_POSTAL") + " " + adrSoc.getString("VILLE"));

        this.mapAllSheetValues.put(0, sheetVals);

        this.listAllSheetValues.put(0, listValues);

        this.styleAllSheetValues.put(0, styleValues);

    }

}