OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 73 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
18 ilm 1 
/*
2 
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3 
 *
4 
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5 
 *
6 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7 
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8 
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9 
 * language governing permissions and limitations under the License.
10 
 *
11 
 * When distributing the software, include this License Header Notice in each file.
12 
 */
13 
 
14 
 package org.openconcerto.erp.core.finance.accounting.model;
15 
 
16 
import org.openconcerto.erp.config.ComptaPropsConfiguration;
17 
import org.openconcerto.sql.Configuration;
18 
import org.openconcerto.sql.model.SQLBase;
156 ilm 19 
import org.openconcerto.sql.model.SQLField;
18 ilm 20 
import org.openconcerto.sql.model.SQLRow;
21 
import org.openconcerto.sql.model.SQLSelect;
22 
import org.openconcerto.sql.model.SQLTable;
23 
import org.openconcerto.sql.model.Where;
24 
 
156 ilm 25 
import java.util.ArrayList;
18 ilm 26 
import java.util.Date;
27 
import java.util.List;
28 
 
29 
import org.apache.commons.dbutils.handlers.ArrayListHandler;
30 
 
31 
public class SommeCompte {
32 
 
33 
    private static final SQLBase base = ((ComptaPropsConfiguration) Configuration.getInstance()).getSQLBaseSociete();
34 
 
35 
    private SQLRow rowAnalytique = null;
36 
 
156 ilm 37 
    private boolean removeClotureCompte = false;
38 
 
39 
    private List<String> compteUsed = new ArrayList<String>();
40 
 
18 ilm 41 
    public SommeCompte() {
42 
        this(null);
43 
    }
44 
 
45 
    public SommeCompte(SQLRow rowAnalytique) {
46 
        this.rowAnalytique = rowAnalytique;
47 
    }
48 
 
156 ilm 49 
    public void setRemoveClotureCompte(boolean removeClotureCompte) {
50 
        this.removeClotureCompte = removeClotureCompte;
51 
    }
52 
 
18 ilm 53 
    SQLTable ecritureTable = base.getTable("ECRITURE");
54 
    SQLTable compteTable = base.getTable("COMPTE_PCE");
55 
 
56 
    private void addAnalytiqueJoin(SQLSelect sel) {
57 
        if (this.rowAnalytique != null) {
58 
            SQLTable tableAssoc = ecritureTable.getTable("ASSOCIATION_ANALYTIQUE");
59 
            Where join = new Where(tableAssoc.getField("ID_ECRITURE"), "=", ecritureTable.getKey());
21 ilm 60 
            join = join.and(new Where(tableAssoc.getField("ID_POSTE_ANALYTIQUE"), "=", this.rowAnalytique.getID()));
18 ilm 61 
            sel.addJoin("RIGHT", ecritureTable.getTable("ASSOCIATION_ANALYTIQUE"), join);
62 
        }
63 
    }
64 
 
65 
    /***********************************************************************************************
66 
     * calcul le solde débiteur du sous arbre du PCE de racine numero
67 
     *
68 
     * @param numero numero du compte racine
69 
     * @param dateDebut Date de début de la période prise en compte
70 
     * @param dateFin Date de la fin de la période prise en compte
71 
     * @return le solde debiteur
72 
     **********************************************************************************************/
73 
    public long sommeCompteFils(String numero, Date dateDebut, Date dateFin) {
156 ilm 74 
 
75 
        this.compteUsed.add(numero.trim() + "%");
18 ilm 76 
        long sommeDebit = 0;
77 
        long sommeCredit = 0;
78 
 
156 ilm 79 
        SQLSelect sel = new SQLSelect();
18 ilm 80 
 
81 
        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
82 
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
83 
        // sel.addSelect(compteTable.getField("ID"));
84 
        // sel.addSelect(compteTable.getField("NUMERO"));
85 
 
86 
        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
87 
        // compteTable.getField("ID"));
88 
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
89 
        Where w2 = new Where(compteTable.getField("NUMERO"), "LIKE", numero.trim() + "%");
90 
        Where w3 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);
156 ilm 91 
        if (this.removeClotureCompte) {
92 
            Where w4 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
93 
            sel.setWhere(w2.and(w3).and(w4));
94 
        } else {
95 
            sel.setWhere(w2.and(w3));
96 
        }
18 ilm 97 
        addAnalytiqueJoin(sel);
98 
 
99 
        // String req = sel.asString() +
156 ilm 100 
        // " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY
101 
        // \"COMPTE_PCE\".\"NUMERO\"";
18 ilm 102 
        String req = sel.asString();
103 
        // System.out.println(req);
104 
 
105 
        Object ob = base.getDataSource().execute(req, new ArrayListHandler());
106 
 
107 
        List myList = (List) ob;
108 
 
109 
        if (myList.size() != 0) {
110 
 
111 
            for (int i = 0; i < myList.size(); i++) {
112 
 
113 
                Object[] objTmp = (Object[]) myList.get(i);
114 
                if (objTmp[0] != null) {
115 
                    sommeDebit += ((Number) objTmp[0]).longValue();
116 
                }
117 
                if (objTmp[1] != null) {
118 
                    sommeCredit += ((Number) objTmp[1]).longValue();
119 
                }
120 
            }
121 
        }
122 
 
123 
        return sommeDebit - sommeCredit;
124 
    }
