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.finance.accounting.report;

import org.openconcerto.erp.config.ComptaPropsConfiguration;
import org.openconcerto.erp.core.finance.accounting.element.ComptePCESQLElement;
import org.openconcerto.erp.rights.ComptaUserRight;
import org.openconcerto.sql.element.SQLElementDirectory;
import org.openconcerto.sql.model.DBRoot;
import org.openconcerto.sql.model.SQLRow;
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.users.rights.UserRightsManager;
import org.openconcerto.utils.GestionDevise;
import org.openconcerto.utils.cc.ITransformer;

import java.awt.Color;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.pdfbox.pdmodel.PDPage;
import org.apache.pdfbox.pdmodel.PDPageContentStream;
import org.apache.pdfbox.pdmodel.common.PDRectangle;
import org.apache.pdfbox.pdmodel.font.PDType1Font;
import org.apache.pdfbox.pdmodel.graphics.image.PDImageXObject;

public class GrandLivrePDF {

    public static final int MODEALL = 1;
    public static final int MODELETTREE = 2;
    public static final int MODENONLETTREE_PERIODE = 3;
    public static final int MODENONLETTREE_ALL = 4;

    private final SQLTable tableEcriture;

    private final SQLTable tableMvt;
    private final SQLTable tableCompte;

    private final DateFormat dateFormatEcr = DateFormat.getDateInstance(DateFormat.SHORT);
    private final SQLRow rowSociete;

    private final Date dateDu;
    private final Date dateAu;
    private final String compteDeb;
    private final String compteEnd;
    private final int lettrage;
    private boolean cumul = false;
    private boolean excludeCompteSolde = true;
    private boolean centralClient = false;
    private boolean centralFourn = false;
    private int idJrnlExclude = -1;

    private final List<Map<String, Object>> recapSousTotaux = new ArrayList<>();

    private final SQLElementDirectory directory;

    public static final String TEMPLATE_ID = "GrandLivre";
    public static final String TEMPLATE_PROPERTY_NAME = "LocationGrandLivre";
    private static final int LINE_HEIGHT = 10;
    private static final float COL_1_SIZE = 34;
    private static final float COL_2_SIZE = 35;
    private static final float COL_3_SIZE = 35;
    private static final float COL_4_SIZE = 246;
    private static final float COL_5_SIZE = 50;
    private static final float COL_6_SIZE = COL_5_SIZE;
    private static final float COL_7_SIZE = 60;

    private static final float COL_1_X = 40;
    private static final float COL_2_X = COL_1_X + COL_1_SIZE;
    private static final float COL_3_X = COL_2_X + COL_2_SIZE;
    private static final float COL_4_X = COL_3_X + COL_3_SIZE;
    private static final float COL_5_X = COL_4_X + COL_4_SIZE;
    private static final float COL_6_X = COL_5_X + COL_5_SIZE;
    private static final float COL_7_X = COL_6_X + COL_6_SIZE;

    private final DecimalFormat decimalFormat = new DecimalFormat("#,##0.00", DecimalFormatSymbols.getInstance(Locale.FRANCE));

    public GrandLivrePDF(ComptaPropsConfiguration conf, Date du, Date au, String compteDep, String compteEnd, int lettrage, boolean cumul, boolean excludeCptSolde, boolean centralClient,
            boolean centralFourn, int idJrnlExclude) {
        this.directory = conf.getDirectory();
        this.rowSociete = conf.getRowSociete();
        final DBRoot b = conf.getRootSociete();
        this.tableEcriture = b.getTable("ECRITURE");
        this.tableMvt = b.getTable("MOUVEMENT");
        this.tableCompte = b.getTable("COMPTE_PCE");

        final Calendar cal = Calendar.getInstance();
        cal.setTime(au);
        this.idJrnlExclude = idJrnlExclude;
        this.dateAu = au;
        this.dateDu = du;
        this.compteDeb = compteDep.trim();
        this.compteEnd = compteEnd.trim();
        this.lettrage = lettrage;
        this.cumul = cumul;
        this.excludeCompteSolde = excludeCptSolde;
        this.centralClient = centralClient;
        this.centralFourn = centralFourn;

    }

