OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 156 | 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 
 
177 ilm 126 
    public long soldeCompte(String numeroCompte, Date dateDebut, Date dateFin) {
127 
 
128 
        long sommeDebit = 0;
129 
        long sommeCredit = 0;
130 
 
131 
        SQLTable ecritureTable = base.getTable("ECRITURE");
132 
        SQLTable compteTable = base.getTable("COMPTE_PCE");
133 
        SQLSelect sel = new SQLSelect();
134 
 
135 
        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
136 
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
137 
        // sel.addSelect(compteTable.getField("ID"));
138 
        // sel.addSelect(compteTable.getField("NUMERO"));
139 
 
140 
        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
141 
        // compteTable.getField("ID"));
142 
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
143 
        Where w2 = new Where(compteTable.getField("NUMERO"), "LIKE", numeroCompte);
144 
        this.compteUsed.add(numeroCompte);
145 
        Where w4 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);
146 
 
147 
        if (this.removeClotureCompte) {
148 
 
149 
            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
150 
            sel.setWhere(w2.and(w4).and(w5));
151 
        } else {
152 
            sel.setWhere(w2.and(w4));
153 
        }
154 
        addAnalytiqueJoin(sel);
155 
 
156 
        // String req = sel.asString() +
157 
        // " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY
158 
        // \"COMPTE_PCE\".\"NUMERO\"";
159 
        String req = sel.asString();
160 
 
161 
        Object ob = base.getDataSource().execute(req, new ArrayListHandler());
162 
 
163 
        List myList = (List) ob;
164 
 
165 
        if (myList.size() != 0) {
166 
 
167 
            for (int i = 0; i < myList.size(); i++) {
168 
 
169 
                Object[] objTmp = (Object[]) myList.get(i);
170 
                if (objTmp[0] != null) {
171 
                    sommeDebit += ((Number) objTmp[0]).longValue();
172 
                }
173 
                if (objTmp[1] != null) {
174 
                    sommeCredit += ((Number) objTmp[1]).longValue();
175 
                }
176 
            }
177 
        }
178 
 
179 
        return sommeDebit - sommeCredit;
180 
    }
181 
 
18 ilm 182 
    /***********************************************************************************************
183 
     * Calcul le solde débiteur des comptes compris dans l'intervalle numeroStart numeroEnd
184 
     *
185 
     * @param numeroStart numero du compte de départ
186 
     * @param numeroEnd nuemro du compte de fin
187 
     * @param includeAllEnd indique si on inclus les sous comptes du compte numeroEnd
188 
     * @param dateDebut Date de début de la période prise en compte
189 
     * @param dateFin Date de la fin de la période prise en compte
190 
     * @return le solde debiteur total des comptes
191 
     **********************************************************************************************/
192 
    public long soldeCompte(int numeroStart, int numeroEnd, boolean includeAllEnd, Date dateDebut, Date dateFin) {
193 
 
194 
        long sommeDebit = 0;
195 
        long sommeCredit = 0;
196 
 
197 
        SQLTable ecritureTable = base.getTable("ECRITURE");
198 
        SQLTable compteTable = base.getTable("COMPTE_PCE");
156 ilm 199 
        SQLSelect sel = new SQLSelect();
18 ilm 200 
 
201 
        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
202 
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
203 
        // sel.addSelect(compteTable.getField("ID"));
204 
        // sel.addSelect(compteTable.getField("NUMERO"));
205 
 
206 
        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
207 
        // compteTable.getField("ID"));
208 
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
209 
        Where w2 = new Where(compteTable.getField("NUMERO"), "LIKE", String.valueOf(numeroStart) + "%");
156 ilm 210 
        this.compteUsed.add(String.valueOf(numeroStart) + "%");
18 ilm 211 
        Where w4 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);
212 
 
213 
        for (int i = numeroStart + 1; i < numeroEnd + 1; i++) {
214 
            Where w3;
215 
            if ((i == numeroEnd) && (!includeAllEnd)) {
216 
                w3 = new Where(compteTable.getField("NUMERO"), "=", String.valueOf(i));
156 ilm 217 
                this.compteUsed.add(String.valueOf(i));
18 ilm 218 
            } else {
219 
                w3 = new Where(compteTable.getField("NUMERO"), "LIKE", String.valueOf(i) + "%");
156 ilm 220 
                this.compteUsed.add(String.valueOf(i) + "%");
18 ilm 221 
            }
222 
            w2 = w2.or(w3);
223 
        }
156 ilm 224 
        if (this.removeClotureCompte) {
18 ilm 225 
 
156 ilm 226 
            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
227 
            sel.setWhere(w2.and(w4).and(w5));
228 
        } else {
229 
            sel.setWhere(w2.and(w4));
230 
        }
18 ilm 231 
        addAnalytiqueJoin(sel);
232 
 
233 
        // String req = sel.asString() +
156 ilm 234 
        // " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY
