OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 174 | Go to most recent revision | Only display areas with differences | Regard whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 174 Rev 180
1 
/*
 1 
/*
2 
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 2 
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3 
 *
3 
 *
4 
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
 4 
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5 
 *
5 
 *
6 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
 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
 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
 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.
 9 
 * language governing permissions and limitations under the License.
10 
 *
10 
 *
11 
 * When distributing the software, include this License Header Notice in each file.
 11 
 * When distributing the software, include this License Header Notice in each file.
12 
 */
 12 
 */
13
13
14 
 package org.openconcerto.erp.core.finance.accounting.report;
 14 
 package org.openconcerto.erp.core.finance.accounting.report;
15 
 
 15 
 
16 
import org.openconcerto.erp.config.ComptaPropsConfiguration;
 16 
import org.openconcerto.erp.config.ComptaPropsConfiguration;
17 
import org.openconcerto.erp.core.finance.accounting.element.ComptePCESQLElement;
 17 
import org.openconcerto.erp.core.finance.accounting.element.ComptePCESQLElement;
18 
import org.openconcerto.erp.generationDoc.AbstractListeSheetXml;
 18 
import org.openconcerto.erp.generationDoc.AbstractListeSheetXml;
19 
import org.openconcerto.erp.preferences.PrinterNXProps;
 19 
import org.openconcerto.erp.preferences.PrinterNXProps;
20 
import org.openconcerto.erp.rights.ComptaUserRight;
 20 
import org.openconcerto.erp.rights.ComptaUserRight;
21 
import org.openconcerto.sql.Configuration;
 21 
import org.openconcerto.sql.Configuration;
22 
import org.openconcerto.sql.model.SQLRow;
 22 
import org.openconcerto.sql.model.SQLRow;
23 
import org.openconcerto.sql.model.SQLRowValues;
 23 
import org.openconcerto.sql.model.SQLRowValues;
24 
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
 24 
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
25 
import org.openconcerto.sql.model.SQLSelect;
 25 
import org.openconcerto.sql.model.SQLSelect;
26 
import org.openconcerto.sql.model.SQLTable;
 26 
import org.openconcerto.sql.model.SQLTable;
27 
import org.openconcerto.sql.model.Where;
 27 
import org.openconcerto.sql.model.Where;
28 
import org.openconcerto.sql.users.rights.UserRightsManager;
 28 
import org.openconcerto.sql.users.rights.UserRightsManager;
29 
import org.openconcerto.utils.GestionDevise;
 29 
import org.openconcerto.utils.GestionDevise;
30 
import org.openconcerto.utils.cc.ITransformer;
 30 
import org.openconcerto.utils.cc.ITransformer;
31 
 
 31 
 
32 
import java.text.DateFormat;
 32 
import java.text.DateFormat;
33 
import java.util.ArrayList;
 33 
import java.util.ArrayList;
34 
import java.util.Calendar;
 34 
import java.util.Calendar;
35 
import java.util.Date;
 35 
import java.util.Date;
36 
import java.util.HashMap;
 36 
import java.util.HashMap;
37 
import java.util.List;
 37 
import java.util.List;
38 
import java.util.Map;
 38 
import java.util.Map;
39 
 
 39 
 
40 
import org.apache.commons.dbutils.handlers.ArrayListHandler;
 40 
import org.apache.commons.dbutils.handlers.ArrayListHandler;
41 
 
 41 
 
42 
public class GrandLivreSheetXML extends AbstractListeSheetXml {
 42 
public class GrandLivreSheetXML extends AbstractListeSheetXml {
43 
 
 43 
 
44 
    private static int debutFill, endFill;
 44 
    private static int debutFill, endFill;
45 
 
 45 
 
46 
    private final static SQLTable tableEcriture = base.getTable("ECRITURE");
 46 
    private final static SQLTable tableEcriture = base.getTable("ECRITURE");
47 
    private final static SQLTable tableJournal = base.getTable("JOURNAL");
 47 
    private final static SQLTable tableJournal = base.getTable("JOURNAL");
48 
    private final static SQLTable tableMvt = base.getTable("MOUVEMENT");
 48 
    private final static SQLTable tableMvt = base.getTable("MOUVEMENT");
49 
    private final static SQLTable tableCompte = base.getTable("COMPTE_PCE");
 49 
    private final static SQLTable tableCompte = base.getTable("COMPTE_PCE");
50 
 
 50 
 
51 
    private final DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.MEDIUM);
 51 
    private final DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.MEDIUM);
52 
    private final DateFormat dateFormatEcr = DateFormat.getDateInstance(DateFormat.SHORT);
 52 
    private final DateFormat dateFormatEcr = DateFormat.getDateInstance(DateFormat.SHORT);
53 
    private SQLRow rowSociete = ((ComptaPropsConfiguration) Configuration.getInstance()).getRowSociete();
 53 
    private SQLRow rowSociete = ((ComptaPropsConfiguration) Configuration.getInstance()).getRowSociete();
54 
 
 54 
 
55 
    private Date dateDu, dateAu;
 55 
    private Date dateDu, dateAu;
56 
    private String compteDeb, compteEnd;
 56 
    private String compteDeb, compteEnd;
57 
    private int lettrage;
 57 
    private int lettrage;
58 
    private boolean cumul = false;
 58 
    private boolean cumul = false;
59 
    private boolean excludeCompteSolde = true;
 59 
    private boolean excludeCompteSolde = true;
60 
    private boolean centralClient = false;
 60 
    private boolean centralClient = false;
61 
    private boolean centralFourn = false;
 61 
    private boolean centralFourn = false;
62 
    int idJrnlExclude = -1;
 62 
    int idJrnlExclude = -1;
63 
 
 63 
 
64 
    public static String TEMPLATE_ID = "GrandLivre";
 64 
    public static String TEMPLATE_ID = "GrandLivre";
65 
    public static String TEMPLATE_PROPERTY_NAME = "LocationGrandLivre";
 65 
    public static String TEMPLATE_PROPERTY_NAME = "LocationGrandLivre";
66 
 
 66 
 
67 
    @Override
 67 
    @Override
68 
    public String getDefaultTemplateId() {
 68 
    public String getDefaultTemplateId() {
69 
        return TEMPLATE_ID;
 69 
        return TEMPLATE_ID;
70 
    }
 70 
    }
71 
 
 71 
 
72 
    Date date;
 72 
    Date date;
73 
 
 73 
 
74 
    @Override
 74 
    @Override
75 
    public String getName() {
 - 
 
76 
        if (this.date == null) {
 - 
 
77 
            this.date = new Date();
 - 
 
78 
        }
 - 
 
79 
        return "GrandLivre" + date.getTime();
 - 
 
80 
    }
 - 
 
81 
 
 - 
 
82 
    @Override
 - 
 
83 
    protected String getStoragePathP() {
 75 
    protected String getStoragePathP() {
84 
        return "Grand Livre";
 76 
        return "Grand Livre";
85 
    }
 77 
    }
86 
 
 78 
 
87 
    public GrandLivreSheetXML(Date du, Date au, String compteDep, String compteEnd, int lettrage, boolean cumul, boolean excludeCptSolde, boolean centralClient, boolean centralFourn,
 79 
    public GrandLivreSheetXML(Date du, Date au, String compteDep, String compteEnd, int lettrage, boolean cumul, boolean excludeCptSolde, boolean centralClient, boolean centralFourn,
88 
            int idJrnlExclude) {
 80 
            int idJrnlExclude) {
89 
        super();
 81 
        super();
90 
        Calendar cal = Calendar.getInstance();
 82 
        Calendar cal = Calendar.getInstance();
91 
        cal.setTime(au);
 83 
        cal.setTime(au);
92 
        this.idJrnlExclude = idJrnlExclude;
 84 
        this.idJrnlExclude = idJrnlExclude;
93 
        this.printer = PrinterNXProps.getInstance().getStringProperty("GrandLivrePrinter");
 85 
        this.printer = PrinterNXProps.getInstance().getStringProperty("GrandLivrePrinter");
94 
        this.dateAu = au;
 86 
        this.dateAu = au;
95 
        this.dateDu = du;
 87 
        this.dateDu = du;
96 
        this.compteDeb = compteDep.trim();
 88 
        this.compteDeb = compteDep.trim();
97 
        this.compteEnd = compteEnd.trim();
 89 
        this.compteEnd = compteEnd.trim();
98 
        this.lettrage = lettrage;
 90 
        this.lettrage = lettrage;
99 
        this.cumul = cumul;
 91 
        this.cumul = cumul;
100 
        this.excludeCompteSolde = excludeCptSolde;
 92 
        this.excludeCompteSolde = excludeCptSolde;
101 
        this.centralClient = centralClient;
 93 
        this.centralClient = centralClient;
102 
        this.centralFourn = centralFourn;
 94 
        this.centralFourn = centralFourn;
103 
        Map<String, Object> map = new HashMap<>();
 95 
        Map<String, Object> map = new HashMap<>();
104 
        recapSousTotaux.add(map);
 96 
        recapSousTotaux.add(map);
105 
        map.put("DATE", "Récapitulatif");
 97 
        map.put("DATE", "Récapitulatif");
106 
        // createMap();
 98 
        // createMap();
107 
    }
 99 
    }