    public void getGeneratedPDFFile(File f) throws IOException {

        final List<Integer> lCompteSolde;
        if (GrandLivrePDF.this.excludeCompteSolde) {
            lCompteSolde = getListeCompteSolde();
        } else {
            lCompteSolde = null;
        }
        final Map<Integer, Long> mapCumul = getCumulsAnterieur(GrandLivrePDF.this.dateDu, lCompteSolde);

        final List<SQLRowValues> list = fetchEcritures(lCompteSolde);

        long totalDebit = 0;
        long totalCredit = 0;
        long sousTotalCredit = 0;
        long sousTotalDebit = 0;

        String nomCpt = "";
        String numCpt = "";

        int y = 0;
        final String companyName = this.rowSociete.getString("TYPE") + " " + this.rowSociete.getString("NOM");
        final String title = "Compte : " + this.compteDeb + " à " + this.compteEnd + ". Période du " + this.dateFormatEcr.format(this.dateDu) + " au " + this.dateFormatEcr.format(this.dateAu);
        final Double doubleZero = Double.valueOf("0");
        try (PDDocument doc = new PDDocument()) {
            PDPageContentStream contents = null;
            final int size = list.size();
            for (int i = 0; i < size; i++) {
                if (y < 70) {
                    final PDPage page = new PDPage(PDRectangle.A4);
                    doc.addPage(page);
                    if (contents != null) {
                        contents.close();
                    }
                    contents = new PDPageContentStream(doc, page);
                    y = drawHeader(contents, companyName, title, true);
                }

                final SQLRowValues rowEcr = list.get(i);

                final int idCpt = rowEcr.getInt("ID_COMPTE_PCE");
                nomCpt = rowEcr.getString("COMPTE_NOM");
                numCpt = rowEcr.getString("COMPTE_NUMERO");

                // Cumuls antérieurs

                // Titre
                String prevNum = (i == 0 ? null : list.get(i - 1).getString("COMPTE_NUMERO"));
                if (prevNum == null || (!prevNum.equals(numCpt) && (!this.centralFourn || !(prevNum.startsWith("401") && numCpt.startsWith("401")))
                        && (!this.centralClient || !(prevNum.startsWith("411") && numCpt.startsWith("411"))))) {

                    // Si on centralise les comptes clients ou fournisseurs on affiche
                    // le compte 401 ou 411
                    if (this.centralClient && nomCpt.startsWith("411")) {
                        nomCpt = "411";
                        numCpt = "Centralisation clients";
                    }
                    if (this.centralFourn && nomCpt.startsWith("401")) {
                        nomCpt = "401";
                        numCpt = "Centralisation fournisseurs";
                    }

                    y = drawNomCompte(contents, y, numCpt, nomCpt);

                    if (this.cumul) {
                        Long longSolde = mapCumul.get(idCpt);
                        if (longSolde == null) {
                            longSolde = Long.valueOf(0);
                        }
                        long debitCumulAnt = 0;
                        long creditCumulAnt = 0;
                        if (longSolde > 0) {
                            debitCumulAnt = longSolde;
                        } else {
                            creditCumulAnt = -longSolde;
                        }
                        final Double debit = debitCumulAnt == 0 ? doubleZero : Double.valueOf(GestionDevise.currencyToString(debitCumulAnt, false));
                        final Double credit = creditCumulAnt == 0 ? doubleZero : Double.valueOf(GestionDevise.currencyToString(creditCumulAnt, false));
                        final Double solde = longSolde == 0 ? doubleZero : Double.valueOf(GestionDevise.currencyToString(longSolde, false));

                        totalCredit += creditCumulAnt;
                        totalDebit += debitCumulAnt;

                        sousTotalCredit += creditCumulAnt;
                        sousTotalDebit += debitCumulAnt;

                        y = drawCumul(contents, y, debit, credit, solde);
                    }

                }

                final long cred = rowEcr.getLong("CREDIT");
                final long deb = rowEcr.getLong("DEBIT");

                final Date dateEcriture = rowEcr.getDate("DATE").getTime();
                final String journal = rowEcr.getString("JOURNAL_CODE");
                final String mouvement = rowEcr.getForeign("ID_MOUVEMENT").getString("NUMERO");

                final String piece = rowEcr.getForeign("ID_MOUVEMENT").getForeign("ID_PIECE").getString("NOM");

                final String libelle = rowEcr.getString("NOM");
                final String codeLettrage = rowEcr.getString("LETTRAGE");
                final String codePointage = rowEcr.getString("POINTEE");

                final Calendar dateLettrage = rowEcr.getDate("DATE_LETTRAGE");
                final Calendar datePointage = rowEcr.getDate("DATE_POINTEE");

                totalCredit += cred;
                totalDebit += deb;
                sousTotalCredit += cred;
                sousTotalDebit += deb;
                final long ssolde = sousTotalDebit - sousTotalCredit;

                final Double debit = deb == 0 ? doubleZero : deb / 100D;
                final Double credit = cred == 0 ? doubleZero : cred / 100D;
                final Double solde = ssolde == 0 ? doubleZero : ssolde / 100D;

                y = drawLine(contents, y, dateEcriture, journal, mouvement, piece, libelle, codeLettrage, codePointage, dateLettrage, datePointage, debit, credit, solde);

                // si on change de compte alors on applique le style Titre 1
                String nextNum = (i >= size - 1 ? null : list.get(i + 1).getString("COMPTE_NUMERO"));

                if (nextNum == null || (!nextNum.equals(numCpt) && (!this.centralFourn || !(nextNum.startsWith("401") && numCpt.startsWith("401"))))
                        && (!this.centralClient || !(nextNum.startsWith("411") && numCpt.startsWith("411")))) {

                    if (this.centralClient && nomCpt.startsWith("411")) {
                        nomCpt = "411";
                        numCpt = "Centralisation clients";
                    }
                    if (this.centralFourn && nomCpt.startsWith("401")) {
                        nomCpt = "401";
                        numCpt = "Centralisation fournisseurs";
                    }

                    y = drawSousTotal(contents, y, numCpt, nomCpt, sousTotalDebit, sousTotalCredit);

                    sousTotalCredit = 0;
                    sousTotalDebit = 0;

                }

            }

            contents.close();

            // Recapitulatif

            PDPage page = new PDPage(PDRectangle.A4);
            doc.addPage(page);

            contents = new PDPageContentStream(doc, page);
            y = drawHeader(contents, companyName, title, false);

            for (final Map<String, Object> recap : this.recapSousTotaux) {
                if (y < 70) {
                    page = new PDPage(PDRectangle.A4);
                    doc.addPage(page);
                    if (contents != null) {
                        contents.close();
                    }
                    contents = new PDPageContentStream(doc, page);
                    y = drawHeader(contents, companyName, title, false);
                }
                final String numero = (String) recap.get("NUMERO");
                final String libelle = (String) recap.get("LIBELLE");
                final double debit = (long) recap.get("DEBIT") / 100D;
                final double credit = (long) recap.get("CREDIT") / 100D;
                final double solde = debit - credit;
                y = drawRecapLine(contents, y, numero, libelle, debit, credit, solde, false);

            }

            final Double debit = totalDebit == 0 ? 0 : totalDebit / 100D;
            final Double credit = totalCredit == 0 ? 0 : totalCredit / 100D;
            final Double solde = totalDebit - totalCredit == 0 ? 0 : (totalDebit - totalCredit) / 100D;
            y = drawRecapLine(contents, y, "", "GLOBAL", debit, credit, solde, true);

            contents.close();

            // Header avec numéro de page
            final InputStream sImage = this.getClass().getResourceAsStream("OpenConcerto_2000px.png");
            final ByteArrayOutputStream bOut = new ByteArrayOutputStream();
            final byte[] buf = new byte[8192];
            int length;
            while ((length = sImage.read(buf)) > 0) {
                bOut.write(buf, 0, length);
            }
            sImage.close();
            bOut.close();

            final float ratio = 20;
            final int pageCount = doc.getNumberOfPages();
            for (int index = 0; index < pageCount; index++) {
                final PDPage currentPage = doc.getPage(index);
                contents = new PDPageContentStream(doc, currentPage, PDPageContentStream.AppendMode.APPEND, true, true);
                if (index == 0 || index == pageCount - 1) {
                    final PDImageXObject pdImage = PDImageXObject.createFromByteArray(doc, bOut.toByteArray(), "openconcerto.png");
                    final float h = pdImage.getHeight() / ratio;
                    contents.drawImage(pdImage, 40, 10, pdImage.getWidth() / ratio, h);
                }
                contents.beginText();
                contents.setFont(PDType1Font.HELVETICA, 10);
                contents.newLineAtOffset(250, 20);
                contents.showText("Edition du " + this.dateFormatEcr.format(new Date()));
                contents.endText();

                contents.beginText();
                contents.setFont(PDType1Font.HELVETICA, 10);
                contents.newLineAtOffset(500, 20);
                contents.showText("Page " + (index + 1) + " / " + pageCount);
                contents.endText();
                contents.close();
            }

            doc.save(f);
        }

    }

