WebSVN - OpenConcerto - Diff - Rev 73 and 156 - /trunk/OpenConcerto/src/org/openconcerto/erp/core/finance/accounting/model/SommeCompte.java

  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;
     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) {
      * @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(base);
+        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));
+            sel.setWhere(w2.and(w3));
+        }
         addAnalytiqueJoin(sel);
         // String req = sel.asString() +
-        // " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";
+        // " 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());
         long sommeDebit = 0;
         long sommeCredit = 0;
         SQLTable ecritureTable = base.getTable("ECRITURE");
         SQLTable compteTable = base.getTable("COMPTE_PCE");
-        SQLSelect sel = new SQLSelect(base);
+        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));
+            sel.setWhere(w2.and(w4));
+        }
         addAnalytiqueJoin(sel);
         // String req = sel.asString() +
-        // " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";
+        // " 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;
     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(base);
+        SQLSelect sel = new SQLSelect();
         sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");
         sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");
         sel.addJoin("LEFT", ecritureTable.getField("ID_COMPTE_PCE"));
         // 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));
+            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());
     // 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(base);
+        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"));
         // 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));
+            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\"";
         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));
+            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());
             sommeCredit += ((Number) objTmp[1]).longValue();
+        }
         return sommeDebit - sommeCredit;
+    }
+    public void clearUsedCompte() {
+        this.compteUsed.clear();
+    }
+    public void 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);
+        }
+    }
+}

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 73 → 156