108 
 
 100 
 
109 
    private String toDay = dateFormat.format(new Date());
 101 
    private String toDay = dateFormat.format(new Date());
110 
    private int size;
 102 
    private int size;
111 
 
 103 
 
112 
    // private void makeEntete(int rowDeb) {
 - 
 
113 
    //
 - 
 
114 
    // this.mCell.put("A" + rowDeb, this.rowSociete.getObject("NOM"));
 - 
 
115 
    // this.mCell.put("G" + rowDeb, "Edition du " + this.toDay);
 - 
 
116 
    // // this.mCell.put("D" + (rowDeb + 2), "Grand livre");
 - 
 
117 
    // // System.err.println("MAKE ENTETE");
 - 
 
118 
    // }
 - 
 
119 
    //
 - 
 
120 
    // private void makePiedPage(int row, String comptes) {
 - 
 
121 
    // this.mCell.put("A" + row, "Compte : " + comptes);
 - 
 
122 
    // this.mCell.put("E" + row, "Période du " + dateFormatEcr.format(this.dateDu) + " au " +
 - 
 
123 
    // dateFormatEcr.format(this.dateAu));
 - 
 
124 
    // }
 - 
 
125 
    private List<Map<String, Object>> recapSousTotaux = new ArrayList<>();
 104 
    private List<Map<String, Object>> recapSousTotaux = new ArrayList<>();
126 
 
 105 
 
- 
 
 106 
    @Override
- 
 
 107 
    public String getName() {
- 
 
 108 
        if (this.date == null) {
- 
 
 109 
            this.date = new Date();
- 
 
 110 
        }
- 
 
 111 
        return "GrandLivre" + this.date.getTime();
- 
 
 112 
    }
- 
 
 113 
 
127 
    private void makeSousTotal(String numCpt, String nomCpt, Map<String, Object> line, Map<Integer, String> style, int pos, long debit, long credit) {
 114 
    private void makeSousTotal(String numCpt, String nomCpt, Map<String, Object> line, Map<Integer, String> style, int pos, long debit, long credit) {
128 
        style.put(pos, "Titre 1");
 115 
        style.put(pos, "Titre 1");
129 
 
 116 
 
130 
        line.put("DATE", "");
 117 
        line.put("DATE", "");
131 
        line.put("JOURNAL", "");
 118 
        line.put("JOURNAL", "");
132 
        line.put("MOUVEMENT", "");
 119 
        line.put("MOUVEMENT", "");
133 
        line.put("LIBELLE", "Sous total");
 120 
        line.put("LIBELLE", "Sous total");
134 
        line.put("DEBIT", Double.valueOf(GestionDevise.currencyToString(debit, false)));
 121 
        line.put("DEBIT", Double.valueOf(GestionDevise.currencyToString(debit, false)));
135 
        line.put("CREDIT", Double.valueOf(GestionDevise.currencyToString(credit, false)));
 122 
        line.put("CREDIT", Double.valueOf(GestionDevise.currencyToString(credit, false)));
136 
        line.put("SOLDE", Double.valueOf(GestionDevise.currencyToString(debit - credit, false)));
 123 
        line.put("SOLDE", Double.valueOf(GestionDevise.currencyToString(debit - credit, false)));
137 
 
 124 
 
138 
        Map<String, Object> lineRecap = new HashMap<>();
 125 
        Map<String, Object> lineRecap = new HashMap<>();
139 
        lineRecap.putAll(line);
 126 
        lineRecap.putAll(line);
140 
        lineRecap.put("DATE", numCpt);
 127 
        lineRecap.put("DATE", numCpt);
141 
        lineRecap.put("JOURNAL", nomCpt);
 128 
        lineRecap.put("JOURNAL", nomCpt);
142 
        lineRecap.put("LIBELLE", "");
 129 
        lineRecap.put("LIBELLE", "");
143 
        recapSousTotaux.add(lineRecap);
 130 
        recapSousTotaux.add(lineRecap);
144 
 
 131 
 
145 
    }
 132 
    }
146 
 
 133 
 
147 
    protected void createListeValues() {
 134 
    protected void createListeValues() {
148 
        Date d = new Date();
 135 
        Date d = new Date();
149 
 
 136 
 
150 
        final SQLRowValues vals = new SQLRowValues(tableEcriture);
 137 
        final SQLRowValues vals = new SQLRowValues(tableEcriture);
151 
        vals.put("ID_COMPTE_PCE", null);
 138 
        vals.put("ID_COMPTE_PCE", null);
152 
        vals.put("COMPTE_NUMERO", null);
 139 
        vals.put("COMPTE_NUMERO", null);
153 
        vals.put("COMPTE_NOM", null);
 140 
        vals.put("COMPTE_NOM", null);
154 
        if (tableEcriture.contains("NOM_PIECE")) {
 141 
        if (tableEcriture.contains("NOM_PIECE")) {
155 
            vals.put("NOM_PIECE", null);
 142 
            vals.put("NOM_PIECE", null);
156 
        }
 143 
        }
157 
        vals.put("ID_JOURNAL", null);
 144 
        vals.put("ID_JOURNAL", null);
158 
        vals.put("JOURNAL_CODE", null);
 145 
        vals.put("JOURNAL_CODE", null);
159 
        vals.putRowValues("ID_MOUVEMENT").put("NUMERO", null).putRowValues("ID_PIECE").put("NOM", null);
 146 
        vals.putRowValues("ID_MOUVEMENT").put("NUMERO", null).putRowValues("ID_PIECE").put("NOM", null);
160 
        vals.put("CREDIT", null);
 147 
        vals.put("CREDIT", null);
161 
        vals.put("DEBIT", null);
 148 
        vals.put("DEBIT", null);
162 
        vals.put("DATE", null);
 149 
        vals.put("DATE", null);
163 
        vals.put("NOM", null);
 150 
        vals.put("NOM", null);
164 
        vals.put("LETTRAGE", null);
 151 
        vals.put("LETTRAGE", null);
165 
        vals.put("POINTEE", null);
 152 
        vals.put("POINTEE", null);
166 
        vals.put("DATE_LETTRAGE", null);
 153 
        vals.put("DATE_LETTRAGE", null);
167 
        vals.put("DATE_POINTEE", null);
 154 
        vals.put("DATE_POINTEE", null);
168 
 
 155 
 
169 
        final List<Integer> lCompteSolde;
 156 
        final List<Integer> lCompteSolde;
170 
        if (GrandLivreSheetXML.this.excludeCompteSolde) {
 157 
        if (GrandLivreSheetXML.this.excludeCompteSolde) {
171 
            lCompteSolde = getListeCompteSolde();
 158 
            lCompteSolde = getListeCompteSolde();
172 
        } else {
 159 
        } else {
173 
            lCompteSolde = null;
 160 
            lCompteSolde = null;
174 
        }
 161 
        }
175 
        Map<Integer, Long> mapCumul = getCumulsAnterieur(GrandLivreSheetXML.this.dateDu, lCompteSolde);
 162 
        Map<Integer, Long> mapCumul = getCumulsAnterieur(GrandLivreSheetXML.this.dateDu, lCompteSolde);
176 
 
 163 
 
177 
        final SQLRowValuesListFetcher fetcher = new SQLRowValuesListFetcher(vals);
 164 
        final SQLRowValuesListFetcher fetcher = new SQLRowValuesListFetcher(vals);
178 
        fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
 165 
        fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
179 
            @Override
 166 
            @Override
180 
            public SQLSelect transformChecked(SQLSelect sel) {
 167 
            public SQLSelect transformChecked(SQLSelect sel) {
181 
 
 168 
 
182 
                Where w = getWhere(lCompteSolde);
 169 
                Where w = getWhere(lCompteSolde);
183 
 
 170 
 
184 
                sel.setWhere(w);
 171 
                sel.setWhere(w);
185 
                sel.addFieldOrder(tableEcriture.getField("COMPTE_NUMERO"));
 172 
                sel.addFieldOrder(tableEcriture.getField("COMPTE_NUMERO"));
186 
                sel.addFieldOrder(tableEcriture.getField("DATE"));
 173 
                sel.addFieldOrder(tableEcriture.getField("DATE"));
187 
                sel.addFieldOrder(tableMvt.getField("NUMERO"));
 174 
                sel.addFieldOrder(tableMvt.getField("NUMERO"));
188 
                System.err.println(sel.asString());
 175 
                System.err.println(sel.asString());
189 
                return sel;
 176 
                return sel;
190 
            }
 177 
            }
191 
        });
 178 
        });
