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

    }

}