OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 142 | 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.humanresources.payroll.report;
15
 
16
import org.openconcerto.erp.config.ComptaPropsConfiguration;
17
import org.openconcerto.erp.generationDoc.SheetInterface;
18
import org.openconcerto.erp.preferences.PrinterNXProps;
19
import org.openconcerto.sql.Configuration;
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;
177 ilm 24
import org.openconcerto.utils.StringUtils;
18 ilm 25
 
26
import java.text.DateFormat;
27
import java.util.Date;
28
import java.util.HashMap;
29
import java.util.List;
30
import java.util.Map;
31
 
32
import org.apache.commons.dbutils.handlers.ArrayListHandler;
33
 
34
public class LivrePayeSheet extends SheetInterface {
35
 
36
    // TODO Incorrect si aucune fiche valider
37
    private static int debutFill, endFill;
38
    private static int nbCol;
39
    private final static SQLTable tableSalarie = base.getTable("SALARIE");
40
    private final static SQLTable tableFichePaye = base.getTable("FICHE_PAYE");
41
    private final static SQLTable tableFichePayeElement = base.getTable("FICHE_PAYE_ELEMENT");
42
    private final static SQLTable tableMois = base.getTable("MOIS");
43
    private final static SQLTable tableRubCot = Configuration.getInstance().getBase().getTable("RUBRIQUE_COTISATION");
44
    private final static SQLTable tableRubNet = Configuration.getInstance().getBase().getTable("RUBRIQUE_NET");
45
    private final static SQLTable tableRubBrut = Configuration.getInstance().getBase().getTable("RUBRIQUE_BRUT");
46
 
142 ilm 47
    private final DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.MEDIUM);
18 ilm 48
    private int moisDu, moisAu;
49
    private String annee;
50
 
51
    public static void setSize(int debut, int fin, int col) {
52
        debutFill = debut;
53
        endFill = fin;
54
        nbCol = col;
55
    }
56
 
57
    static {
132 ilm 58
        setSize(9, 65, 6);
18 ilm 59
    }
60
 
25 ilm 61
    @Override
62
    protected String getYear() {
63
        return "";
18 ilm 64
    }
65
 
25 ilm 66
    public static final String TEMPLATE_ID = "Livre de paye";
67
    public static final String TEMPLATE_PROPERTY_NAME = "LocationLivrePaye";
68
 
18 ilm 69
    public LivrePayeSheet(int moisDu, int moisAu, String annee) {
70
        super();
71
        this.printer = PrinterNXProps.getInstance().getStringProperty("LivrePayePrinter");
72
        this.modele = "LivrePaye.ods";
73
        this.moisAu = moisAu;
74
        this.moisDu = moisDu;
75
        this.annee = annee;
76
 
77
        this.nbRowsPerPage = 67;
78
 
79
        createMap();
80
    }
81
 
82
    private void makeEntete(int row) {
83
        SQLRow rowSociete = ((ComptaPropsConfiguration) Configuration.getInstance()).getRowSociete();
84
        this.mCell.put("A" + row, rowSociete.getObject("NOM"));
132 ilm 85
        this.mCell.put("G" + row, "Edition du " + dateFormat.format(new Date()));
18 ilm 86
        // this.mCell.put("D" + (row + 2), "Impression Journaux");
87
        System.err.println("MAKE ENTETE");
88
    }
89
 
90
    private void makeBasPage(int row) {
91
        SQLRow rowMoisDu = tableMois.getRow(this.moisDu);
92
        SQLRow rowMoisAu = tableMois.getRow(this.moisAu);
93
 
94
        this.mCell.put("A" + row, "Période de " + rowMoisDu.getString("NOM") + " à " + rowMoisAu.getString("NOM") + " " + this.annee);
95
    }
96
 
25 ilm 97
    @Override
98
    public String getTemplateId() {
99
        return TEMPLATE_ID;
100
    }
101
 