192 
 
 179 
 
193 
        List<SQLRowValues> list = fetcher.fetch();
 180 
        List<SQLRowValues> list = fetcher.fetch();
194 
        size = list.size();
 181 
        size = list.size();
195 
        System.err.println("START CREATE Grand livre, NB ecritures  " + size);
 182 
        System.err.println("START CREATE Grand livre, NB ecritures  " + size);
196 
 
 183 
 
197 
        long totalDebit, totalCredit, sousTotalDebit, sousTotalCredit, totalCreditAntC, totalDebitAntC, totalCreditAntF, totalDebitAntF;
 184 
        long totalDebit, totalCredit, sousTotalDebit, sousTotalCredit, totalCreditAntC, totalDebitAntC, totalCreditAntF, totalDebitAntF;
198 
 
 185 
 
199 
        totalDebit = 0;
 186 
        totalDebit = 0;
200 
        totalCredit = 0;
 187 
        totalCredit = 0;
201 
        sousTotalCredit = 0;
 188 
        sousTotalCredit = 0;
202 
        sousTotalDebit = 0;
 189 
        sousTotalDebit = 0;
203 
        totalCreditAntC = 0;
 190 
        totalCreditAntC = 0;
204 
        totalDebitAntC = 0;
 191 
        totalDebitAntC = 0;
205 
        totalCreditAntF = 0;
 192 
        totalCreditAntF = 0;
206 
        totalDebitAntF = 0;
 193 
        totalDebitAntF = 0;
207 
        SQLRowValues rowFirstEcr = null;
 194 
        SQLRowValues rowFirstEcr = null;
208 
        int idCptFirstEcr = 1;
 195 
        int idCptFirstEcr = 1;
209 
 
 196 
 
210 
        boolean setTitle = true;
 197 
        boolean setTitle = true;
211 
        boolean setLine = false;
 198 
        boolean setLine = false;
212 
        boolean setCumuls = true;
 199 
        boolean setCumuls = true;
213 
        boolean firstEcrCentC = true;
 200 
        boolean firstEcrCentC = true;
214 
        boolean firstEcrCentF = true;
 201 
        boolean firstEcrCentF = true;
215 
        String numCptFirstEcr = "411";
 202 
        String numCptFirstEcr = "411";
216 
        String numCptClient = "411";
 203 
        String numCptClient = "411";
217 
        String nomCptClient = "Clients";
 204 
        String nomCptClient = "Clients";
218 
        String numCptFourn = "401";
 205 
        String numCptFourn = "401";
219 
        String nomCptFourn = "Fournisseurs";
 206 
        String nomCptFourn = "Fournisseurs";
220 
        int idCptClient = ComptePCESQLElement.getId(numCptClient, nomCptClient);
 207 
        int idCptClient = ComptePCESQLElement.getId(numCptClient, nomCptClient);
221 
        int idCptFourn = ComptePCESQLElement.getId(numCptFourn, nomCptFourn);
 208 
        int idCptFourn = ComptePCESQLElement.getId(numCptFourn, nomCptFourn);
222 
 
 209 
 
223 
        final String titre3 = "Titre 3";
 210 
        final String titre3 = "Titre 3";
224 
        final String cumulAntString = "Cumuls antérieurs";
 211 
        final String cumulAntString = "Cumuls antérieurs";
225 
        // int j = 0;
 212 
        // int j = 0;
226 
 
 213 
 
227 
        // Valeur de la liste
 214 
        // Valeur de la liste
228 
        // listAllSheetValues ;
 215 
        // listAllSheetValues ;
229 
 
 216 
 
230 
        // Style des lignes
 217 
        // Style des lignes
231 
        // styleAllSheetValues;
 218 
        // styleAllSheetValues;
232 
 
 219 
 
233 
        // Valeur à l'extérieur de la liste
 220 
        // Valeur à l'extérieur de la liste
234 
        // mapAllSheetValues
 221 
        // mapAllSheetValues
235 
 
 222 
 
236 
        List<Map<String, Object>> tableauVals = new ArrayList<Map<String, Object>>();
 223 
        List<Map<String, Object>> tableauVals = new ArrayList<Map<String, Object>>();
237 
        this.listAllSheetValues.put(0, tableauVals);
 224 
        this.listAllSheetValues.put(0, tableauVals);
238 
 
 225 
 
239 
        Map<Integer, String> style = new HashMap<Integer, String>();
 226 
        Map<Integer, String> style = new HashMap<Integer, String>();
240 
        this.styleAllSheetValues.put(0, style);
 227 
        this.styleAllSheetValues.put(0, style);
241 
 
 228 
 
242 
        // Affiche le nom du compte
 229 
        // Affiche le nom du compte
243 
        setTitle = true;
 230 
        setTitle = true;
244 
        // ligne vide avant de mettre le setTitle
 231 
        // ligne vide avant de mettre le setTitle
245 
        setLine = false;
 232 
        setLine = false;
246 
        String nomCpt = "";
 233 
        String nomCpt = "";
247 
        String numCpt = "";
 234 
        String numCpt = "";