    private int drawLine(PDPageContentStream contents, int y, Date dateEcriture, String journal, String mouvement, String piece, String libelle, String codeLettrage, String codePointage,
            Calendar dateLettrage, Calendar datePointage, Double debit, Double credit, Double solde) throws IOException {
        y -= LINE_HEIGHT;

        contents.setFont(PDType1Font.HELVETICA, 7);
        if (dateEcriture != null) {
            drawRightAlign(contents, COL_1_X, y, COL_1_SIZE - 5, this.dateFormatEcr.format(dateEcriture));
        }
        if (journal != null) {
            contents.beginText();
            contents.newLineAtOffset(COL_2_X, y);
            contents.showText(cleanString(journal));
            contents.endText();
        }
        if (mouvement != null) {
            contents.beginText();
            contents.newLineAtOffset(COL_3_X, y);
            contents.showText(cleanString(mouvement));
            contents.endText();
        }
        contents.beginText();
        contents.newLineAtOffset(COL_4_X, y);
        contents.showText(cleanString(libelle));
        contents.endText();

        drawRightAlign(contents, COL_5_X, y, COL_5_SIZE, this.decimalFormat.format(debit));
        drawRightAlign(contents, COL_6_X, y, COL_6_SIZE, this.decimalFormat.format(credit));
        drawRightAlign(contents, COL_7_X, y, COL_7_SIZE, this.decimalFormat.format(solde));

        contents.setLineWidth(0.5f);
        contents.setStrokingColor(Color.LIGHT_GRAY);
        contents.moveTo(COL_1_X - 4, y - 2f);
        contents.lineTo(COL_7_X + 4 + COL_7_SIZE, y - 2f);
        contents.stroke();

        return y;
    }