18 ilm 102
    protected void createMap() {
103
 
104
        this.mapReplace = new HashMap();
105
        this.mCell = new HashMap();
106
        this.mapStyleRow = new HashMap();
107
 
108
        SQLSelect sel = new SQLSelect(base);
109
        sel.addSelect(tableFichePaye.getField("ID"));
110
        sel.addSelect(tableFichePayeElement.getField("ID"));
111
        sel.addSelect(tableSalarie.getField("ID"));
112
 
113
        Where w = (new Where(tableFichePayeElement.getField("ID_FICHE_PAYE"), "=", tableFichePaye.getField("ID")));
114
        Where w2 = (new Where(tableFichePaye.getField("ID_SALARIE"), "=", tableSalarie.getField("ID")));
115
        Where w3 = (new Where(tableFichePaye.getField("ID_MOIS"), new Integer(this.moisDu), new Integer(this.moisAu)));
116
        Where w4 = (new Where(tableFichePaye.getField("ANNEE"), "=", new Integer(this.annee)));
117
        Where w5 = (new Where(tableFichePaye.getField("VALIDE"), "=", Boolean.TRUE));
118
 
119
        sel.setWhere(w);
120
        sel.andWhere(w2);
121
        sel.andWhere(w3);
122
        sel.andWhere(w4);
123
        sel.andWhere(w5);
124
        String req = sel.asString();
125
 
126
        System.err.println(req);
127
 
128
        // Liste des rubriques de chaque salaries
129
        List l = (List) base.getDataSource().execute(req, new ArrayListHandler());
130
 
131
        // Association idSal, map Value(idRub, val)
132
        Map mapSalarieBrut = new HashMap();
133
        Map mapSalarieNet = new HashMap();
134
        Map mapSalarieCot = new HashMap();
135
 
136
        Map mapTotalCot = new HashMap();
137
        Map mapTotalNet = new HashMap();
138
        Map mapTotalbrut = new HashMap();
139
 
140
        Map mapRubriqueBrut = new HashMap();
141
        Map mapRubriqueNet = new HashMap();
142
        Map mapRubriqueCot = new HashMap();
143
        Map mapSal = new HashMap();
144
 
145
        // Cumuls des rubriques par salaries
146
        for (int i = 0; i < l.size(); i++) {
147
            Object[] tmp = (Object[]) l.get(i);
148
            // int idFiche = new Integer(tmp[0].toString()).intValue();
149
            int idFicheElt = Integer.parseInt(tmp[1].toString());
150
            int idSal = Integer.parseInt(tmp[2].toString());
151
 
152
            Map mapValue = new HashMap();
153
            Map mapTotal = new HashMap();
154
 
155
            // SQLRow rowFiche = tableFichePaye.getRow(idFiche);
156
            SQLRow rowFicheElt = tableFichePayeElement.getRow(idFicheElt);
157
 
158
            mapSal.put(new Integer(idSal), "");
159
 
177 ilm 160
            float montantTestTotal = 0;
161
            if (rowFicheElt.getObject("MONTANT_SAL_AJ") != null) {
162
                montantTestTotal += rowFicheElt.getFloat("MONTANT_SAL_AJ");
163
            }
164
            if (rowFicheElt.getObject("MONTANT_SAL_DED") != null) {
165
                montantTestTotal -= rowFicheElt.getFloat("MONTANT_SAL_DED");
166
            }
18 ilm 167
 
177 ilm 168
            if (rowFicheElt.getObject("MONTANT_PAT") != null) {
169
                montantTestTotal += rowFicheElt.getFloat("MONTANT_PAT");
170
            }
171
            if (montantTestTotal != 0) {
172
 
173
                if (rowFicheElt.getString("SOURCE").equalsIgnoreCase("RUBRIQUE_BRUT")) {
174
 
175
                    mapRubriqueBrut.put(new Integer(rowFicheElt.getInt("IDSOURCE")), "");
176
                    mapTotal = mapTotalbrut;
177
                    if (mapSalarieBrut.get(new Integer(idSal)) == null) {
178
                        mapSalarieBrut.put(new Integer(idSal), mapValue);
18 ilm 179
                    } else {
177 ilm 180
                        mapValue = (Map) mapSalarieBrut.get(new Integer(idSal));
18 ilm 181
                    }
182
                } else {
177 ilm 183
                    if (rowFicheElt.getString("SOURCE").equalsIgnoreCase("RUBRIQUE_COTISATION")) {
184
                        mapRubriqueCot.put(new Integer(rowFicheElt.getInt("IDSOURCE")), "");
185
                        mapTotal = mapTotalCot;
186
                        if (mapSalarieCot.get(new Integer(idSal)) == null) {
187
                            mapSalarieCot.put(new Integer(idSal), mapValue);
18 ilm 188
                        } else {
177 ilm 189
                            mapValue = (Map) mapSalarieCot.get(new Integer(idSal));
18 ilm 190
                        }
177 ilm 191
                    } else {
192
                        if (rowFicheElt.getString("SOURCE").equalsIgnoreCase("RUBRIQUE_NET")) {
193
                            mapRubriqueNet.put(new Integer(rowFicheElt.getInt("IDSOURCE")), "");
194
                            mapTotal = mapTotalNet;
195
                            if (mapSalarieNet.get(new Integer(idSal)) == null) {
196
                                mapSalarieNet.put(new Integer(idSal), mapValue);
197
                            } else {
198
                                mapValue = (Map) mapSalarieNet.get(new Integer(idSal));
199
                            }
200
                        }
18 ilm 201
                    }
202
                }
203
 
177 ilm 204
                if (rowFicheElt.getObject("MONTANT_SAL_AJ") != null) {
205
                    Object o = mapValue.get(new Integer(rowFicheElt.getInt("IDSOURCE")));
206
                    Object oTot = mapTotal.get(new Integer(rowFicheElt.getInt("IDSOURCE")));
18 ilm 207
 
177 ilm 208
                    float montant = (o == null) ? 0.0F : ((Float) o).floatValue();
209
                    float montantTotal = (oTot == null) ? 0.0F : ((Float) oTot).floatValue();
210
                    montant += rowFicheElt.getFloat("MONTANT_SAL_AJ");
211
                    montantTotal += rowFicheElt.getFloat("MONTANT_SAL_AJ");
18 ilm 212
 
177 ilm 213
                    mapValue.put(new Integer(rowFicheElt.getInt("IDSOURCE")), new Float(montant));
214
                    mapTotal.put(new Integer(rowFicheElt.getInt("IDSOURCE")), new Float(montantTotal));
215
                }
216
                if (rowFicheElt.getObject("MONTANT_SAL_DED") != null) {
217
                    Object o = mapValue.get(new Integer(rowFicheElt.getInt("IDSOURCE")));
218
                    Object oTot = mapTotal.get(new Integer(rowFicheElt.getInt("IDSOURCE")));
18 ilm 219
 
177 ilm 220
                    float montant = (o == null) ? 0.0F : ((Float) o).floatValue();
221
                    float montantTot = (oTot == null) ? 0.0F : ((Float) oTot).floatValue();
222
                    montant -= rowFicheElt.getFloat("MONTANT_SAL_DED");
223
                    montantTot -= rowFicheElt.getFloat("MONTANT_SAL_DED");
18 ilm 224
 
177 ilm 225
                    mapValue.put(new Integer(rowFicheElt.getInt("IDSOURCE")), new Float(montant));
226
                    mapTotal.put(new Integer(rowFicheElt.getInt("IDSOURCE")), new Float(montantTot));
227
                }
132 ilm 228
 
177 ilm 229
                if (rowFicheElt.getObject("MONTANT_PAT") != null) {
230
                    Object o = mapValue.get(new Integer(rowFicheElt.getInt("IDSOURCE")) + "_PAT");
231
                    Object oTot = mapTotal.get(new Integer(rowFicheElt.getInt("IDSOURCE")) + "_PAT");
132 ilm 232
 
177 ilm 233
                    float montant = (o == null) ? 0.0F : ((Float) o).floatValue();
234
                    float montantTotal = (oTot == null) ? 0.0F : ((Float) oTot).floatValue();
235
                    montant += rowFicheElt.getFloat("MONTANT_PAT");
236
                    montantTotal += rowFicheElt.getFloat("MONTANT_PAT");
132 ilm 237
 
177 ilm 238
                    mapValue.put(new Integer(rowFicheElt.getInt("IDSOURCE")) + "_PAT", new Float(montant));
239
                    mapTotal.put(new Integer(rowFicheElt.getInt("IDSOURCE")) + "_PAT", new Float(montantTotal));
240
                }
132 ilm 241
            }
18 ilm 242
        }
243
 
244
        // Dump
245
        /*
246
         * for (int j = 0; j < mapSalarieBrut.keySet().size(); j++) {
247
         * System.err.println(mapSalarieBrut.get(mapSalarieBrut.keySet().toArray()[j])); }
248
         */
249
 
250
        // Fill
251
        int posLine = 1;
252
        int firstLine = 1;
253
 
132 ilm 254
        int nbSalPerPage = 3;
18 ilm 255
        System.err.println("NB Sal = " + mapSal.keySet().size());
256
 
132 ilm 257
        System.err.println("NB Pages = " + Math.ceil((double) (mapSal.keySet().size()) / nbSalPerPage));
258
        for (int n = 0; n < Math.ceil((double) (mapSal.keySet().size() + 1) / nbSalPerPage); n++) {
259
 
18 ilm 260
            // entete
261
            makeEntete(posLine);
262
            posLine += (debutFill - 1);
263
 
132 ilm 264
            int numFirstSal = (n * nbSalPerPage);
18 ilm 265
 
266
            if (numFirstSal < mapSal.keySet().size()) {
267
                SQLRow rowSal = tableSalarie.getRow(((Integer) mapSal.keySet().toArray()[numFirstSal]).intValue());
132 ilm 268
                this.mCell.put("B" + (posLine - 3), rowSal.getObject("NOM"));
269
                this.mCell.put("B" + (posLine - 2), rowSal.getObject("PRENOM"));
18 ilm 270
            } else {
271
                if (numFirstSal == mapSal.keySet().size()) {
272
                    System.err.println("Cumuls B");
132 ilm 273
                    this.mCell.put("B" + (posLine - 3), "Cumuls");
274
                    this.mCell.put("B" + (posLine - 2), "");
18 ilm 275
                }
276
            }
277
            if (numFirstSal + 1 < mapSal.keySet().size()) {
278
                SQLRow rowSal = tableSalarie.getRow(((Integer) mapSal.keySet().toArray()[numFirstSal + 1]).intValue());
132 ilm 279
                this.mCell.put("D" + (posLine - 3), rowSal.getObject("NOM"));
280
                this.mCell.put("D" + (posLine - 2), rowSal.getObject("PRENOM"));
18 ilm 281
            } else {
282
                if (numFirstSal + 1 == mapSal.keySet().size()) {
283
                    System.err.println("Cumuls C");
132 ilm 284
                    this.mCell.put("D" + (posLine - 3), "Cumuls");
285
                    this.mCell.put("D" + (posLine - 2), "");
18 ilm 286
                }
287
            }
288
            if (numFirstSal + 2 < mapSal.keySet().size()) {
289
                SQLRow rowSal = tableSalarie.getRow(((Integer) mapSal.keySet().toArray()[numFirstSal + 2]).intValue());
132 ilm 290
                this.mCell.put("F" + (posLine - 3), rowSal.getObject("NOM"));
291
                this.mCell.put("F" + (posLine - 2), rowSal.getObject("PRENOM"));
18 ilm 292
            } else {
293
                if (numFirstSal + 2 == mapSal.keySet().size()) {
294
                    System.err.println("Cumuls D");
132 ilm 295
                    this.mCell.put("F" + (posLine - 3), "Cumuls");
296
                    this.mCell.put("F" + (posLine - 2), "");
18 ilm 297
                }
298
            }
299
            for (int i = 0; i < mapRubriqueBrut.keySet().size(); i++) {
300
 
301
                int idRub = ((Number) mapRubriqueBrut.keySet().toArray()[i]).intValue();
302
                SQLRow rowRub = tableRubBrut.getRow(idRub);
303
 
177 ilm 304
                this.mCell.put("A" + posLine, StringUtils.limitLength(rowRub.getString("NOM"), 55));
18 ilm 305
 
132 ilm 306
                this.mCell.put("B" + posLine, fillLine(mapSalarieBrut, idRub, mapSal, numFirstSal, mapTotalbrut, false));
307
                this.mCell.put("C" + posLine, fillLine(mapSalarieBrut, idRub, mapSal, numFirstSal, mapTotalbrut, true));
308
                this.mCell.put("D" + posLine, fillLine(mapSalarieBrut, idRub, mapSal, numFirstSal + 1, mapTotalbrut, false));
309
                this.mCell.put("E" + posLine, fillLine(mapSalarieBrut, idRub, mapSal, numFirstSal + 1, mapTotalbrut, true));
310
                this.mCell.put("F" + posLine, fillLine(mapSalarieBrut, idRub, mapSal, numFirstSal + 2, mapTotalbrut, false));
311
                this.mCell.put("G" + posLine, fillLine(mapSalarieBrut, idRub, mapSal, numFirstSal + 2, mapTotalbrut, true));
18 ilm 312
                posLine++;
313
            }
314
 
315
            for (int i = 0; i < mapRubriqueCot.keySet().size(); i++) {
316
 
317
                int idRub = ((Number) mapRubriqueCot.keySet().toArray()[i]).intValue();
318
                SQLRow rowRub = tableRubCot.getRow(idRub);
319
 
177 ilm 320
                this.mCell.put("A" + posLine, StringUtils.limitLength(rowRub.getString("NOM"), 55));
18 ilm 321
 
132 ilm 322
                this.mCell.put("B" + posLine, fillLine(mapSalarieCot, idRub, mapSal, numFirstSal, mapTotalCot, false));
323
                this.mCell.put("C" + posLine, fillLine(mapSalarieCot, idRub, mapSal, numFirstSal, mapTotalCot, true));
324
                this.mCell.put("D" + posLine, fillLine(mapSalarieCot, idRub, mapSal, numFirstSal + 1, mapTotalCot, false));
325
                this.mCell.put("E" + posLine, fillLine(mapSalarieCot, idRub, mapSal, numFirstSal + 1, mapTotalCot, true));
326
                this.mCell.put("F" + posLine, fillLine(mapSalarieCot, idRub, mapSal, numFirstSal + 2, mapTotalCot, false));
327
                this.mCell.put("G" + posLine, fillLine(mapSalarieCot, idRub, mapSal, numFirstSal + 2, mapTotalCot, true));
18 ilm 328
 
329
                posLine++;
330
            }
331
 
332
            for (int i = 0; i < mapRubriqueNet.keySet().size(); i++) {
333
 
334
                int idRub = ((Number) mapRubriqueNet.keySet().toArray()[i]).intValue();
335
                SQLRow rowRub = tableRubNet.getRow(idRub);
336
 
177 ilm 337
                this.mCell.put("A" + posLine, StringUtils.limitLength(rowRub.getString("NOM"), 55));
18 ilm 338
 
132 ilm 339
                this.mCell.put("B" + posLine, fillLine(mapSalarieNet, idRub, mapSal, numFirstSal, mapTotalNet, false));
340
                this.mCell.put("C" + posLine, fillLine(mapSalarieNet, idRub, mapSal, numFirstSal, mapTotalNet, true));
341
                this.mCell.put("D" + posLine, fillLine(mapSalarieNet, idRub, mapSal, numFirstSal + 1, mapTotalNet, false));
342
                this.mCell.put("E" + posLine, fillLine(mapSalarieNet, idRub, mapSal, numFirstSal + 1, mapTotalNet, true));
343
                this.mCell.put("F" + posLine, fillLine(mapSalarieNet, idRub, mapSal, numFirstSal + 2, mapTotalNet, false));
344
                this.mCell.put("G" + posLine, fillLine(mapSalarieNet, idRub, mapSal, numFirstSal + 2, mapTotalNet, true));
18 ilm 345
 
346
                posLine++;
347
            }
348
 
349
            // pied de page
350
            posLine = firstLine + endFill - 1;
351
            posLine += 2;
352
            makeBasPage(posLine);
353
 
354
            posLine++;
355
            firstLine = posLine;
356
        }
357
 
132 ilm 358
        this.nbPage = new Double(Math.ceil((double) (mapSal.keySet().size() + 1) / (nbSalPerPage))).intValue();
18 ilm 359
 
360
        System.err.println("Nombre de page " + this.nbPage);
361
 
362
        // on conserve la page d'origine du model
363
 
364
        if (this.nbPage > 0) {
365
            this.nbPage--;
366
        }
367
    }
368
 
132 ilm 369
    private Object fillLine(Map mapSalRub, int idRub, Map mapSal, int numSal, Map mapTotal, boolean pat) {
18 ilm 370
 
371
        Object value = null;
372
        if (numSal < mapSal.keySet().size()) {
373
            Map m = (Map) mapSalRub.get(mapSal.keySet().toArray()[numSal]);
374
 
375
            value = new Float(0);
132 ilm 376
            Object key = (pat ? new Integer(idRub) + "_PAT" : new Integer(idRub));
377
            if (m != null && m.get(key) != null) {
378
                value = m.get(key);
18 ilm 379
            }
380
        } else {
132 ilm 381
            Object key = (pat ? new Integer(idRub) + "_PAT" : new Integer(idRub));
18 ilm 382
            if (numSal == mapSal.keySet().size()) {
132 ilm 383
                value = mapTotal.get(key);
18 ilm 384
            }
385
        }
386
        return value;
387
    }
388
}