248 
        for (int i = 0; i < size;) {
 235 
        for (int i = 0; i < size;) {
249 
            // System.err.println(i);
 236 
            // System.err.println(i);
250 
            // // System.err.println("START NEW PAGE; POS : " + posLine);
 237 
            // // System.err.println("START NEW PAGE; POS : " + posLine);
251 
            //
 238 
            //
252 
            // /***************************************************************************************
 239 
            // /***************************************************************************************
253 
            // * ENTETE
 240 
            // * ENTETE
254 
            // **************************************************************************************/
 241 
            // **************************************************************************************/
255 
            // // makeEntete(posLine);
 242 
            // // makeEntete(posLine);
256 
            // // posLine += debutFill - 1;
 243 
            // // posLine += debutFill - 1;
257 
 
 244 
 
258 
            /***************************************************************************************
 245 
            /***************************************************************************************
259 
             * CONTENU
 246 
             * CONTENU
260 
             **************************************************************************************/
 247 
             **************************************************************************************/
261 
            final Double doubleZero = Double.valueOf("0");
 248 
            final Double doubleZero = Double.valueOf("0");
262 
 
 249 
 
263 
            SQLRowValues rowEcr = list.get(i);
 250 
            SQLRowValues rowEcr = list.get(i);
264 
 
 251 
 
265 
            int idCpt = rowEcr.getInt("ID_COMPTE_PCE");
 252 
            int idCpt = rowEcr.getInt("ID_COMPTE_PCE");
266 
            nomCpt = rowEcr.getString("COMPTE_NOM");
 253 
            nomCpt = rowEcr.getString("COMPTE_NOM");
267 
            numCpt = rowEcr.getString("COMPTE_NUMERO");
 254 
            numCpt = rowEcr.getString("COMPTE_NUMERO");
268 
 
 255 
 
269 
            Map<String, Object> ooLine = new HashMap<String, Object>();
 256 
            Map<String, Object> ooLine = new HashMap<String, Object>();
270 
            tableauVals.add(ooLine);
 257 
            tableauVals.add(ooLine);
271 
            // Cumuls antérieurs
 258 
            // Cumuls antérieurs
272 
            if (setCumuls && this.cumul && !setTitle) {
 259 
            if (setCumuls && this.cumul && !setTitle) {
273 
 
 260 
 
274 
                style.put(tableauVals.size(), titre3);
 261 
                style.put(tableauVals.size(), titre3);
275 
                ooLine.put("DATE", "");
 262 
                ooLine.put("DATE", "");
276 
                ooLine.put("JOURNAL", "");
 263 
                ooLine.put("JOURNAL", "");
277 
                ooLine.put("MOUVEMENT", "");
 264 
                ooLine.put("MOUVEMENT", "");
278 
 
 265 
 
279 
                ooLine.put("LIBELLE", cumulAntString);
 266 
                ooLine.put("LIBELLE", cumulAntString);
280 
                Long longSolde = mapCumul.get(idCpt);
 267 
                Long longSolde = mapCumul.get(idCpt);
281 
 
 268 
 
282 
                if (longSolde == null) {
 269 
                if (longSolde == null) {
283 
                    longSolde = Long.valueOf(0);
 270 
                    longSolde = Long.valueOf(0);
284 
                }
 271 
                }
285 
                long debitCumulAnt = 0;
 272 
                long debitCumulAnt = 0;
286 
                long creditCumulAnt = 0;
 273 
                long creditCumulAnt = 0;
287 
 
 274 
 
288 
                if (longSolde > 0) {
 275 
                if (longSolde > 0) {
289 
                    debitCumulAnt = longSolde;
 276 
                    debitCumulAnt = longSolde;
290 
                } else {
 277 
                } else {
291 
                    creditCumulAnt = -longSolde;
 278 
                    creditCumulAnt = -longSolde;
292 
                }
 279 
                }
293 
                ooLine.put("DEBIT", (debitCumulAnt == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(debitCumulAnt, false)));
 280 
                ooLine.put("DEBIT", (debitCumulAnt == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(debitCumulAnt, false)));
294 
                ooLine.put("CREDIT", (creditCumulAnt == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(creditCumulAnt, false)));
 281 
                ooLine.put("CREDIT", (creditCumulAnt == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(creditCumulAnt, false)));
295 
                ooLine.put("SOLDE", (longSolde == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(longSolde, false)));
 282 
                ooLine.put("SOLDE", (longSolde == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(longSolde, false)));
296 
 
 283 
 
297 
                totalCredit += creditCumulAnt;
 284 
                totalCredit += creditCumulAnt;
298 
                totalDebit += debitCumulAnt;
 285 
                totalDebit += debitCumulAnt;
299 
 
 286 
 
300 
                sousTotalCredit += creditCumulAnt;
 287 
                sousTotalCredit += creditCumulAnt;
301 
                sousTotalDebit += debitCumulAnt;
 288 
                sousTotalDebit += debitCumulAnt;
302 
                setCumuls = false;
 289 
                setCumuls = false;
303 
            } else {
 290 
            } else {
304 
                // Titre
 291 
                // Titre
305 
                if (setTitle) {
 292 
                if (setTitle) {
306 
                    if (!setLine) {
 293 
                    if (!setLine) {
307 
                        style.put(tableauVals.size() - 1, "Titre 1");
 294 
                        style.put(tableauVals.size() - 1, "Titre 1");
308 
 
 295 
 
309 
                        // Si on centralise les comptes clients ou fournisseurs on affiche le
 296 
                        // Si on centralise les comptes clients ou fournisseurs on affiche le
310 
                        // compte 401 ou 411
 297 
                        // compte 401 ou 411
311 
                        if (this.centralClient && nomCpt.startsWith("411")) {
 298 
                        if (this.centralClient && nomCpt.startsWith("411")) {
312 
                            nomCpt = nomCptClient;
 299 
                            nomCpt = nomCptClient;
313 
                            numCpt = numCptClient;
 300 
                            numCpt = numCptClient;
314 
                            idCpt = idCptClient;
 301 
                            idCpt = idCptClient;
315 
                        }
 302 
                        }
316 
                        if (this.centralFourn && nomCpt.startsWith("401")) {
 303 
                        if (this.centralFourn && nomCpt.startsWith("401")) {
317 
                            nomCpt = nomCptFourn;
 304 
                            nomCpt = nomCptFourn;
318 
                            numCpt = numCptFourn;
 305 
                            numCpt = numCptFourn;
319 
                            idCpt = idCptFourn;
 306 
                            idCpt = idCptFourn;
320 
                        }
 307 
                        }
321 
                        ooLine.put("DATE", numCpt);
 308 
                        ooLine.put("DATE", numCpt);
322 
                        ooLine.put("JOURNAL", nomCpt);
 309 
                        ooLine.put("JOURNAL", nomCpt);
323 
                        ooLine.put("MOUVEMENT", "");
 310 
                        ooLine.put("MOUVEMENT", "");
324 
                        ooLine.put("LIBELLE", "");
 311 
                        ooLine.put("LIBELLE", "");
325 
                        ooLine.put("DEBIT", "");
 312 
                        ooLine.put("DEBIT", "");
326 
                        ooLine.put("CREDIT", "");
 313 
                        ooLine.put("CREDIT", "");
327 
                        ooLine.put("SOLDE", "");
 314 
                        ooLine.put("SOLDE", "");
328 
                        setTitle = false;
 315 
                        setTitle = false;
329 
                        setLine = true;
 316 
                        setLine = true;
330 
 
 317 
 
331 
                        if (rowFirstEcr == null) {
 318 
                        if (rowFirstEcr == null) {
332 
                            rowFirstEcr = rowEcr;
 319 
                            rowFirstEcr = rowEcr;
333 
                            idCptFirstEcr = rowEcr.getInt("ID_COMPTE_PCE");
 320 
                            idCptFirstEcr = rowEcr.getInt("ID_COMPTE_PCE");
334 
                            numCptFirstEcr = rowEcr.getString("COMPTE_NUMERO");
 321 
                            numCptFirstEcr = rowEcr.getString("COMPTE_NUMERO");
335 
                        }
 322 
                        }
336 
 
 323 
 
337 
                    } else {
 324 
                    } else {
338 
                        style.put(tableauVals.size() - 1, "Normal");
 325 
                        style.put(tableauVals.size() - 1, "Normal");
339 
                        setLine = false;
 326 
                        setLine = false;
340 
                    }
 327 
                    }
341 
                } else {
 328 
                } else {
342 
 
 329 
 
343 
                    // si on change de compte alors on applique le style Titre 1
 330 
                    // si on change de compte alors on applique le style Titre 1
344 
                    if (rowFirstEcr != null && !numCptFirstEcr.equals(numCpt) && (!this.centralFourn || (!(numCptFirstEcr.startsWith("401") && numCpt.startsWith("401"))))
 331 
                    if (rowFirstEcr != null && !numCptFirstEcr.equals(numCpt) && (!this.centralFourn || (!(numCptFirstEcr.startsWith("401") && numCpt.startsWith("401"))))
345 
                            && (!this.centralClient || (!(numCptFirstEcr.startsWith("411") && numCpt.startsWith("411"))))) {
 332 
                            && (!this.centralClient || (!(numCptFirstEcr.startsWith("411") && numCpt.startsWith("411"))))) {
346 
 
 333 
 
347 
                        makeSousTotal(rowFirstEcr.getString("COMPTE_NUMERO"), rowFirstEcr.getString("COMPTE_NOM"), ooLine, style, tableauVals.size() - 1, sousTotalDebit, sousTotalCredit);
 334 
                        makeSousTotal(rowFirstEcr.getString("COMPTE_NUMERO"), rowFirstEcr.getString("COMPTE_NOM"), ooLine, style, tableauVals.size() - 1, sousTotalDebit, sousTotalCredit);
348 
 
 335 
 
349 
                        rowFirstEcr = rowEcr;
 336 
                        rowFirstEcr = rowEcr;
350 
                        idCptFirstEcr = rowFirstEcr.getInt("ID_COMPTE_PCE");
 337 
                        idCptFirstEcr = rowFirstEcr.getInt("ID_COMPTE_PCE");
351 
                        numCptFirstEcr = rowEcr.getString("COMPTE_NUMERO");
 338 
                        numCptFirstEcr = rowEcr.getString("COMPTE_NUMERO");
352 
 
 339 
 
353 
                        sousTotalCredit = 0;
 340 
                        sousTotalCredit = 0;
354 
                        sousTotalDebit = 0;
 341 
                        sousTotalDebit = 0;
355 
                        setTitle = true;
 342 
                        setTitle = true;
356 
                        setCumuls = true;
 343 
                        setCumuls = true;
357 
                    } else {
 344 
                    } else {
358 
                        long cred = rowEcr.getLong("CREDIT");
 345 
                        long cred = rowEcr.getLong("CREDIT");
359 
                        long deb = rowEcr.getLong("DEBIT");
 346 
                        long deb = rowEcr.getLong("DEBIT");
360 
                        // Centralisation fournisseur
 347 
                        // Centralisation fournisseur
361 
                        if (this.centralFourn && numCpt.startsWith("401")) {
 348 
                        if (this.centralFourn && numCpt.startsWith("401")) {
362 
                            i++;
 349 
                            i++;
363 
 
 350 
 
364 
                            if (firstEcrCentF) {
 351 
                            if (firstEcrCentF) {
365 
                                // posLine++;
 352 
                                // posLine++;
366 
                                ooLine.put("LIBELLE", "Centralisation des comptes fournisseurs");
 353 
                                ooLine.put("LIBELLE", "Centralisation des comptes fournisseurs");
367 
                                style.put(tableauVals.size() - 1, "Normal");
 354 
                                style.put(tableauVals.size() - 1, "Normal");
368 
                                firstEcrCentF = false;
 355 
                                firstEcrCentF = false;
369 
                            }
 356 
                            }
370 
 
 357 
 
371 
                            totalCreditAntF += cred;
 358 
                            totalCreditAntF += cred;
372 
                            totalDebitAntF += deb;
 359 
                            totalDebitAntF += deb;
373 
                            sousTotalCredit += cred;
 360 
                            sousTotalCredit += cred;
374 
                            sousTotalDebit += deb;
 361 
                            sousTotalDebit += deb;
375 
                            long solde = totalDebitAntF - totalCreditAntF;
 362 
                            long solde = totalDebitAntF - totalCreditAntF;
376 
                            ooLine.put("DEBIT", (totalDebitAntF == 0) ? doubleZero : new Double(GestionDevise.currencyToString(totalDebitAntF, false)));
 363 
                            ooLine.put("DEBIT", (totalDebitAntF == 0) ? doubleZero : new Double(GestionDevise.currencyToString(totalDebitAntF, false)));
377 
                            ooLine.put("CREDIT", (totalCreditAntF == 0) ? doubleZero : new Double(GestionDevise.currencyToString(totalCreditAntF, false)));
 364 
                            ooLine.put("CREDIT", (totalCreditAntF == 0) ? doubleZero : new Double(GestionDevise.currencyToString(totalCreditAntF, false)));
378 
                            ooLine.put("SOLDE", (solde == 0) ? doubleZero : new Double(GestionDevise.currencyToString(solde, false)));
 365 
                            ooLine.put("SOLDE", (solde == 0) ? doubleZero : new Double(GestionDevise.currencyToString(solde, false)));
379 
 
 366 
 
380 
                            continue;
 367 
                            continue;
381 
                        }
 368 
                        }
382 
                        // Centralisation client
 369 
                        // Centralisation client
383 
                        if (this.centralClient && numCpt.startsWith("411")) {
 370 
                        if (this.centralClient && numCpt.startsWith("411")) {
384 
                            i++;
 371 
                            i++;
385 
                            if (firstEcrCentC) {
 372 
                            if (firstEcrCentC) {
386 
                                // posLine++;
 373 
                                // posLine++;
387 
                                ooLine.put("LIBELLE", "Centralisation des comptes clients");
 374 
                                ooLine.put("LIBELLE", "Centralisation des comptes clients");
388 
                                style.put(tableauVals.size() - 1, "Normal");
 375 
                                style.put(tableauVals.size() - 1, "Normal");
389 
                                firstEcrCentC = false;
 376 
                                firstEcrCentC = false;
390 
                            }
 377 
                            }
391 
 
 378 
 
392 
                            totalCreditAntC += cred;
 379 
                            totalCreditAntC += cred;
393 
                            totalDebitAntC += deb;
 380 
                            totalDebitAntC += deb;
394 
                            sousTotalCredit += cred;
 381 
                            sousTotalCredit += cred;
395 
                            sousTotalDebit += deb;
 382 
                            sousTotalDebit += deb;
396 
                            long solde = totalDebitAntC - totalCreditAntC;
 383 
                            long solde = totalDebitAntC - totalCreditAntC;
397 
                            ooLine.put("DEBIT", (totalDebitAntC == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(totalDebitAntC, false)));
 384 
                            ooLine.put("DEBIT", (totalDebitAntC == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(totalDebitAntC, false)));
398 
                            ooLine.put("CREDIT", (totalCreditAntC == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(totalCreditAntC, false)));
 385 
                            ooLine.put("CREDIT", (totalCreditAntC == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(totalCreditAntC, false)));
399 
                            ooLine.put("SOLDE", (solde == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(solde, false)));
 386 
                            ooLine.put("SOLDE", (solde == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(solde, false)));
400 
 
 387 
 
401 
                            continue;
 388 
                            continue;
402 
                        }
 389 
                        }
403 
 
 390 
 
404 
                        ooLine.put("DATE", dateFormatEcr.format((Date) rowEcr.getObject("DATE")));
 391 
                        ooLine.put("DATE", dateFormatEcr.format((Date) rowEcr.getObject("DATE")));
405 
 
 392 
 
406 
                        ooLine.put("JOURNAL", rowEcr.getString("JOURNAL_CODE"));
 393 
                        ooLine.put("JOURNAL", rowEcr.getString("JOURNAL_CODE"));
407 
                        ooLine.put("MOUVEMENT", rowEcr.getForeign("ID_MOUVEMENT").getObject("NUMERO"));
 394 
                        ooLine.put("MOUVEMENT", rowEcr.getForeign("ID_MOUVEMENT").getObject("NUMERO"));
408 
                        ooLine.put("PIECE", rowEcr.getForeign("ID_MOUVEMENT").getForeign("ID_PIECE").getObject("NOM"));
 395 
                        ooLine.put("PIECE", rowEcr.getForeign("ID_MOUVEMENT").getForeign("ID_PIECE").getObject("NOM"));
409 
                        ooLine.put("LIBELLE", rowEcr.getObject("NOM"));
 396 
                        ooLine.put("LIBELLE", rowEcr.getObject("NOM"));
410 
                        ooLine.put("CODE_LETTRAGE", rowEcr.getObject("LETTRAGE"));
 397 
                        ooLine.put("CODE_LETTRAGE", rowEcr.getObject("LETTRAGE"));
411 
                        ooLine.put("CODE_POINTAGE", rowEcr.getObject("POINTEE"));
 398 
                        ooLine.put("CODE_POINTAGE", rowEcr.getObject("POINTEE"));
412 
                        ooLine.put("DATE_LETTRAGE", rowEcr.getObject("DATE_LETTRAGE"));
 399 
                        ooLine.put("DATE_LETTRAGE", rowEcr.getObject("DATE_LETTRAGE"));
413 
                        ooLine.put("DATE_POINTAGE", rowEcr.getObject("DATE_LETTRAGE"));
 400 
                        ooLine.put("DATE_POINTAGE", rowEcr.getObject("DATE_LETTRAGE"));
414 
                        if (tableEcriture.contains("NOM_PIECE")) {
 401 
                        if (tableEcriture.contains("NOM_PIECE")) {
415 
                            ooLine.put("NOM_PIECE", rowEcr.getObject("NOM_PIECE"));
 402 
                            ooLine.put("NOM_PIECE", rowEcr.getObject("NOM_PIECE"));
416 
                        }
 403 
                        }
417 
 
 404 
 
418 
                        totalCredit += cred;
 405 
                        totalCredit += cred;
419 
                        totalDebit += deb;
 406 
                        totalDebit += deb;
420 
 
 407 
 
421 
                        sousTotalCredit += cred;
 408 
                        sousTotalCredit += cred;
422 
                        sousTotalDebit += deb;
 409 
                        sousTotalDebit += deb;
423 
                        long solde = sousTotalDebit - sousTotalCredit;
 410 
                        long solde = sousTotalDebit - sousTotalCredit;
424 
 
 411 
 
425 
                        ooLine.put("DEBIT", (deb == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(deb, false)));
 412 
                        ooLine.put("DEBIT", (deb == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(deb, false)));
426 
                        ooLine.put("CREDIT", (cred == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(cred, false)));
 413 
                        ooLine.put("CREDIT", (cred == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(cred, false)));
427 
                        ooLine.put("SOLDE", (solde == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(solde, false)));
 414 
                        ooLine.put("SOLDE", (solde == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(solde, false)));
428 
 
 415 
 
429 
                        style.put(tableauVals.size() - 1, "Normal");
 416 
                        style.put(tableauVals.size() - 1, "Normal");
430 
                        i++;
 417 
                        i++;
431 
                    }
 418 
                    }
432 
 
 419 
 
433 
                }
 420 
                }
434 
 
 421 
 
435 
            }
 422 
            }
436 
 
 423 
 
437 
            // if (i >= size && j < endFill - debutFill + 1) {
 424 
            // if (i >= size && j < endFill - debutFill + 1) {
438 
            // makeSousTotal(posLine, sousTotalDebit, sousTotalCredit);
 425 
            // makeSousTotal(posLine, sousTotalDebit, sousTotalCredit);
439 
            // }
 426 
            // }
440 
            //
 427 
            //
441 
            // posLine = firstLine + endFill;
 428 
            // posLine = firstLine + endFill;
442 
            /*
 429 
            /*
443 
             * if (this.mapStyleRow.get(new Integer(posLine - 1)) != null) {
 430 
             * if (this.mapStyleRow.get(new Integer(posLine - 1)) != null) {
444 
             * this.mapStyleRow.put(new Integer(posLine - 1), "Titre 2"); }
 431 
             * this.mapStyleRow.put(new Integer(posLine - 1), "Titre 2"); }
445 
             */
 432 
             */
446 
 
 433 
 
447 
            // Total
 434 
            // Total
448 
            // this.mCell.put("E" + posLine, (totalDebit == 0) ? doubleZero : new
 435 
            // this.mCell.put("E" + posLine, (totalDebit == 0) ? doubleZero : new
449 
            // Double(GestionDevise.currencyToString(totalDebit, false)));
 436 
            // Double(GestionDevise.currencyToString(totalDebit, false)));
450 
            // this.mCell.put("F" + posLine, (totalCredit == 0) ? doubleZero : new
 437 
            // this.mCell.put("F" + posLine, (totalCredit == 0) ? doubleZero : new
451 
            // Double(GestionDevise.currencyToString(totalCredit, false)));
 438 
            // Double(GestionDevise.currencyToString(totalCredit, false)));
452 
            // this.mCell.put("G" + posLine, (totalDebit - totalCredit == 0) ? doubleZero : new
 439 
            // this.mCell.put("G" + posLine, (totalDebit - totalCredit == 0) ? doubleZero : new
453 
            // Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
 440 
            // Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
454 
            //
 441 
            //
455 
            // posLine += 2;
 442 
            // posLine += 2;
456 
            //
 443 
            //
457 
            // // bas de page
 444 
            // // bas de page
458 
            // makePiedPage(posLine, this.compteDeb + " à " + this.compteEnd);
 445 
            // makePiedPage(posLine, this.compteDeb + " à " + this.compteEnd);
459 
            //
 446 
            //
460 
            // posLine++;
 447 
            // posLine++;
461 
            // firstLine = posLine;
 448 
            // firstLine = posLine;
462 
            // this.nbPage++;
 449 
            // this.nbPage++;
463 
            //
 450 
            //
464 
            // if (i >= size && j >= (endFill - debutFill + 1)) {
 451 
            // if (i >= size && j >= (endFill - debutFill + 1)) {
465 
            //
 452 
            //
466 
            // makeEntete(posLine);
 453 
            // makeEntete(posLine);
467 
            // posLine += debutFill - 1;
 454 
            // posLine += debutFill - 1;
468 
            // makeSousTotal(posLine, sousTotalDebit, sousTotalCredit);
 455 
            // makeSousTotal(posLine, sousTotalDebit, sousTotalCredit);
469 
            // this.nbPage++;
 456 
            // this.nbPage++;
470 
            // }
 457 
            // }
471 
 
 458 
 
472 
        }
 459 
        }