235 
        // \"COMPTE_PCE\".\"NUMERO\"";
18 ilm 236 
        String req = sel.asString();
237 
 
238 
        Object ob = base.getDataSource().execute(req, new ArrayListHandler());
239 
 
240 
        List myList = (List) ob;
241 
 
242 
        if (myList.size() != 0) {
243 
 
244 
            for (int i = 0; i < myList.size(); i++) {
245 
 
246 
                Object[] objTmp = (Object[]) myList.get(i);
247 
                if (objTmp[0] != null) {
248 
                    sommeDebit += ((Number) objTmp[0]).longValue();
249 
                }
250 
                if (objTmp[1] != null) {
251 
                    sommeCredit += ((Number) objTmp[1]).longValue();
252 
                }
253 
            }
254 
        }
255 
 
256 
        return sommeDebit - sommeCredit;
257 
    }
258 
 
259 
    public long soldeCompteDebiteur(int numeroStart, int numeroEnd, boolean includeAllEnd, Date dateDebut, Date dateFin) {
260 
 
261 
        SQLTable ecritureTable = base.getTable("ECRITURE");
262 
        SQLTable compteTable = base.getTable("COMPTE_PCE");
156 ilm 263 
        SQLSelect sel = new SQLSelect();
18 ilm 264 
 
265 
        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
266 
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
267 
 
268 
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
269 
        sel.addSelect(sel.getAlias(compteTable).getField("ID"));
270 
        sel.addSelect(sel.getAlias(compteTable).getField("NUMERO"));
271 
 
272 
        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
273 
        // compteTable.getField("ID"));
274 
 
275 
        Where w2 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(numeroStart) + "%");
156 ilm 276 
        this.compteUsed.add(String.valueOf(numeroStart) + "%");
18 ilm 277 
        Where w4 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);
278 
 
279 
        for (int i = numeroStart + 1; i < numeroEnd + 1; i++) {
280 
            Where w3;
281 
            if ((i == numeroEnd) && (!includeAllEnd)) {
282 
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "=", String.valueOf(i));
156 ilm 283 
                this.compteUsed.add(String.valueOf(i));
18 ilm 284 
            } else {
285 
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(i) + "%");
156 ilm 286 
                this.compteUsed.add(String.valueOf(i) + "%");
18 ilm 287 
            }
288 
            w2 = w2.or(w3);
289 
        }
290 
 
156 ilm 291 
        if (this.removeClotureCompte) {
292 
 
293 
            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
294 
 
295 
            sel.setWhere(w2.and(w4).and(w5));
296 
        } else {
297 
            sel.setWhere(w2.and(w4));
298 
        }
18 ilm 299 
        addAnalytiqueJoin(sel);
300 
        String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";
73 ilm 301 
 
18 ilm 302 
        Object ob = base.getDataSource().execute(req, new ArrayListHandler());
303 
 
304 
        List myList = (List) ob;
305 
 
306 
        long debit = 0;
307 
        long credit = 0;
308 
        long solde = 0;
309 
        final int size = myList.size();
310 
        for (int i = 0; i < size; i++) {
311 
 
312 
            Object[] objTmp = (Object[]) myList.get(i);
313 
            debit = ((Number) objTmp[0]).longValue();
314 
            credit = ((Number) objTmp[1]).longValue();
315 
            if ((debit - credit) > 0) {
316 
                solde += (debit - credit);
317 
            }
318 
        }
319 
 
320 
        return solde;
321 
    } // MAYBE utiliser HAVING (credit - debit) > 0.0
322 
 
323 
    // FIXME soldeCompteCrediteur(47, 475, boolean includeAllEnd) --> LIKE 47, 48, 49, 50 , ...,
324 
    // 474, 475
325 
    public long soldeCompteCrediteur(int numeroStart, int numeroEnd, boolean includeAllEnd, Date dateDebut, Date dateFin) {
326 
 
327 
        SQLTable ecritureTable = base.getTable("ECRITURE");
328 
        SQLTable compteTable = base.getTable("COMPTE_PCE");
156 ilm 329 
        SQLSelect sel = new SQLSelect();
18 ilm 330 
 
331 
        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
332 
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
333 
        sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
334 
        sel.addSelect(sel.getAlias(compteTable).getField("ID"));
335 
        sel.addSelect(sel.getAlias(compteTable).getField("NUMERO"));
336 
 
337 
        // Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=",
338 
        // compteTable.getField("ID"));
339 
 
340 
        Where w2 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(numeroStart) + "%");
156 ilm 341 
        this.compteUsed.add(String.valueOf(numeroStart) + "%");
18 ilm 342 
        Where w4 = new Where(ecritureTable.getField("DATE"), dateDebut, dateFin);
343 
 