    private int drawRecapLine(PDPageContentStream contents, int y, String num, String libelle, Double debit, Double credit, Double solde, boolean total) throws IOException {
        y -= LINE_HEIGHT;

        contents.setFont(PDType1Font.HELVETICA_BOLD, 7);
        if (!total) {
            contents.beginText();
            contents.newLineAtOffset(COL_1_X, y);
            contents.showText(num);
            contents.endText();

            contents.setFont(PDType1Font.HELVETICA, 7);

            contents.beginText();
            contents.newLineAtOffset(COL_3_X, y);
            contents.showText(cleanString(libelle));
            contents.endText();
        }
        drawRightAlign(contents, COL_5_X, y, COL_5_SIZE, this.decimalFormat.format(debit));
        drawRightAlign(contents, COL_6_X, y, COL_6_SIZE, this.decimalFormat.format(credit));
        drawRightAlign(contents, COL_7_X, y, COL_7_SIZE, this.decimalFormat.format(solde));

        if (!total) {
            contents.setLineWidth(0.5f);
            contents.setStrokingColor(Color.LIGHT_GRAY);
            contents.moveTo(COL_1_X - 4, y - 2f);
            contents.lineTo(COL_7_X + 4 + COL_7_SIZE, y - 2f);
            contents.stroke();
        }
        return y;
    }