473 
 
 460 
 
474 
        // Total
 461 
        // Total
475 
        // this.mCell.put("E" + posLine, (totalDebit == 0) ? doubleZero : new
 462 
        // this.mCell.put("E" + posLine, (totalDebit == 0) ? doubleZero : new
476 
        // Double(GestionDevise.currencyToString(totalDebit, false)));
 463 
        // Double(GestionDevise.currencyToString(totalDebit, false)));
477 
        // this.mCell.put("F" + posLine, (totalCredit == 0) ? doubleZero : new
 464 
        // this.mCell.put("F" + posLine, (totalCredit == 0) ? doubleZero : new
478 
        // Double(GestionDevise.currencyToString(totalCredit, false)));
 465 
        // Double(GestionDevise.currencyToString(totalCredit, false)));
479 
        // this.mCell.put("G" + posLine, (totalDebit - totalCredit == 0) ? doubleZero : new
 466 
        // this.mCell.put("G" + posLine, (totalDebit - totalCredit == 0) ? doubleZero : new
480 
        // Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
 467 
        // Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
481 
        Map<String, Object> sheetVals = new HashMap<String, Object>();
 468 
        Map<String, Object> sheetVals = new HashMap<String, Object>();
482 
        this.mapAllSheetValues.put(0, sheetVals);
 469 
        this.mapAllSheetValues.put(0, sheetVals);