125 
 
126 
    /***********************************************************************************************
127 
     * Calcul le solde débiteur des comptes compris dans l'intervalle numeroStart numeroEnd
128 
     *
129 
     * @param numeroStart numero du compte de départ
130 
     * @param numeroEnd nuemro du compte de fin
131 
     * @param includeAllEnd indique si on inclus les sous comptes du compte numeroEnd
132 
     * @param dateDebut Date de début de la période prise en compte
133 
     * @param dateFin Date de la fin de la période prise en compte
134 
     * @return le solde debiteur total des comptes
135 
     **********************************************************************************************/
136 
    public long soldeCompte(int numeroStart, int numeroEnd, boolean includeAllEnd, Date dateDebut, Date dateFin) {
137 
 
138 
        long sommeDebit = 0;
139 
        long sommeCredit = 0;
140 
 
141 
        SQLTable ecritureTable = base.getTable("ECRITURE");
142 
        SQLTable compteTable = base.getTable("COMPTE_PCE");
156 ilm 143 
        SQLSelect sel = new SQLSelect();
18 ilm 144 
 
145 
        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
146 
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
147 
        // sel.addSelect(compteTable.getField("ID"));
148 
        // sel.addSelect(compteTable.getField("NUMERO"));
149 
 
150 
        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
151 
        // compteTable.getField("ID"));
152 
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
153 
        Where w2 = new Where(compteTable.getField("NUMERO"), "LIKE", String.valueOf(numeroStart) + "%");
156 ilm 154 
        this.compteUsed.add(String.valueOf(numeroStart) + "%");
18 ilm 155 
        Where w4 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);
156 
 
157 
        for (int i = numeroStart + 1; i < numeroEnd + 1; i++) {
158 
            Where w3;
159 
            if ((i == numeroEnd) && (!includeAllEnd)) {
160 
                w3 = new Where(compteTable.getField("NUMERO"), "=", String.valueOf(i));
156 ilm 161 
                this.compteUsed.add(String.valueOf(i));
18 ilm 162 
            } else {
163 
                w3 = new Where(compteTable.getField("NUMERO"), "LIKE", String.valueOf(i) + "%");
156 ilm 164 
                this.compteUsed.add(String.valueOf(i) + "%");
18 ilm 165 
            }
166 
            w2 = w2.or(w3);
167 
        }
156 ilm 168 
        if (this.removeClotureCompte) {
18 ilm 169 
 
156 ilm 170 
            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
171 
            sel.setWhere(w2.and(w4).and(w5));
172 
        } else {
173 
            sel.setWhere(w2.and(w4));
174 
        }
18 ilm 175 
        addAnalytiqueJoin(sel);
176 
 
177 
        // String req = sel.asString() +
156 ilm 178 
        // " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY
179 
        // \"COMPTE_PCE\".\"NUMERO\"";
18 ilm 180 
        String req = sel.asString();
181 
 
182 
        Object ob = base.getDataSource().execute(req, new ArrayListHandler());
183 
 
184 
        List myList = (List) ob;
185 
 
186 
        if (myList.size() != 0) {
187 
 
188 
            for (int i = 0; i < myList.size(); i++) {
189 
 
190 
                Object[] objTmp = (Object[]) myList.get(i);
191 
                if (objTmp[0] != null) {
192 
                    sommeDebit += ((Number) objTmp[0]).longValue();
193 
                }
194 
                if (objTmp[1] != null) {
195 
                    sommeCredit += ((Number) objTmp[1]).longValue();
196 
                }
197 
            }
198 
        }
