OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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

import org.openconcerto.erp.config.ComptaPropsConfiguration;
import org.openconcerto.erp.element.objet.Compte;
import org.openconcerto.erp.generationDoc.DocumentLocalStorageManager;
import org.openconcerto.erp.generationDoc.SheetInterface;
import org.openconcerto.erp.preferences.PrinterNXProps;
import org.openconcerto.sql.Configuration;
import org.openconcerto.sql.model.SQLBase;
import org.openconcerto.sql.model.SQLRow;
import org.openconcerto.sql.model.SQLRowListRSH;
import org.openconcerto.sql.model.SQLSelect;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.Where;
import org.openconcerto.utils.GestionDevise;

import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class BalanceSheet extends SheetInterface {

    private static int debutFill, endFill;
    private final static SQLTable tableEcriture = base.getTable("ECRITURE");
    private final static SQLTable tableCompte = base.getTable("COMPTE_PCE");
    private boolean centralClient, centralFourn, centralFournImmo;
    private final DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.MEDIUM);
    private final DateFormat dateFormatEcr = DateFormat.getDateInstance(DateFormat.SHORT);
    public static String TEMPLATE_ID = "Balance";
    public static String TEMPLATE_PROPERTY_NAME = "LocationBalance";
    private Date dateAu, dateDu;
    private String compteDeb, compteEnd;

    public static void setSize(int debut, int fin) {
        debutFill = debut;
        endFill = fin;
    }

    static {
        setSize(7, 69);

    }

    @Override
    protected String getYear() {
        return "";
    }

    @Override
    public String getTemplateId() {
        return TEMPLATE_ID;
    }

    public BalanceSheet(Date du, Date au, String compteDeb, String compteEnd, boolean centralClient, boolean centralFourn, boolean centralFournImmo, boolean displayAll) {
        super();

        Calendar cal = Calendar.getInstance();
        cal.setTime(au);

        // Initialisation des Valeur
        this.nbRowsPerPage = 72;
        this.printer = PrinterNXProps.getInstance().getStringProperty("BalancePrinter");
        this.modele = "Balance.ods";

        final DocumentLocalStorageManager storage = DocumentLocalStorageManager.getInstance();

        // this.locationOO = storage.getDocumentOutputDirectory(TEMPLATE_ID);
        // this.locationPDF = storage.getPDFOutputDirectory(TEMPLATE_ID);
        this.dateAu = au;
        this.dateDu = du;
        this.viewMode = displayAll;
        this.compteDeb = compteDeb;
        this.compteEnd = compteEnd;
        this.centralClient = centralClient;
        this.centralFourn = centralFourn;
        this.centralFournImmo = centralFournImmo;
        createMap();
    }

    private void makeEntete(int rowDeb) {
        SQLRow rowSociete = ((ComptaPropsConfiguration) Configuration.getInstance()).getRowSociete();
        this.mCell.put("A" + rowDeb, rowSociete.getObject("NOM"));
        this.mCell.put("D" + rowDeb, "Edition du " + dateFormat.format(new Date()));
        // this.mCell.put("D" + (rowDeb + 2), "Grand livre");
        System.err.println("MAKE ENTETE");
    }

    private void makePiedPage(int row) {
        if (this.dateDu == null) {
            this.mCell.put("C" + row, "Balance au " + dateFormatEcr.format(this.dateAu));
        } else {
            this.mCell.put("C" + row, "Balance du " + dateFormatEcr.format(this.dateDu) + " au " + dateFormatEcr.format(this.dateAu));
        }
        this.mCell.put("B" + row, "Du compte " + this.compteDeb + " à " + this.compteEnd);
    }

    private void makeSousTotalClasse(int row, long debit, long credit, String classe) {
        this.mCell.put("A" + row, "Total classe " + classe);
        this.mCell.put("B" + row, "");

        this.mCell.put("C" + row, new Double(GestionDevise.currencyToString(debit, false)));
        this.mCell.put("D" + row, new Double(GestionDevise.currencyToString(credit, false)));
        this.mCell.put("E" + row, new Double(GestionDevise.currencyToString(debit - credit, false)));

        this.mapStyleRow.put(new Integer(row), "Titre 1");
    }

    private boolean viewMode = true;

    protected void createMap() {
        int posLine = 1;
        int firstLine = 1;
        this.nbPage = 0;
        long totalDebit, totalCredit, sousTotalDebit, sousTotalCredit;

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

        long totalDebitClient = 0;
        long totalCreditClient = 0;

        long totalDebitFourn = 0;
        long totalCreditFourn = 0;

        long totalDebitFournImmo = 0;
        long totalCreditFournImmo = 0;

        String numCptClient = "411";
        String nomCptClient = "Clients";
        String numCptFourn = "401";
        String nomCptFourn = "Fournisseurs";
        String numCptFournImmo = "404";
        String nomCptFournImmo = "Fournisseurs d'immobilisations";
        boolean addedLine = false;
        boolean addedLineImmo = false;
        boolean addedLineFourn = false;
        int j = 0;
        String classe = "";

        if (viewMode) {
            getBalance();

            for (int i = 0; i < this.vecteurCompte.size();) {

                System.err.println("START NEW PAGE; POS : " + posLine);

                /***************************************************************************************
                 * ENTETE
                 **************************************************************************************/
                makeEntete(posLine);
                posLine += debutFill - 1;

                /***************************************************************************************
                 * CONTENU
                 **************************************************************************************/
                for (j = 0; (j < endFill - debutFill + 1) && i < this.vecteurCompte.size(); j++) {
                    Compte compte = this.vecteurCompte.get(i);

                    String numeroCpt = compte.getNumero();
                    String nomCpt = compte.getNom();

                    long deb = compte.getTotalDebit();
                    long cred = compte.getTotalCredit();

                    totalCredit += cred;
                    sousTotalCredit += cred;
                    totalDebit += deb;
                    sousTotalDebit += deb;

                    this.mCell.put("A" + posLine, numeroCpt);
                    this.mCell.put("B" + posLine, nomCpt);
                    this.mCell.put("C" + posLine, new Double(GestionDevise.currencyToString(deb, false)));
                    this.mCell.put("D" + posLine, new Double(GestionDevise.currencyToString(cred, false)));
                    this.mCell.put("E" + posLine, new Double(GestionDevise.currencyToString(deb - cred, false)));

                    if (compte.getSousCompte().isEmpty()) {
                        this.mapStyleRow.put(new Integer(posLine), "Normal");
                    } else {
                        this.mapStyleRow.put(new Integer(posLine), "Titre 1");
                    }
                    i++;

                    posLine++;
                }

                if (i >= this.vecteurCompte.size() && j < endFill - debutFill + 1) {

                    makeSousTotalClasse(posLine, sousTotalDebit, sousTotalCredit, classe);
                }

                posLine = firstLine + endFill;
                /*
                 * if (this.mapStyleRow.get(new Integer(posLine - 1)) != null) {
                 * this.mapStyleRow.put(new Integer(posLine - 1), "Titre 2"); }
                 */

                // Total
                this.mCell.put("C" + posLine, ((totalDebit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalDebit, false))));
                this.mCell.put("D" + posLine, ((totalCredit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalCredit, false))));
                this.mCell.put("E" + posLine, (totalDebit - totalCredit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));

                posLine += 2;

                // bas de page
                makePiedPage(posLine);

                posLine++;
                firstLine = posLine;
                this.nbPage++;

                // if (i >= this.vecteurCompte.size() && j >= (endFill - debutFill + 1)) {
                //
                // makeEntete(posLine);
                // posLine += debutFill - 1;
                //
                // makeSousTotalClasse(posLine, sousTotalDebit, sousTotalCredit, classe);
                //
                // this.nbPage++;
                // }

            }
        } else {

            this.mapReplace = new HashMap();
            this.mCell = new HashMap();
            this.mapStyleRow = new HashMap();

            SQLSelect sel = new SQLSelect();
            sel.addSelect(tableCompte.getField("ID"));
            sel.addSelect(tableEcriture.getField("DEBIT"), "SUM");
            sel.addSelect(tableEcriture.getField("CREDIT"), "SUM");

            Where w = (new Where(tableEcriture.getField("DATE"), this.dateDu, this.dateAu));
            if (dateDu == null) {
                w = (new Where(tableEcriture.getField("DATE"), "<=", this.dateAu));
            }

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

            // FIXME use flag cloture
            Where wCloture = new Where(tableEcriture.getField("NOM"), "NOT LIKE", "Fermeture du compte%");
            wCloture = wCloture.and(new Where(tableEcriture.getField("DATE"), "=", this.dateAu));
            wCloture = wCloture.or(new Where(tableEcriture.getField("DATE"), "<", this.dateAu));
            w = w.and(wCloture);

            sel.setWhere(w);

            String req = sel.asString() + " AND \"ECRITURE\".\"ID_COMPTE_PCE\" = \"COMPTE_PCE\".\"ID\" GROUP BY  \"COMPTE_PCE\".\"NUMERO\", \"COMPTE_PCE\".\"ID\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";

            System.err.println(req);

            List l = (List) base.getDataSource().execute(req, new ArrayListHandler());

            SQLSelect selCompte = new SQLSelect();
            selCompte.addSelectStar(tableCompte);
            List<SQLRow> compteRows = SQLRowListRSH.execute(selCompte);
            Map<Integer, SQLRow> mapCompte = new HashMap<Integer, SQLRow>();
            for (SQLRow sqlRow : compteRows) {
                mapCompte.put(sqlRow.getID(), sqlRow);
            }

            for (int i = 0; i < l.size();) {

                System.err.println("START NEW PAGE; POS : " + posLine);

                /***************************************************************************************
                 * ENTETE
                 **************************************************************************************/
                makeEntete(posLine);
                posLine += debutFill - 1;

                /***************************************************************************************
                 * CONTENU
                 **************************************************************************************/
                for (j = 0; (j < endFill - debutFill + 1) && i < l.size(); j++) {
                    Object[] o = (Object[]) l.get(i);
                    int idCpt = Integer.parseInt(o[0].toString());
                    // SQLRow rowCpt = tableCompte.getRow(idCpt);
                    SQLRow rowCpt = mapCompte.get(idCpt);

                    String numeroCpt = rowCpt.getString("NUMERO").trim();
                    String nomCpt = rowCpt.getString("NOM");
                    // Changement de classe de compte
                    if (classe.trim().length() != 0 && numeroCpt.length() > 0 && !classe.trim().equalsIgnoreCase(numeroCpt.substring(0, 1))) {

                        makeSousTotalClasse(posLine, sousTotalDebit, sousTotalCredit, classe);

                        sousTotalCredit = 0;
                        sousTotalDebit = 0;
                        classe = numeroCpt.substring(0, 1);

                    } else {
                        if (classe.trim().length() == 0 && numeroCpt.trim().length() > 0) {
                            classe = numeroCpt.substring(0, 1);
                        }

                        long deb = new Double(o[1].toString()).longValue();
                        long cred = new Double(o[2].toString()).longValue();

                        totalCredit += cred;
                        sousTotalCredit += cred;
                        totalDebit += deb;
                        sousTotalDebit += deb;

                        // Centralisation compte client
                        if (this.centralClient && (numeroCpt.equalsIgnoreCase("411") || numeroCpt.startsWith("411"))) {
                            totalDebitClient += deb;
                            totalCreditClient += cred;
                            deb = totalDebitClient;
                            cred = totalCreditClient;
                        }
                        // Centralisation compte fournisseur immo
                        else if (this.centralFournImmo && (numeroCpt.equalsIgnoreCase("404") || numeroCpt.startsWith("404"))) {
                            totalDebitFournImmo += deb;
                            totalCreditFournImmo += cred;
                            deb = totalDebitFournImmo;
                            cred = totalCreditFournImmo;
                        }
                        // Centralisation compte fournisseur
                        else if (this.centralFourn && (numeroCpt.equalsIgnoreCase("401") || numeroCpt.startsWith("401"))) {
                            totalDebitFourn += deb;
                            totalCreditFourn += cred;
                            deb = totalDebitFourn;
                            cred = totalCreditFourn;
                        }

                        if (this.centralClient && !numeroCpt.equalsIgnoreCase("411") && numeroCpt.startsWith("411")) {
                            if (addedLine || !this.centralFournImmo) {
                                posLine--;
                                j--;
                            } else {
                                addedLine = true;
                            }
                            this.mCell.put("A" + posLine, numCptClient);
                            this.mCell.put("B" + posLine, nomCptClient);
                        } else if (this.centralFourn && !numeroCpt.equalsIgnoreCase("401") && numeroCpt.startsWith("401")) {

                            if (addedLineFourn) {
                                posLine--;
                                j--;
                            } else {
                                addedLineFourn = true;
                            }

                            this.mCell.put("A" + posLine, numCptFourn);
                            this.mCell.put("B" + posLine, nomCptFourn);
                        } else if (this.centralFournImmo && !numeroCpt.equalsIgnoreCase("404") && numeroCpt.startsWith("404")) {
                            if (addedLineImmo || !this.centralFourn) {
                                posLine--;
                                j--;
                            } else {
                                addedLineImmo = true;
                            }

                            this.mCell.put("A" + posLine, numCptFournImmo);
                            this.mCell.put("B" + posLine, nomCptFournImmo);
                        } else {
                            this.mCell.put("A" + posLine, numeroCpt);
                            this.mCell.put("B" + posLine, nomCpt);
                        }

                        this.mCell.put("C" + posLine, new Double(GestionDevise.currencyToString(deb, false)));
                        this.mCell.put("D" + posLine, new Double(GestionDevise.currencyToString(cred, false)));
                        this.mCell.put("E" + posLine, new Double(GestionDevise.currencyToString(deb - cred, false)));

                        this.mapStyleRow.put(new Integer(posLine), "Normal");
                        i++;
                    }

                    posLine++;
                }

                if (i >= l.size() && j < endFill - debutFill + 1) {

                    makeSousTotalClasse(posLine, sousTotalDebit, sousTotalCredit, classe);
                }

                posLine = firstLine + endFill;
                /*
                 * if (this.mapStyleRow.get(new Integer(posLine - 1)) != null) {
                 * this.mapStyleRow.put(new Integer(posLine - 1), "Titre 2"); }
                 */

                // Total
                this.mCell.put("C" + posLine, ((totalDebit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalDebit, false))));
                this.mCell.put("D" + posLine, ((totalCredit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalCredit, false))));
                this.mCell.put("E" + posLine, (totalDebit - totalCredit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));

                posLine += 2;

                // bas de page
                makePiedPage(posLine);

                posLine++;
                firstLine = posLine;
                this.nbPage++;

                if (i >= l.size() && j >= (endFill - debutFill + 1)) {

                    makeEntete(posLine);
                    posLine += debutFill - 1;

                    makeSousTotalClasse(posLine, sousTotalDebit, sousTotalCredit, classe);

                    this.nbPage++;
                }

            }
        }

        // on conserve la page d'origine du model
        if (this.nbPage > 0) {
            this.nbPage--;
        }
    }

    private long totalDebitBalance = 0;
    private long totalCreditBalance = 0;
    private List<Compte> vecteurCompte = new ArrayList<Compte>();

    public void getBalance() {

        // Compte numero -- totalDebit
        Map<Number, Long> mapCompteDebit = new HashMap<Number, Long>();
        Map<Number, Long> mapCompteCredit = new HashMap<Number, Long>();
        List<Compte> comptes = new ArrayList<Compte>();
        this.totalDebitBalance = 0;
        this.totalCreditBalance = 0;

        SQLBase base = ((ComptaPropsConfiguration) Configuration.getInstance()).getSQLBaseSociete();
        SQLTable compteTable = base.getTable("COMPTE_PCE");
        SQLTable ecritureTable = base.getTable("ECRITURE");

        SQLSelect sel = new SQLSelect();

        // On recupere le solde des comptes
        sel.addSelect(compteTable.getField("ID"));
        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
        sel.addSelect(compteTable.getField("NUMERO"));
        sel.setDistinct(true);
        Where w = (new Where(tableEcriture.getField("DATE"), this.dateDu, this.dateAu));
        if (dateDu == null) {
            w = (new Where(tableEcriture.getField("DATE"), "<=", this.dateAu));
        }
        sel.setWhere(w.and(new Where(compteTable.getField("ID"), "=", ecritureTable.getField("ID_COMPTE_PCE"))));

        String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\"  ORDER BY \"COMPTE_PCE\".\"NUMERO\"";

        System.out.println(req);

        Object ob = base.getDataSource().execute(req, new ArrayListHandler());

        List myList = (List) ob;

        if (myList.size() != 0) {

            for (int i = 0; i < myList.size(); i++) {

                Object[] tmp = (Object[]) myList.get(i);

                mapCompteDebit.put((Number) tmp[0], Long.parseLong(tmp[1].toString()));
                mapCompteCredit.put((Number) tmp[0], Long.parseLong(tmp[2].toString()));
            }
        }

        // Création du vecteur balance
        sel = new SQLSelect();

        sel.addSelect(compteTable.getKey());
        sel.addSelect(compteTable.getField("NUMERO"));
        sel.addSelect(compteTable.getField("NOM"));

        sel.addRawOrder("\"COMPTE_PCE\".\"NUMERO\"");

        String reqCompte = sel.asString();
        System.out.println(req);

        Object obCompte = base.getDataSource().execute(reqCompte, new ArrayListHandler());

        List myListCompte = (List) obCompte;

        if (myListCompte.size() != 0) {

            for (int i = 0; i < myListCompte.size(); i++) {

                Object[] tmp = (Object[]) myListCompte.get(i);
                System.err.println("Compte " + tmp[1].toString().trim());

                long totalDebit = 0;
                long totalCredit = 0;
                if (mapCompteDebit.get(tmp[0]) != null) {
                    totalDebit = Long.parseLong(mapCompteDebit.get(tmp[0]).toString());
                }

                if (mapCompteCredit.get(tmp[0]) != null) {
                    totalCredit = Long.parseLong(mapCompteCredit.get(tmp[0]).toString());
                }

                this.totalDebitBalance += totalDebit;
                this.totalCreditBalance += totalCredit;

                List<String> sousCompte = new ArrayList<>();
                for (int j = i + 1; j < (myListCompte.size() - 1); j++) {
                    Object[] tmpNext = (Object[]) myListCompte.get(j);
                    if (tmpNext[1].toString().trim().startsWith(tmp[1].toString().trim())) {
                        System.err.println("Sous Compte " + tmpNext[1].toString().trim());
                        sousCompte.add(tmpNext[1].toString().trim());
                        if (mapCompteDebit.get(tmpNext[0]) != null) {
                            totalDebit += Long.parseLong(mapCompteDebit.get(tmpNext[0]).toString());
                        }

                        if (mapCompteCredit.get(tmpNext[0]) != null) {
                            totalCredit += Long.parseLong(mapCompteCredit.get(tmpNext[0]).toString());
                        }
                    } else {
                        break;
                    }
                }
                if ((totalDebit != 0.0) || (totalCredit != 0.0)) {
                    Compte cpt = new Compte(((Number) tmp[0]).intValue(), tmp[1].toString(), tmp[2].toString(), "", totalDebit, totalCredit);
                    cpt.addSousCompte(sousCompte);
                    comptes.add(cpt);
                }
            }
        }

        this.vecteurCompte = comptes;
    }
}