483 
 
 470 
 
484 
        if (size > 0) {
 471 
        if (size > 0) {
485 
 
 472 
 
486 
            Map<String, Object> ooLine = new HashMap<String, Object>();
 473 
            Map<String, Object> ooLine = new HashMap<String, Object>();
487 
            tableauVals.add(ooLine);
 474 
            tableauVals.add(ooLine);
488 
            makeSousTotal(numCpt, nomCpt, ooLine, style, tableauVals.size() - 1, sousTotalDebit, sousTotalCredit);
 475 
            makeSousTotal(numCpt, nomCpt, ooLine, style, tableauVals.size() - 1, sousTotalDebit, sousTotalCredit);
489 
 
 476 
 
490 
            sheetVals.put("TOTAL_DEBIT", (totalDebit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit, false)));
 477 
            sheetVals.put("TOTAL_DEBIT", (totalDebit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit, false)));
491 
            sheetVals.put("TOTAL_CREDIT", (totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalCredit, false)));
 478 
            sheetVals.put("TOTAL_CREDIT", (totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalCredit, false)));
492 
            sheetVals.put("TOTAL_SOLDE", (totalDebit - totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
 479 
            sheetVals.put("TOTAL_SOLDE", (totalDebit - totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
493 
 
 480 
 
494 
            Map<String, Object> emptyLine = new HashMap<String, Object>();
 481 
            Map<String, Object> emptyLine = new HashMap<String, Object>();
495 
            style.put(tableauVals.size(), "Normal");
 482 
            style.put(tableauVals.size(), "Normal");
496 
            tableauVals.add(emptyLine);
 483 
            tableauVals.add(emptyLine);
497 
            for (Map<String, Object> recap : recapSousTotaux) {
 484 
            for (Map<String, Object> recap : recapSousTotaux) {
498 
                style.put(tableauVals.size(), "Titre 1");
 485 
                style.put(tableauVals.size(), "Titre 1");
499 
                tableauVals.add(recap);
 486 
                tableauVals.add(recap);
500 
            }
 487 
            }
501 
            Map<String, Object> lineRecap = new HashMap<>();
 488 
            Map<String, Object> lineRecap = new HashMap<>();
502 
            lineRecap.put("DEBIT", (totalDebit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit, false)));
 489 
            lineRecap.put("DEBIT", (totalDebit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit, false)));
503 
            lineRecap.put("CREDIT", (totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalCredit, false)));
 490 
            lineRecap.put("CREDIT", (totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalCredit, false)));
504 
            lineRecap.put("SOLDE", (totalDebit - totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
 491 
            lineRecap.put("SOLDE", (totalDebit - totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
505 
            lineRecap.put("DATE", "");
 492 
            lineRecap.put("DATE", "");
506 
            lineRecap.put("JOURNAL", "");
 493 
            lineRecap.put("JOURNAL", "");
507 
            lineRecap.put("LIBELLE", "Global");
 494 
            lineRecap.put("LIBELLE", "Global");
508 
            style.put(tableauVals.size(), "Titre 1");
 495 
            style.put(tableauVals.size(), "Titre 1");
509 
            tableauVals.add(lineRecap);
 496 
            tableauVals.add(lineRecap);
510 
        }
 497 
        }
511 
 
 498 
 
512 
        sheetVals.put("TITRE_1", "GRAND LIVRE " + this.rowSociete.getString("TYPE") + " " + this.rowSociete.getString("NOM"));
 499 
        sheetVals.put("TITRE_1", "GRAND LIVRE " + this.rowSociete.getString("TYPE") + " " + this.rowSociete.getString("NOM"));
513 
        sheetVals.put("DATE_EDITION", new Date());
 500 
        sheetVals.put("DATE_EDITION", new Date());
514 
        sheetVals.put("TITRE_2", "Compte : " + this.compteDeb + " à " + this.compteEnd + ". Période du " + dateFormatEcr.format(this.dateDu) + " au " + dateFormatEcr.format(this.dateAu) + ".");
 501 
        sheetVals.put("TITRE_2", "Compte : " + this.compteDeb + " à " + this.compteEnd + ". Période du " + dateFormatEcr.format(this.dateDu) + " au " + dateFormatEcr.format(this.dateAu) + ".");
515 
        // on conserve la page d'origine du model
 502 
        // on conserve la page d'origine du model
516 
        // if (this.nbPage > 0) {
 503 
        // if (this.nbPage > 0) {
517 
        // this.nbPage--;
 504 
        // this.nbPage--;
518 
        // }
 505 
        // }
519 
 
 506 
 
520 
        Date end = new Date();
 507 
        Date end = new Date();
521 
        System.err.println("///////// TAKE " + (end.getTime() - d.getTime()) + " millisecondes TO CREATE MAP");
 508 
        System.err.println("///////// TAKE " + (end.getTime() - d.getTime()) + " millisecondes TO CREATE MAP");
522 
    }
 509 
    }
523 
 
 510 
 