199 
 
200 
        return sommeDebit - sommeCredit;
201 
    }
202 
 
203 
    public long soldeCompteDebiteur(int numeroStart, int numeroEnd, boolean includeAllEnd, Date dateDebut, Date dateFin) {
204 
 
205 
        SQLTable ecritureTable = base.getTable("ECRITURE");
206 
        SQLTable compteTable = base.getTable("COMPTE_PCE");
156 ilm 207 
        SQLSelect sel = new SQLSelect();
18 ilm 208 
 
209 
        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
210 
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
211 
 
212 
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
213 
        sel.addSelect(sel.getAlias(compteTable).getField("ID"));
214 
        sel.addSelect(sel.getAlias(compteTable).getField("NUMERO"));
215 
 
216 
        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
217 
        // compteTable.getField("ID"));
218 
 
219 
        Where w2 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(numeroStart) + "%");
156 ilm 220 
        this.compteUsed.add(String.valueOf(numeroStart) + "%");
18 ilm 221 
        Where w4 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);
222 
 
223 
        for (int i = numeroStart + 1; i < numeroEnd + 1; i++) {
224 
            Where w3;
225 
            if ((i == numeroEnd) && (!includeAllEnd)) {
226 
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "=", String.valueOf(i));
156 ilm 227 
                this.compteUsed.add(String.valueOf(i));
18 ilm 228 
            } else {
229 
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(i) + "%");
156 ilm 230 
                this.compteUsed.add(String.valueOf(i) + "%");
18 ilm 231 
            }
232 
            w2 = w2.or(w3);
233 
        }
234 
 
156 ilm 235 
        if (this.removeClotureCompte) {
236 
 
237 
            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
238 
 
239 
            sel.setWhere(w2.and(w4).and(w5));
240 
        } else {
241 
            sel.setWhere(w2.and(w4));
242 
        }
18 ilm 243 
        addAnalytiqueJoin(sel);
244 
        String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";
73 ilm 245 
 
18 ilm 246 
        Object ob = base.getDataSource().execute(req, new ArrayListHandler());
247 
 
248 
        List myList = (List) ob;
249 
 
250 
        long debit = 0;
251 
        long credit = 0;
252 
        long solde = 0;
253 
        final int size = myList.size();
254 
        for (int i = 0; i < size; i++) {
255 
 
256 
            Object[] objTmp = (Object[]) myList.get(i);
257 
            debit = ((Number) objTmp[0]).longValue();
258 
            credit = ((Number) objTmp[1]).longValue();
259 
            if ((debit - credit) > 0) {
260 
                solde += (debit - credit);
261 
            }
262 
        }
263 
 
264 
        return solde;
265 
    } // MAYBE utiliser HAVING (credit - debit) > 0.0
266 
 
267 
    // FIXME soldeCompteCrediteur(47, 475, boolean includeAllEnd) --> LIKE 47, 48, 49, 50 , ...,
268 
    // 474, 475
269 
    public long soldeCompteCrediteur(int numeroStart, int numeroEnd, boolean includeAllEnd, Date dateDebut, Date dateFin) {
270 
 
271 
        SQLTable ecritureTable = base.getTable("ECRITURE");
272 
        SQLTable compteTable = base.getTable("COMPTE_PCE");
156 ilm 273 
        SQLSelect sel = new SQLSelect();
18 ilm 274 
 
275 
        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
276 
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
277 
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
278 
        sel.addSelect(sel.getAlias(compteTable).getField("ID"));
279 
        sel.addSelect(sel.getAlias(compteTable).getField("NUMERO"));
280 
 
281 
        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
282 
        // compteTable.getField("ID"));
283 
 
284 
        Where w2 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(numeroStart) + "%");
156 ilm 285 
        this.compteUsed.add(String.valueOf(numeroStart) + "%");
18 ilm 286 
        Where w4 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);
287 
 
288 
        for (int i = numeroStart + 1; i < numeroEnd + 1; i++) {
289 
            Where w3;
290 
            if ((i == numeroEnd) && (!includeAllEnd)) {
291 
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "=", String.valueOf(i));
156 ilm 292 
                this.compteUsed.add(String.valueOf(i));
18 ilm 293 
            } else {
294 
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(i) + "%");
156 ilm 295 
                this.compteUsed.add(String.valueOf(i) + "%");