    private String cleanString(String s) {
        StringBuilder b = new StringBuilder(s.length());

        for (char c : s.toCharArray()) {
            if (Character.isLetterOrDigit(c)) {
                b.append(c);
            } else if (c == 'é' || c == 'è' || c == 'ê' || c == 'â' || c == 'à' || c == 'î' || c == 'ù' || c == 'û' || c == 'ô' || c == 'ç') {
                b.append(c);
            } else if (c >= 32 && c < 127) {
                b.append(c);
            } else {
                b.append(' ');
            }
        }

        return b.toString();

    }

    private void drawRightAlign(PDPageContentStream contents, float x, float y, float width, String text) throws IOException {
        contents.beginText();
        final float w = PDType1Font.HELVETICA.getStringWidth(text) / 1000.0f * 7f;
        contents.newLineAtOffset(x + width - w, y);
        contents.showText(text);
        contents.endText();
    }

    private int drawNomCompte(PDPageContentStream contents, int y, String numCpt, String nomCpt) throws IOException {
        y -= LINE_HEIGHT;
        contents.beginText();
        contents.setFont(PDType1Font.HELVETICA_BOLD, 7);
        contents.newLineAtOffset(COL_1_X, y);
        String string = numCpt + " " + nomCpt.toUpperCase();
        contents.showText(cleanString(string));
        contents.endText();

        return y;
    }

    private int drawCumul(PDPageContentStream contents, int y, Double debit, Double credit, Double solde) throws IOException {
        y -= LINE_HEIGHT;
        contents.setFont(PDType1Font.HELVETICA_BOLD, 7);
        contents.beginText();
        contents.newLineAtOffset(COL_4_X, y);
        contents.showText("Cumuls antérieurs");
        contents.endText();

        drawRightAlign(contents, COL_5_X, y, COL_5_SIZE, this.decimalFormat.format(debit));
        drawRightAlign(contents, COL_6_X, y, COL_6_SIZE, this.decimalFormat.format(credit));
        drawRightAlign(contents, COL_7_X, y, COL_7_SIZE, this.decimalFormat.format(solde));

        return y;
    }