524 
    private List<Integer> getListeCompteSolde() {
 511 
    private List<Integer> getListeCompteSolde() {
525 
        SQLSelect sel = new SQLSelect(base);
 512 
        SQLSelect sel = new SQLSelect();
526 
 
 - 
 
527 
        sel.addSelect(tableCompte.getField("ID"));
 513 
        sel.addSelect(tableEcriture.getField("ID_COMPTE_PCE"));
528 
        sel.addSelect(tableEcriture.getField("DEBIT"), "SUM");
 514 
        sel.addSelect(tableEcriture.getField("DEBIT"), "SUM");
529 
        sel.addSelect(tableEcriture.getField("CREDIT"), "SUM");
 515 
        sel.addSelect(tableEcriture.getField("CREDIT"), "SUM");
530 
 
 516 
 
531 
        Where w = getWhere(null);
 517 
        Where w = getWhere(null);
532 
 
 518 
 
533 
        sel.setWhere(w);
 519 
        sel.setWhere(w);
- 
 
 520 
        sel.addGroupBy(tableEcriture.getField("ID_COMPTE_PCE"));
534 
 
 521 
 
535 
        String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\"";
 522 
        String req = sel.asString();
536 
        System.err.println(req);
 523 
        System.err.println(req);
537 
        List<Object[]> l = (List) base.getDataSource().execute(req, new ArrayListHandler());
 524 
        List<Object[]> l = (List) base.getDataSource().execute(req, new ArrayListHandler());
538 
        List<Integer> list = new ArrayList<Integer>();
 525 
        List<Integer> list = new ArrayList<Integer>();
539 
        for (Object[] o : l) {
 526 
        for (Object[] o : l) {
540 
            long credit = 0;
 527 
            long credit = 0;
541 
            if (o[2] != null) {
 528 
            if (o[2] != null) {
542 
                credit = Long.valueOf(o[2].toString());
 529 
                credit = Long.valueOf(o[2].toString());
543 
            }
 530 
            }
544 
 
 531 
 
545 
            long debit = 0;
 532 
            long debit = 0;
546 
            if (o[1] != null) {
 533 
            if (o[1] != null) {
547 
                debit = Long.valueOf(o[1].toString());
 534 
                debit = Long.valueOf(o[1].toString());
548 
            }
 535 
            }
549 
 
 536 
 
550 
            int id = Integer.valueOf(o[0].toString());
 537 
            int id = Integer.valueOf(o[0].toString());
551 
            long solde = debit - credit;
 538 
            long solde = debit - credit;
552 
            if (solde == 0) {
 539 
            if (solde == 0) {
553 
                list.add(id);
 540 
                list.add(id);
554 
            }
 541 
            }
555 
        }
 542 
        }
556 
        return list;
 543 
        return list;
557 
    }
 544 
    }
558 
 
 545 
 
559 
    private Where getWhere(final List<Integer> lCompteSolde) {
 546 
    private Where getWhere(final List<Integer> lCompteSolde) {
560 
        Where w = (new Where(tableEcriture.getField("DATE"), GrandLivreSheetXML.this.dateDu, GrandLivreSheetXML.this.dateAu));
 547 
        Where w = (new Where(tableEcriture.getField("DATE"), GrandLivreSheetXML.this.dateDu, GrandLivreSheetXML.this.dateAu));
561 
 
 548 
 
562 
        if (GrandLivreSheetXML.this.compteDeb.equals(GrandLivreSheetXML.this.compteEnd)) {
 549 
        if (GrandLivreSheetXML.this.compteDeb.equals(GrandLivreSheetXML.this.compteEnd)) {
563 
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), "=", GrandLivreSheetXML.this.compteDeb));
 550 
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), "=", GrandLivreSheetXML.this.compteDeb));
564 
        } else {
 551 
        } else {
565 
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), (Object) GrandLivreSheetXML.this.compteDeb, (Object) GrandLivreSheetXML.this.compteEnd));
 552 
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), (Object) GrandLivreSheetXML.this.compteDeb, (Object) GrandLivreSheetXML.this.compteEnd));
566 
        }
 553 
        }
567 
        w = w.and(new Where(tableEcriture.getField("ID_JOURNAL"), "!=", idJrnlExclude));
 554 
        w = w.and(new Where(tableEcriture.getField("ID_JOURNAL"), "!=", idJrnlExclude));
568 
        w = w.and(new Where(tableEcriture.getField("ID_MOUVEMENT"), "=", tableMvt.getField("ID")));
 555 
        w = w.and(new Where(tableEcriture.getField("ID_MOUVEMENT"), "=", tableMvt.getField("ID")));
569 
 
 556 
 
570 
        if (GrandLivreSheetXML.this.lettrage == GrandLivreSheet.MODELETTREE) {
 557 
        if (GrandLivreSheetXML.this.lettrage == GrandLivreSheet.MODELETTREE) {
571 
            Object o = null;
 558 
            Object o = null;
572 
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "<>", o));
 559 
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "<>", o));
573 
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "!=", ""));
 560 
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "!=", ""));
574 
            w = w.and(new Where(tableEcriture.getField("DATE_LETTRAGE"), "<=", GrandLivreSheetXML.this.dateAu));
 561 
            w = w.and(new Where(tableEcriture.getField("DATE_LETTRAGE"), "<=", GrandLivreSheetXML.this.dateAu));
575 
        } else if (GrandLivreSheetXML.this.lettrage == GrandLivreSheet.MODENONLETTREE_PERIODE) {
 562 
        } else if (GrandLivreSheetXML.this.lettrage == GrandLivreSheet.MODENONLETTREE_PERIODE) {
576 
            Object o = null;
 563 
            Object o = null;
577 
            Where w2 = new Where(tableEcriture.getField("LETTRAGE"), "=", o);
 564 
            Where w2 = new Where(tableEcriture.getField("LETTRAGE"), "=", o);
578 
            Where wSTTC = new Where(tableEcriture.getField("DATE_LETTRAGE"), "<>", o);
 565 
            Where wSTTC = new Where(tableEcriture.getField("DATE_LETTRAGE"), "<>", o);
579 
            wSTTC = wSTTC.and(new Where(tableEcriture.getField("DATE_LETTRAGE"), ">", GrandLivreSheetXML.this.dateAu));
 566 
            wSTTC = wSTTC.and(new Where(tableEcriture.getField("DATE_LETTRAGE"), ">", GrandLivreSheetXML.this.dateAu));
580 
 
 567 
 
581 
            w2 = w2.or(wSTTC);
 568 
            w2 = w2.or(wSTTC);
582 
            w = w.and(w2.or(new Where(tableEcriture.getField("LETTRAGE"), "=", "")));
 569 
            w = w.and(w2.or(new Where(tableEcriture.getField("LETTRAGE"), "=", "")));
583 
        } else if (GrandLivreSheetXML.this.lettrage == GrandLivreSheet.MODENONLETTREE_ALL) {
 570 
        } else if (GrandLivreSheetXML.this.lettrage == GrandLivreSheet.MODENONLETTREE_ALL) {
584 
            Object o = null;
 571 
            Object o = null;
585 
            Where w2 = new Where(tableEcriture.getField("LETTRAGE"), "=", o);
 572 
            Where w2 = new Where(tableEcriture.getField("LETTRAGE"), "=", o);
586 
            w = w.and(w2.or(new Where(tableEcriture.getField("LETTRAGE"), "=", "")));
 573 
            w = w.and(w2.or(new Where(tableEcriture.getField("LETTRAGE"), "=", "")));
587 
        }
 574 
        }
588 
 
 575 
 
589 
        if (GrandLivreSheetXML.this.excludeCompteSolde) {
 576 
        if (GrandLivreSheetXML.this.excludeCompteSolde) {
590 
            System.err.println("Exclude compte");
 577 
            System.err.println("Exclude compte");
591 
 
 578 
 
592 
            if (lCompteSolde != null) {
 579 
            if (lCompteSolde != null) {
593 
                w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), lCompteSolde).not());
 580 
                w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), lCompteSolde).not());
594 
            }
 581 
            }
595 
        }
 582 
        }
596 
        w = w.and(new Where(tableEcriture.getField("NOM"), "NOT LIKE", "Fermeture du compte%"));
 583 
        w = w.and(new Where(tableEcriture.getField("NOM"), "NOT LIKE", "Fermeture du compte%"));
597 
 
 584 
 
598 
        if (!UserRightsManager.getCurrentUserRights().haveRight(ComptaUserRight.ACCES_NOT_RESCTRICTED_TO_411)) {
 585 
        if (!UserRightsManager.getCurrentUserRights().haveRight(ComptaUserRight.ACCES_NOT_RESCTRICTED_TO_411)) {
599 
            // TODO Show Restricted acces in UI
 586 
            // TODO Show Restricted acces in UI
600 
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), "LIKE", "411%"));
 587 
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), "LIKE", "411%"));
601 
        }
 588 
        }
602 
        return w;
 589 
        return w;
603 
    }
 590 
    }
604 
 
 591 
 
605 
    /**
 592 
    /**
606 
     * @param d date limite des cumuls
 593 
     * @param d date limite des cumuls
607 
     * @return Map<Integer id compte, Long solde(debit-credit)>
 594 
     * @return Map<Integer id compte, Long solde(debit-credit)>
608 
     */
 595 
     */
