WebSVN - OpenConcerto - Rev 182 - /trunk/OpenConcerto/src/org/openconcerto/erp/core/sales/invoice/report/SituationCompteXmlSheet.java

/*
 * 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);

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

svn://code.openconcerto.org/openconcerto

(root)/trunk/OpenConcerto/src/org/openconcerto/erp/core/sales/invoice/report/SituationCompteXmlSheet.java - Rev 182