344 
        for (int i = numeroStart + 1; i < numeroEnd + 1; i++) {
345 
            Where w3;
346 
            if ((i == numeroEnd) && (!includeAllEnd)) {
347 
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "=", String.valueOf(i));
156 ilm 348 
                this.compteUsed.add(String.valueOf(i));
18 ilm 349 
            } else {
350 
                w3 = new Where(sel.getAlias(compteTable).getField("NUMERO"), "LIKE", String.valueOf(i) + "%");
156 ilm 351 
                this.compteUsed.add(String.valueOf(i) + "%");
18 ilm 352 
            }
353 
            w2 = w2.or(w3);
354 
        }
156 ilm 355 
        if (this.removeClotureCompte) {
356 
            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
18 ilm 357 
 
156 ilm 358 
            sel.setWhere(w2.and(w4).and(w5));
359 
        } else {
360 
            sel.setWhere(w2.and(w4));
361 
        }
18 ilm 362 
        addAnalytiqueJoin(sel);
363 
        String req = sel.asString();
364 
 
365 
        req += " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";
366 
 
367 
        Object ob = base.getDataSource().execute(req, new ArrayListHandler());
368 
        List myList = (List) ob;
369 
 
370 
        long debit = 0;
371 
        long credit = 0;
372 
        long solde = 0;
373 
        final int size = myList.size();
374 
        for (int i = 0; i < size; i++) {
375 
 
376 
            Object[] objTmp = (Object[]) myList.get(i);
377 
            debit = ((Number) objTmp[0]).longValue();
378 
            credit = ((Number) objTmp[1]).longValue();
379 
 
380 
            if ((credit - debit) > 0) {
381 
                solde += (credit - debit);
382 
            }
383 
        }
384 
 
385 
        return solde;
386 
    }
387 
 
388 
    /***********************************************************************************************
389 
     * Calcul le solde d'un compte
390 
     *
391 
     * @param numero numero du compte
392 
     * @return le solde du compte passé en parametre
393 
     **********************************************************************************************/
394 
    public long soldeCompte(String numero) {
395 
        long sommeDebit = 0;
396 
        long sommeCredit = 0;
397 
 
398 
        SQLTable ecritureTable = base.getTable("ECRITURE");
399 
        SQLTable compteTable = base.getTable("COMPTE_PCE");
400 
        SQLSelect sel = new SQLSelect(base);
401 
 
402 
        sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
403 
        sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
404 
 
405 
        Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=", compteTable.getField("ID"));
406 
        Where w2 = new Where(compteTable.getField("NUMERO"), "=", numero.trim());
156 ilm 407 
        this.compteUsed.add(numero.trim());
408 
 
409 
        if (this.removeClotureCompte) {
410 
            Where w5 = new Where(ecritureTable.getField("NOM"), "NOT LIKE", "Fermeture du compte %");
411 
 
412 
            sel.setWhere(w.and(w2).and(w5));
413 
        } else {
414 
            sel.setWhere(w.and(w2));
415 
        }
18 ilm 416 
        addAnalytiqueJoin(sel);
417 
        String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";
418 
        // System.out.println(req);
419 
 
420 
        Object ob = base.getDataSource().execute(req, new ArrayListHandler());
421 
 
422 
        List myList = (List) ob;
423 
 
424 
        final int size = myList.size();
425 
        for (int i = 0; i < size; i++) {
426 
 
427 
            Object[] objTmp = (Object[]) myList.get(i);
428 
            sommeDebit += ((Number) objTmp[0]).longValue();
429 
            sommeCredit += ((Number) objTmp[1]).longValue();
430 
        }
431 
 
432 
        return sommeDebit - sommeCredit;
433 
    }
156 ilm 434 
 
435 
    public void clearUsedCompte() {
436 
        this.compteUsed.clear();
437 
    }
438 
 
177 ilm 439 
    public List<String> getNonUsedCompte(Where where, Date dateDebut, Date dateFin) {
156 ilm 440 
 
441 
        SQLSelect sel = new SQLSelect();
442 
        final SQLTable table = base.getTable("COMPTE_PCE");
443 
        final SQLTable tableEcr = base.getTable("ECRITURE");
444 
        final SQLField field = table.getField("NUMERO");
445 
        sel.addSelect(field);
446 
 
447 
        for (String cpt : this.compteUsed) {
448 
            where = where.and(new Where(field, "NOT LIKE", cpt));
449 
        }
450 
 
451 
        Where w2 = new Where(tableEcr.getField("ID_COMPTE_PCE"), "=", table.getKey());
452 
        w2 = w2.and(new Where(tableEcr.getField("DATE"), dateDebut, dateFin));
453 
        where = where.and(w2);
454 
        sel.setWhere(where);
455 
        sel.addGroupBy(field);
456 
        List<String> s = tableEcr.getBase().getDataSource().executeCol(sel.asString());
457 
        System.err.println("COMPTE NOT USED");
458 
        for (String string : s) {
459 
            System.err.println("Compte " + s);
460 
        }
177 ilm 461 
        return s;
156 ilm 462 
    }
18 ilm 463 
}