609 
    private Map<Integer, Long> getCumulsAnterieur(Date d, List<Integer> listCompteSolde) {
 596 
    private Map<Integer, Long> getCumulsAnterieur(Date d, List<Integer> listCompteSolde) {
610 
        SQLSelect sel = new SQLSelect(base);
 597 
        SQLSelect sel = new SQLSelect(base);
611 
 
 598 
 
612 
        sel.addSelect(tableEcriture.getField("ID_COMPTE_PCE"));
 599 
        sel.addSelect(tableEcriture.getField("ID_COMPTE_PCE"));
613 
        sel.addSelect(tableEcriture.getField("DEBIT"), "SUM");
 600 
        sel.addSelect(tableEcriture.getField("DEBIT"), "SUM");
614 
        sel.addSelect(tableEcriture.getField("CREDIT"), "SUM");
 601 
        sel.addSelect(tableEcriture.getField("CREDIT"), "SUM");
615 
        sel.addSelect(tableEcriture.getField("COMPTE_NUMERO"));
 602 
        sel.addSelect(tableEcriture.getField("COMPTE_NUMERO"));
616 
        // sel.addSelect(tableEcriture.getField("ID_MOUVEMENT"));
 603 
        // sel.addSelect(tableEcriture.getField("ID_MOUVEMENT"));
617 
        Where w = (new Where(tableEcriture.getField("DATE"), "<", d));
 604 
        Where w = (new Where(tableEcriture.getField("DATE"), "<", d));
618 
        w = w.and(new Where(tableEcriture.getField("ID_MOUVEMENT"), "=", tableMvt.getKey()));
 605 
        w = w.and(new Where(tableEcriture.getField("ID_MOUVEMENT"), "=", tableMvt.getKey()));
619 
 
 606 
 
620 
        if (this.compteDeb.equals(this.compteEnd)) {
 607 
        if (this.compteDeb.equals(this.compteEnd)) {
621 
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), "=", this.compteDeb));
 608 
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), "=", this.compteDeb));
622 
        } else {
 609 
        } else {
623 
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), (Object) this.compteDeb, (Object) this.compteEnd));
 610 
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), (Object) this.compteDeb, (Object) this.compteEnd));
624 
        }
 611 
        }
625 
 
 612 
 
626 
        if (this.lettrage == GrandLivreSheet.MODELETTREE) {
 613 
        if (this.lettrage == GrandLivreSheet.MODELETTREE) {
627 
            Object o = null;
 614 
            Object o = null;
628 
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "<>", o));
 615 
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "<>", o));
629 
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "!=", ""));
 616 
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "!=", ""));
630 
        } else {
 617 
        } else {
631 
            if (this.lettrage == GrandLivreSheet.MODENONLETTREE_ALL || this.lettrage == GrandLivreSheet.MODENONLETTREE_PERIODE) {
 618 
            if (this.lettrage == GrandLivreSheet.MODENONLETTREE_ALL || this.lettrage == GrandLivreSheet.MODENONLETTREE_PERIODE) {
632 
                Object o = null;
 619 
                Object o = null;
633 
                Where w2 = new Where(tableEcriture.getField("LETTRAGE"), "=", o);
 620 
                Where w2 = new Where(tableEcriture.getField("LETTRAGE"), "=", o);
634 
                w = w.and(w2.or(new Where(tableEcriture.getField("LETTRAGE"), "=", "")));
 621 
                w = w.and(w2.or(new Where(tableEcriture.getField("LETTRAGE"), "=", "")));
635 
            }
 622 
            }
636 
        }
 623 
        }
637 
 
 624 
 
638 
        w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), "=", tableCompte.getField("ID")));
 625 
        w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), "=", tableCompte.getField("ID")));
639 
        w = w.and(new Where(tableEcriture.getField("ID_JOURNAL"), "!=", idJrnlExclude));
 626 
        w = w.and(new Where(tableEcriture.getField("ID_JOURNAL"), "!=", idJrnlExclude));
640 
        if (listCompteSolde != null) {
 627 
        if (listCompteSolde != null) {
641 
            w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), listCompteSolde).not());
 628 
            w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), listCompteSolde).not());
642 
        }
 629 
        }
643 
 
 630 
 
644 
        sel.setWhere(w);
 631 
        sel.setWhere(w);
645 
 
 632 
 
646 
        String req = sel.asString() + " GROUP BY \"ECRITURE\".\"ID_COMPTE_PCE\", \"ECRITURE\".\"COMPTE_NUMERO\"";
 633 
        String req = sel.asString() + " GROUP BY \"ECRITURE\".\"ID_COMPTE_PCE\", \"ECRITURE\".\"COMPTE_NUMERO\"";
647 
        System.err.println(req);
 634 
        System.err.println(req);
648 
        List<Object[]> l = (List) base.getDataSource().execute(req, new ArrayListHandler());
 635 
        List<Object[]> l = (List) base.getDataSource().execute(req, new ArrayListHandler());
649 
        Map<Integer, Long> map = new HashMap<Integer, Long>();
 636 
        Map<Integer, Long> map = new HashMap<Integer, Long>();
650 
 
 637 
 
651 
        int idCptFourn = ComptePCESQLElement.getId("401", "Fournisseurs");
 638 
        int idCptFourn = ComptePCESQLElement.getId("401", "Fournisseurs");
652 
        int idCptClient = ComptePCESQLElement.getId("411", "Clients");
 639 
        int idCptClient = ComptePCESQLElement.getId("411", "Clients");
653 
 
 640 
 
654 
        for (Object[] o : l) {
 641 
        for (Object[] o : l) {
655 
 
 642 
 
656 
            long credit = 0;
 643 
            long credit = 0;
657 
            if (o[2] != null) {
 644 
            if (o[2] != null) {
658 
                credit = Long.valueOf(o[2].toString());
 645 
                credit = Long.valueOf(o[2].toString());
659 
            }
 646 
            }
660 
 
 647 
 
661 
            long debit = 0;
 648 
            long debit = 0;
662 
            if (o[1] != null) {
 649 
            if (o[1] != null) {
663 
                debit = Long.valueOf(o[1].toString());
 650 
                debit = Long.valueOf(o[1].toString());
664 
            }
 651 
            }
665 
 
 652 
 
666 
            int id = Integer.valueOf(o[0].toString());
 653 
            int id = Integer.valueOf(o[0].toString());
667 
            long solde = debit - credit;
 654 
            long solde = debit - credit;
668 
            map.put(id, solde);
 655 
            map.put(id, solde);
669 
            if (o[3] != null) {
 656 
            if (o[3] != null) {
670 
                String numero = o[3].toString();
 657 
                String numero = o[3].toString();
671 
                if (this.centralFourn && numero.startsWith("401")) {
 658 
                if (this.centralFourn && numero.startsWith("401")) {
672 
                    Long lS = map.get(idCptFourn);
 659 
                    Long lS = map.get(idCptFourn);
673 
                    if (lS != null) {
 660 
                    if (lS != null) {
674 
                        lS += solde;
 661 
                        lS += solde;
675 
                    } else {
 662 
                    } else {
676 
                        lS = new Long(solde);
 663 
                        lS = new Long(solde);
677 
                    }
 664 
                    }
678 
                    map.put(idCptFourn, lS);
 665 
                    map.put(idCptFourn, lS);
679 
                }
 666 
                }
680 
                if (this.centralClient && numero.startsWith("411")) {
 667 
                if (this.centralClient && numero.startsWith("411")) {
681 
                    Long lS = map.get(idCptClient);
 668 
                    Long lS = map.get(idCptClient);
682 
                    if (lS != null) {
 669 
                    if (lS != null) {
683 
                        lS += solde;
 670 
                        lS += solde;
684 
                    } else {
 671 
                    } else {
685 
                        lS = new Long(solde);
 672 
                        lS = new Long(solde);
686 
                    }
 673 
                    }
687 
                    map.put(idCptClient, lS);
 674 
                    map.put(idCptClient, lS);
688 
                }
 675 
                }
689 
            }
 676 
            }
690 
        }
 677 
        }
691 
 
 678 
 
692 
        return map;
 679 
        return map;
693 
    }
 680 
    }
694 
 
 681 
 
695 
    @Override
 682 
    @Override
696 
    public String getTemplateId() {
 683 
    public String getTemplateId() {
697 
        return TEMPLATE_ID;
 684 
        return TEMPLATE_ID;
698 
    }
 685 
    }
699 
 
 686 
 
700 
    public int getSize() {
 687 
    public int getSize() {
701 
        return size;
 688 
        return size;
702 
    }
 689 
    }
703 
}
 690 
}