18 ilm 296 
            }
297 
            w2 = w2.or(w3);
298 
        }
156 ilm 299 
        if (this.removeClotureCompte) {
300 
            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
18 ilm 301 
 
156 ilm 302 
            sel.setWhere(w2.and(w4).and(w5));
303 
        } else {
304 
            sel.setWhere(w2.and(w4));
305 
        }
18 ilm 306 
        addAnalytiqueJoin(sel);
307 
        String req = sel.asString();
308 
 
309 
        req += " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";
310 
 
311 
        Object ob = base.getDataSource().execute(req, new ArrayListHandler());
312 
        List myList = (List) ob;
313 
 
314 
        long debit = 0;
315 
        long credit = 0;
316 
        long solde = 0;
317 
        final int size = myList.size();
318 
        for (int i = 0; i < size; i++) {
319 
 
320 
            Object[] objTmp = (Object[]) myList.get(i);
321 
            debit = ((Number) objTmp[0]).longValue();
322 
            credit = ((Number) objTmp[1]).longValue();
323 
 
324 
            if ((credit - debit) > 0) {
325 
                solde += (credit - debit);
326 
            }
327 
        }
328 
 
329 
        return solde;
330 
    }
331 
 
332 
    /***********************************************************************************************
333 
     * Calcul le solde d'un compte
334 
     *
335 
     * @param numero numero du compte
336 
     * @return le solde du compte passé en parametre
337 
     **********************************************************************************************/
338 
    public long soldeCompte(String numero) {
339 
        long sommeDebit = 0;
340 
        long sommeCredit = 0;
341 
 
342 
        SQLTable ecritureTable = base.getTable("ECRITURE");
343 
        SQLTable compteTable = base.getTable("COMPTE_PCE");
344 
        SQLSelect sel = new SQLSelect(base);
345 
 
346 
        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
347 
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
348 
 
349 
        Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=", compteTable.getField("ID"));
350 
        Where w2 = new Where(compteTable.getField("NUMERO"), "=", numero.trim());
156 ilm 351 
        this.compteUsed.add(numero.trim());
352 
 
353 
        if (this.removeClotureCompte) {
354 
            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
355 
 
356 
            sel.setWhere(w.and(w2).and(w5));
357 
        } else {
358 
            sel.setWhere(w.and(w2));
359 
        }
18 ilm 360 
        addAnalytiqueJoin(sel);
361 
        String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";
362 
        // System.out.println(req);
363 
 
364 
        Object ob = base.getDataSource().execute(req, new ArrayListHandler());
365 
 
366 
        List myList = (List) ob;
367 
 
368 
        final int size = myList.size();
369 
        for (int i = 0; i < size; i++) {
370 
 
371 
            Object[] objTmp = (Object[]) myList.get(i);
372 
            sommeDebit += ((Number) objTmp[0]).longValue();
373 
            sommeCredit += ((Number) objTmp[1]).longValue();
374 
        }
375 
 
376 
        return sommeDebit - sommeCredit;
377 
    }
156 ilm 378 
 
379 
    public void clearUsedCompte() {
380 
        this.compteUsed.clear();
381 
    }
382 
 
383 
    public void getNonUsedCompte(Where where, Date dateDebut, Date dateFin) {
384 
 
385 
        SQLSelect sel = new SQLSelect();
386 
        final SQLTable table = base.getTable("COMPTE_PCE");
387 
        final SQLTable tableEcr = base.getTable("ECRITURE");
388 
        final SQLField field = table.getField("NUMERO");
389 
        sel.addSelect(field);
390 
 
391 
        for (String cpt : this.compteUsed) {
392 
            where = where.and(new Where(field, "NOT LIKE", cpt));
393 
        }
394 
 
395 
        Where w2 = new Where(tableEcr.getField("ID_COMPTE_PCE"), "=", table.getKey());
396 
        w2 = w2.and(new Where(tableEcr.getField("DATE"), dateDebut, dateFin));
397 
        where = where.and(w2);
398 
        sel.setWhere(where);
399 
        sel.addGroupBy(field);
400 
        List<String> s = tableEcr.getBase().getDataSource().executeCol(sel.asString());
401 
        System.err.println("COMPTE NOT USED");
402 
        for (String string : s) {
403 
            System.err.println("Compte " + s);
404 
        }
405 
 
406 
    }
18 ilm 407 
}