    private int drawHeader(PDPageContentStream contents, String companyName, String title, boolean full) throws IOException {
        int y = 795;
        contents.beginText();
        contents.setFont(PDType1Font.HELVETICA_BOLD, 14);
        contents.newLineAtOffset(40, y);
        contents.showText("GRAND LIVRE    " + companyName.toUpperCase());
        contents.endText();
        y -= 17;
        contents.beginText();
        contents.setFont(PDType1Font.HELVETICA, 12);
        contents.newLineAtOffset(40, y);
        contents.showText(title);
        contents.endText();

        y -= 20;
        contents.setFont(PDType1Font.HELVETICA_BOLD, 7);

        if (full) {
            contents.beginText();
            contents.newLineAtOffset(COL_1_X + 5, y);
            contents.showText("DATE");
            contents.endText();

            contents.beginText();
            contents.newLineAtOffset(COL_2_X, y);
            contents.showText("JOURNAL");
            contents.endText();

            contents.beginText();
            contents.newLineAtOffset(COL_3_X, y);
            contents.showText(" MVT");
            contents.endText();

            contents.beginText();
            contents.newLineAtOffset(COL_4_X, y);
            contents.showText("LIBELLE");
            contents.endText();
        } else {
            contents.beginText();
            contents.newLineAtOffset(COL_1_X, y);
            contents.showText("COMPTE");
            contents.endText();
        }

        drawRightAlign(contents, COL_5_X, y, COL_5_SIZE, "DEBIT");
        drawRightAlign(contents, COL_6_X, y, COL_6_SIZE, "CREDIT");
        drawRightAlign(contents, COL_7_X, y, COL_7_SIZE, "SOLDE CUMULE");

        y -= 4;
        contents.setStrokingColor(Color.BLACK);
        contents.setLineWidth(1f);
        contents.moveTo(COL_1_X - 4, y);
        contents.lineTo(COL_7_X + COL_7_SIZE + 4, y);
        contents.stroke();

        return y;

    }

    private int drawSousTotal(PDPageContentStream contents, int y, String numCpt, String nomCpt, long debit, long credit) throws IOException {

        final Map<String, Object> lineRecap = new HashMap<>();
        lineRecap.put("NUMERO", numCpt);
        lineRecap.put("LIBELLE", nomCpt);
        lineRecap.put("DEBIT", debit);
        lineRecap.put("CREDIT", credit);

        this.recapSousTotaux.add(lineRecap);

        y -= LINE_HEIGHT;
        contents.beginText();
        contents.setFont(PDType1Font.HELVETICA_BOLD, 7);
        contents.newLineAtOffset(COL_4_X, y);
        contents.showText("Sous-total");
        contents.endText();

        drawRightAlign(contents, COL_5_X, y, COL_5_SIZE, this.decimalFormat.format(debit / 100D));
        drawRightAlign(contents, COL_6_X, y, COL_6_SIZE, this.decimalFormat.format(credit / 100D));
        drawRightAlign(contents, COL_7_X, y, COL_7_SIZE, this.decimalFormat.format((debit - credit) / 100D));

        y -= LINE_HEIGHT;

        return y;
    }

