WebSVN - OpenConcerto - Rev 177 - /trunk/OpenConcerto/src/org/openconcerto/erp/core/finance/accounting/model/SommeCompte.java

/*
 * 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.model;

import org.openconcerto.erp.config.ComptaPropsConfiguration;
import org.openconcerto.sql.Configuration;
import org.openconcerto.sql.model.SQLBase;
import org.openconcerto.sql.model.SQLField;
import org.openconcerto.sql.model.SQLRow;
import org.openconcerto.sql.model.SQLSelect;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.Where;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

public class SommeCompte {

    private static final SQLBase base = ((ComptaPropsConfiguration) Configuration.getInstance()).getSQLBaseSociete();

    private SQLRow rowAnalytique = null;

    private boolean removeClotureCompte = false;

    private List<String> compteUsed = new ArrayList<String>();

    public SommeCompte() {
        this(null);
    }

    public SommeCompte(SQLRow rowAnalytique) {
        this.rowAnalytique = rowAnalytique;
    }

    public void setRemoveClotureCompte(boolean removeClotureCompte) {
        this.removeClotureCompte = removeClotureCompte;
    }

    SQLTable ecritureTable = base.getTable("ECRITURE");
    SQLTable compteTable = base.getTable("COMPTE_PCE");

    private void addAnalytiqueJoin(SQLSelect sel) {
        if (this.rowAnalytique != null) {
            SQLTable tableAssoc = ecritureTable.getTable("ASSOCIATION_ANALYTIQUE");
            Where join = new Where(tableAssoc.getField("ID_ECRITURE"), "=", ecritureTable.getKey());
            join = join.and(new Where(tableAssoc.getField("ID_POSTE_ANALYTIQUE"), "=", this.rowAnalytique.getID()));
            sel.addJoin("RIGHT", ecritureTable.getTable("ASSOCIATION_ANALYTIQUE"), join);
        }
    }

    /***********************************************************************************************
     * calcul le solde débiteur du sous arbre du PCE de racine numero
     * 
     * @param numero numero du compte racine
     * @param dateDebut Date de début de la période prise en compte
     * @param dateFin Date de la fin de la période prise en compte
     * @return le solde debiteur
     **********************************************************************************************/
    public long sommeCompteFils(String numero, Date dateDebut, Date dateFin) {

        this.compteUsed.add(numero.trim() + "%");
        long sommeDebit = 0;
        long sommeCredit = 0;

        SQLSelect sel = new SQLSelect();

        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
        // sel.addSelect(compteTable.getField("ID"));
        // sel.addSelect(compteTable.getField("NUMERO"));

        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
        // compteTable.getField("ID"));
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
        Where w2 = new Where(compteTable.getField("NUMERO"), "LIKE", numero.trim() + "%");
        Where w3 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);
        if (this.removeClotureCompte) {
            Where w4 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
            sel.setWhere(w2.and(w3).and(w4));
        } else {
            sel.setWhere(w2.and(w3));
        }
        addAnalytiqueJoin(sel);

        // String req = sel.asString() +
        // " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY
        // \"COMPTE_PCE\".\"NUMERO\"";
        String req = sel.asString();
        // 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[] objTmp = (Object[]) myList.get(i);
                if (objTmp[0] != null) {
                    sommeDebit += ((Number) objTmp[0]).longValue();
                }
                if (objTmp[1] != null) {
                    sommeCredit += ((Number) objTmp[1]).longValue();
                }
            }
        }

        return sommeDebit - sommeCredit;
    }

    public long soldeCompte(String numeroCompte, Date dateDebut, Date dateFin) {

        long sommeDebit = 0;
        long sommeCredit = 0;

        SQLTable ecritureTable = base.getTable("ECRITURE");
        SQLTable compteTable = base.getTable("COMPTE_PCE");
        SQLSelect sel = new SQLSelect();

        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
        // sel.addSelect(compteTable.getField("ID"));
        // sel.addSelect(compteTable.getField("NUMERO"));

        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
        // compteTable.getField("ID"));
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
        Where w2 = new Where(compteTable.getField("NUMERO"), "LIKE", numeroCompte);
        this.compteUsed.add(numeroCompte);
        Where w4 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);

        if (this.removeClotureCompte) {

            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
            sel.setWhere(w2.and(w4).and(w5));
        } else {
            sel.setWhere(w2.and(w4));
        }
        addAnalytiqueJoin(sel);

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

        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[] objTmp = (Object[]) myList.get(i);
                if (objTmp[0] != null) {
                    sommeDebit += ((Number) objTmp[0]).longValue();
                }
                if (objTmp[1] != null) {
                    sommeCredit += ((Number) objTmp[1]).longValue();
                }
            }
        }

        return sommeDebit - sommeCredit;
    }

    /***********************************************************************************************
     * Calcul le solde débiteur des comptes compris dans l'intervalle numeroStart numeroEnd
     * 
     * @param numeroStart numero du compte de départ
     * @param numeroEnd nuemro du compte de fin
     * @param includeAllEnd indique si on inclus les sous comptes du compte numeroEnd
     * @param dateDebut Date de début de la période prise en compte
     * @param dateFin Date de la fin de la période prise en compte
     * @return le solde debiteur total des comptes
     **********************************************************************************************/
    public long soldeCompte(int numeroStart, int numeroEnd, boolean includeAllEnd, Date dateDebut, Date dateFin) {

        long sommeDebit = 0;
        long sommeCredit = 0;

        SQLTable ecritureTable = base.getTable("ECRITURE");
        SQLTable compteTable = base.getTable("COMPTE_PCE");
        SQLSelect sel = new SQLSelect();

        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
        // sel.addSelect(compteTable.getField("ID"));
        // sel.addSelect(compteTable.getField("NUMERO"));

        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
        // compteTable.getField("ID"));
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
        Where w2 = new Where(compteTable.getField("NUMERO"), "LIKE", String.valueOf(numeroStart) + "%");
        this.compteUsed.add(String.valueOf(numeroStart) + "%");
        Where w4 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);

        for (int i = numeroStart + 1; i < numeroEnd + 1; i++) {
            Where w3;
            if ((i == numeroEnd) && (!includeAllEnd)) {
                w3 = new Where(compteTable.getField("NUMERO"), "=", String.valueOf(i));
                this.compteUsed.add(String.valueOf(i));
            } else {
                w3 = new Where(compteTable.getField("NUMERO"), "LIKE", String.valueOf(i) + "%");
                this.compteUsed.add(String.valueOf(i) + "%");
            }
            w2 = w2.or(w3);
        }
        if (this.removeClotureCompte) {

            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
            sel.setWhere(w2.and(w4).and(w5));
        } else {
            sel.setWhere(w2.and(w4));
        }
        addAnalytiqueJoin(sel);

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

        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[] objTmp = (Object[]) myList.get(i);
                if (objTmp[0] != null) {
                    sommeDebit += ((Number) objTmp[0]).longValue();
                }
                if (objTmp[1] != null) {
                    sommeCredit += ((Number) objTmp[1]).longValue();
                }
            }
        }

        return sommeDebit - sommeCredit;
    }

    public long soldeCompteDebiteur(int numeroStart, int numeroEnd, boolean includeAllEnd, Date dateDebut, Date dateFin) {

        SQLTable ecritureTable = base.getTable("ECRITURE");
        SQLTable compteTable = base.getTable("COMPTE_PCE");
        SQLSelect sel = new SQLSelect();

        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");

        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
        sel.addSelect(sel.getAlias(compteTable).getField("ID"));
        sel.addSelect(sel.getAlias(compteTable).getField("NUMERO"));

        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
        // compteTable.getField("ID"));

        Where w2 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(numeroStart) + "%");
        this.compteUsed.add(String.valueOf(numeroStart) + "%");
        Where w4 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);

        for (int i = numeroStart + 1; i < numeroEnd + 1; i++) {
            Where w3;
            if ((i == numeroEnd) && (!includeAllEnd)) {
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "=", String.valueOf(i));
                this.compteUsed.add(String.valueOf(i));
            } else {
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(i) + "%");
                this.compteUsed.add(String.valueOf(i) + "%");
            }
            w2 = w2.or(w3);
        }

        if (this.removeClotureCompte) {

            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");

            sel.setWhere(w2.and(w4).and(w5));
        } else {
            sel.setWhere(w2.and(w4));
        }
        addAnalytiqueJoin(sel);
        String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";

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

        List myList = (List) ob;

        long debit = 0;
        long credit = 0;
        long solde = 0;
        final int size = myList.size();
        for (int i = 0; i < size; i++) {

            Object[] objTmp = (Object[]) myList.get(i);
            debit = ((Number) objTmp[0]).longValue();
            credit = ((Number) objTmp[1]).longValue();
            if ((debit - credit) > 0) {
                solde += (debit - credit);
            }
        }

        return solde;
    } // MAYBE utiliser HAVING (credit - debit) > 0.0

    // FIXME soldeCompteCrediteur(47, 475, boolean includeAllEnd) --> LIKE 47, 48, 49, 50 , ...,
    // 474, 475
    public long soldeCompteCrediteur(int numeroStart, int numeroEnd, boolean includeAllEnd, Date dateDebut, Date dateFin) {

        SQLTable ecritureTable = base.getTable("ECRITURE");
        SQLTable compteTable = base.getTable("COMPTE_PCE");
        SQLSelect sel = new SQLSelect();

        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
        sel.addSelect(sel.getAlias(compteTable).getField("ID"));
        sel.addSelect(sel.getAlias(compteTable).getField("NUMERO"));

        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
        // compteTable.getField("ID"));

        Where w2 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(numeroStart) + "%");
        this.compteUsed.add(String.valueOf(numeroStart) + "%");
        Where w4 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);

        for (int i = numeroStart + 1; i < numeroEnd + 1; i++) {
            Where w3;
            if ((i == numeroEnd) && (!includeAllEnd)) {
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "=", String.valueOf(i));
                this.compteUsed.add(String.valueOf(i));
            } else {
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(i) + "%");
                this.compteUsed.add(String.valueOf(i) + "%");
            }
            w2 = w2.or(w3);
        }
        if (this.removeClotureCompte) {
            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");

            sel.setWhere(w2.and(w4).and(w5));
        } else {
            sel.setWhere(w2.and(w4));
        }
        addAnalytiqueJoin(sel);
        String req = sel.asString();

        req += " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";

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

        long debit = 0;
        long credit = 0;
        long solde = 0;
        final int size = myList.size();
        for (int i = 0; i < size; i++) {

            Object[] objTmp = (Object[]) myList.get(i);
            debit = ((Number) objTmp[0]).longValue();
            credit = ((Number) objTmp[1]).longValue();

            if ((credit - debit) > 0) {
                solde += (credit - debit);
            }
        }

        return solde;
    }

    /***********************************************************************************************
     * Calcul le solde d'un compte
     * 
     * @param numero numero du compte
     * @return le solde du compte passé en parametre
     **********************************************************************************************/
    public long soldeCompte(String numero) {
        long sommeDebit = 0;
        long sommeCredit = 0;

        SQLTable ecritureTable = base.getTable("ECRITURE");
        SQLTable compteTable = base.getTable("COMPTE_PCE");
        SQLSelect sel = new SQLSelect(base);

        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");

        Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=", compteTable.getField("ID"));
        Where w2 = new Where(compteTable.getField("NUMERO"), "=", numero.trim());
        this.compteUsed.add(numero.trim());

        if (this.removeClotureCompte) {
            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");

            sel.setWhere(w.and(w2).and(w5));
        } else {
            sel.setWhere(w.and(w2));
        }
        addAnalytiqueJoin(sel);
        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;

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

            Object[] objTmp = (Object[]) myList.get(i);
            sommeDebit += ((Number) objTmp[0]).longValue();
            sommeCredit += ((Number) objTmp[1]).longValue();
        }

        return sommeDebit - sommeCredit;
    }

    public void clearUsedCompte() {
        this.compteUsed.clear();
    }

    public List<String> getNonUsedCompte(Where where, Date dateDebut, Date dateFin) {

        SQLSelect sel = new SQLSelect();
        final SQLTable table = base.getTable("COMPTE_PCE");
        final SQLTable tableEcr = base.getTable("ECRITURE");
        final SQLField field = table.getField("NUMERO");
        sel.addSelect(field);

        for (String cpt : this.compteUsed) {
            where = where.and(new Where(field, "NOT LIKE", cpt));
        }

        Where w2 = new Where(tableEcr.getField("ID_COMPTE_PCE"), "=", table.getKey());
        w2 = w2.and(new Where(tableEcr.getField("DATE"), dateDebut, dateFin));
        where = where.and(w2);
        sel.setWhere(where);
        sel.addGroupBy(field);
        List<String> s = tableEcr.getBase().getDataSource().executeCol(sel.asString());
        System.err.println("COMPTE NOT USED");
        for (String string : s) {
            System.err.println("Compte " + s);
        }
        return s;
    }
}
Dépôt officiel du code source de l'ERP OpenConcerto

svn://code.openconcerto.org/openconcerto

(root)/trunk/OpenConcerto/src/org/openconcerto/erp/core/finance/accounting/model/SommeCompte.java - Rev 177