    private List<SQLRowValues> fetchEcritures(List<Integer> lCompteSolde) {
        final SQLRowValues vals = new SQLRowValues(this.tableEcriture);
        vals.put("ID_COMPTE_PCE", null);
        vals.put("COMPTE_NUMERO", null);
        vals.put("COMPTE_NOM", null);
        if (this.tableEcriture.contains("NOM_PIECE")) {
            vals.put("NOM_PIECE", null);
        }
        vals.put("ID_JOURNAL", null);
        vals.put("JOURNAL_CODE", null);
        vals.putRowValues("ID_MOUVEMENT").put("NUMERO", null).putRowValues("ID_PIECE").put("NOM", null);
        vals.put("CREDIT", null);
        vals.put("DEBIT", null);
        vals.put("DATE", null);
        vals.put("NOM", null);
        vals.put("LETTRAGE", null);
        vals.put("POINTEE", null);
        vals.put("DATE_LETTRAGE", null);
        vals.put("DATE_POINTEE", null);
        final SQLRowValuesListFetcher fetcher = new SQLRowValuesListFetcher(vals);
        fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
            @Override
            public SQLSelect transformChecked(SQLSelect sel) {
                final Where w = getWhere(lCompteSolde);
                sel.setWhere(w);
                sel.addFieldOrder(GrandLivrePDF.this.tableEcriture.getField("COMPTE_NUMERO"));
                sel.addFieldOrder(GrandLivrePDF.this.tableEcriture.getField("DATE"));
                sel.addFieldOrder(GrandLivrePDF.this.tableMvt.getField("NUMERO"));
                return sel;
            }
        });

        return fetcher.fetch();
    }

    private List<Integer> getListeCompteSolde() {
        final SQLSelect sel = new SQLSelect();
        sel.addSelect(this.tableEcriture.getField("ID_COMPTE_PCE"));
        sel.addSelect(this.tableEcriture.getField("DEBIT"), "SUM");
        sel.addSelect(this.tableEcriture.getField("CREDIT"), "SUM");

        final Where w = getWhere(null);

        sel.setWhere(w);
        sel.addGroupBy(this.tableEcriture.getField("ID_COMPTE_PCE"));

        final String req = sel.asString();
        @SuppressWarnings("unchecked")
        final List<Object[]> l = (List<Object[]>) this.tableCompte.getBase().getDataSource().execute(req, new ArrayListHandler());
        final List<Integer> list = new ArrayList<>();
        for (final Object[] o : l) {
            long credit = 0;
            if (o[2] != null) {
                credit = Long.parseLong(o[2].toString());
            }

            long debit = 0;
            if (o[1] != null) {
                debit = Long.parseLong(o[1].toString());
            }

            final int id = Integer.parseInt(o[0].toString());
            final long solde = debit - credit;
            if (solde == 0) {
                list.add(id);
            }
        }
        return list;
    }

    private Where getWhere(final List<Integer> lCompteSolde) {
        Where w = new Where(this.tableEcriture.getField("DATE"), this.dateDu, this.dateAu);

        if (this.compteDeb.equals(this.compteEnd)) {
            w = w.and(new Where(this.tableEcriture.getField("COMPTE_NUMERO"), "=", this.compteDeb));
        } else {
            w = w.and(new Where(this.tableEcriture.getField("COMPTE_NUMERO"), (Object) this.compteDeb, (Object) this.compteEnd));
        }
        w = w.and(new Where(this.tableEcriture.getField("ID_JOURNAL"), "!=", this.idJrnlExclude));
        w = w.and(new Where(this.tableEcriture.getField("ID_MOUVEMENT"), "=", this.tableMvt.getField("ID")));

        if (this.lettrage == MODELETTREE) {
            w = w.and(new Where(this.tableEcriture.getField("LETTRAGE"), "<>", (Object) null));
            w = w.and(new Where(this.tableEcriture.getField("LETTRAGE"), "!=", ""));
            w = w.and(new Where(this.tableEcriture.getField("DATE_LETTRAGE"), "<=", this.dateAu));
        } else if (this.lettrage == MODENONLETTREE_PERIODE) {
            Where w2 = new Where(this.tableEcriture.getField("LETTRAGE"), "=", (Object) null);
            Where wSTTC = new Where(this.tableEcriture.getField("DATE_LETTRAGE"), "<>", (Object) null);
            wSTTC = wSTTC.and(new Where(this.tableEcriture.getField("DATE_LETTRAGE"), ">", this.dateAu));
            w2 = w2.or(wSTTC);
            w = w.and(w2.or(new Where(this.tableEcriture.getField("LETTRAGE"), "=", "")));
        } else if (this.lettrage == MODENONLETTREE_ALL) {
            final Where w2 = new Where(this.tableEcriture.getField("LETTRAGE"), "=", (Object) null);
            w = w.and(w2.or(new Where(this.tableEcriture.getField("LETTRAGE"), "=", "")));
        }

        if (this.excludeCompteSolde && lCompteSolde != null) {
            w = w.and(new Where(this.tableEcriture.getField("ID_COMPTE_PCE"), lCompteSolde).not());

        }
        w = w.and(new Where(this.tableEcriture.getField("NOM"), "NOT LIKE", "Fermeture du compte%"));

        if (!UserRightsManager.getCurrentUserRights().haveRight(ComptaUserRight.ACCES_NOT_RESCTRICTED_TO_411)) {
            // TODO Show Restricted acces in UI
            w = w.and(new Where(this.tableEcriture.getField("COMPTE_NUMERO"), "LIKE", "411%"));
        }
        return w;
    }

    /**
     * @param d date limite des cumuls
     * @return Map<Integer id compte, Long solde(debit-credit)>
     */
    private Map<Integer, Long> getCumulsAnterieur(Date d, List<Integer> listCompteSolde) {
        final SQLSelect sel = new SQLSelect();
        sel.addSelect(this.tableEcriture.getField("ID_COMPTE_PCE"));
        sel.addSelect(this.tableEcriture.getField("DEBIT"), "SUM");
        sel.addSelect(this.tableEcriture.getField("CREDIT"), "SUM");
        sel.addSelect(this.tableEcriture.getField("COMPTE_NUMERO"));
        Where w = new Where(this.tableEcriture.getField("DATE"), "<", d);
        w = w.and(new Where(this.tableEcriture.getField("ID_MOUVEMENT"), "=", this.tableMvt.getKey()));

        if (this.compteDeb.equals(this.compteEnd)) {
            w = w.and(new Where(this.tableEcriture.getField("COMPTE_NUMERO"), "=", this.compteDeb));
        } else {
            w = w.and(new Where(this.tableEcriture.getField("COMPTE_NUMERO"), (Object) this.compteDeb, (Object) this.compteEnd));
        }

        if (this.lettrage == MODELETTREE) {
            final Object o = null;
            w = w.and(new Where(this.tableEcriture.getField("LETTRAGE"), "<>", o));
            w = w.and(new Where(this.tableEcriture.getField("LETTRAGE"), "!=", ""));
        } else {
            if (this.lettrage == MODENONLETTREE_ALL || this.lettrage == MODENONLETTREE_PERIODE) {
                final Object o = null;
                final Where w2 = new Where(this.tableEcriture.getField("LETTRAGE"), "=", o);
                w = w.and(w2.or(new Where(this.tableEcriture.getField("LETTRAGE"), "=", "")));
            }
        }

        w = w.and(new Where(this.tableEcriture.getField("ID_COMPTE_PCE"), "=", this.tableCompte.getField("ID")));
        w = w.and(new Where(this.tableEcriture.getField("ID_JOURNAL"), "!=", this.idJrnlExclude));
        if (listCompteSolde != null) {
            w = w.and(new Where(this.tableEcriture.getField("ID_COMPTE_PCE"), listCompteSolde).not());
        }

        sel.setWhere(w);

        final String req = sel.asString() + " GROUP BY \"ECRITURE\".\"ID_COMPTE_PCE\", \"ECRITURE\".\"COMPTE_NUMERO\"";
        @SuppressWarnings("unchecked")
        final List<Object[]> l = (List<Object[]>) this.tableEcriture.getBase().getDataSource().execute(req, new ArrayListHandler());
        final Map<Integer, Long> map = new HashMap<>();

        final int idCptFourn = ComptePCESQLElement.getId("401", "Fournisseurs");
        final int idCptClient = ComptePCESQLElement.getId("411", "Clients");

        for (final Object[] o : l) {

            long credit = 0;
            if (o[2] != null) {
                credit = Long.parseLong(o[2].toString());
            }

            long debit = 0;
            if (o[1] != null) {
                debit = Long.parseLong(o[1].toString());
            }

            final int id = Integer.parseInt(o[0].toString());
            final long solde = debit - credit;
            map.put(id, solde);
            if (o[3] != null) {
                final String numero = o[3].toString();
                if (this.centralFourn && numero.startsWith("401")) {
                    Long lS = map.get(idCptFourn);
                    if (lS != null) {
                        lS += solde;
                    } else {
                        lS = solde;
                    }
                    map.put(idCptFourn, lS);
                }
                if (this.centralClient && numero.startsWith("411")) {
                    Long lS = map.get(idCptClient);
                    if (lS != null) {
                        lS += solde;
                    } else {
                        lS = solde;
                    }
                    map.put(idCptClient, lS);
                }
            }
        }

        return map;
    }

}