OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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

Rev Author Line No. Line
18 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.erp.core.humanresources.payroll.report;
15
 
16
import org.openconcerto.erp.config.ComptaPropsConfiguration;
25 ilm 17
import org.openconcerto.erp.generationDoc.DocumentLocalStorageManager;
18 ilm 18
import org.openconcerto.erp.generationDoc.SheetInterface;
19
import org.openconcerto.erp.generationDoc.SpreadSheetGeneratorGestComm;
20
import org.openconcerto.erp.preferences.PrinterNXProps;
21
import org.openconcerto.map.model.Ville;
22
import org.jopendocument.link.Component;
23
import org.jopendocument.link.OOConnexion;
24
import org.openconcerto.sql.Configuration;
25
import org.openconcerto.sql.model.SQLRow;
26
import org.openconcerto.sql.model.SQLSelect;
27
import org.openconcerto.sql.model.SQLTable;
28
import org.openconcerto.utils.ExceptionHandler;
29
 
30
import java.io.File;
31
import java.text.DateFormat;
32
import java.util.Calendar;
33
import java.util.Date;
34
import java.util.HashMap;
35
import java.util.Iterator;
36
import java.util.List;
37
import java.util.Map;
38
 
39
import javax.swing.JOptionPane;
40
import javax.swing.SwingUtilities;
41
 
42
import org.apache.commons.dbutils.handlers.ArrayListHandler;
43
 
44
public class FichePayeSheet extends SheetInterface {
45
    // TODO Ajouter un champ DIF dans le modele pour le remplir a la main --> Droit individuel à la
46
    // formation
47
    private final static SQLTable tableFiche = base.getTable("FICHE_PAYE");
48
    private final static SQLTable tableFicheElt = base.getTable("FICHE_PAYE_ELEMENT");
49
    private final static SQLTable tableMois = base.getTable("MOIS");
50
    private final static SQLTable tableAdresse = base.getTable("ADRESSE");
51
    private final static SQLTable tableAdresseCommon = Configuration.getInstance().getBase().getTable("ADRESSE_COMMON");
52
    private final static SQLTable tableSalarie = base.getTable("SALARIE");
53
    private final static SQLTable tableEtatCivil = base.getTable("ETAT_CIVIL");
54
    private final static SQLTable tableInfosPaye = base.getTable("INFOS_SALARIE_PAYE");
55
    private final static SQLTable tableReglementPaye = base.getTable("REGLEMENT_PAYE");
56
    private final static SQLTable tableContrat = base.getTable("CONTRAT_SALARIE");
57
    private final static SQLTable tableModeRegl = base.getTable("MODE_REGLEMENT_PAYE");
58
    private final static SQLTable tableCumulsConges = base.getTable("CUMULS_CONGES");
59
    private final static SQLTable tableCumulsPaye = base.getTable("CUMULS_PAYE");
60
    private final static SQLTable tableVarPeriode = base.getTable("VARIABLE_SALARIE");
61
    private final static SQLTable tableConventionC = base.getTable("IDCC");
62
    private final static DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.SHORT);
63
 
64
    private Map styleMapRow;
65
 
66
    public Map getStyleMapRow() {
67
        return this.styleMapRow;
68
    }
69
 
70
    public FichePayeSheet(int idFiche) {
71
        super(idFiche, tableFiche);
72
        init();
73
    }
74
 
75
    public FichePayeSheet(SQLRow rowFiche) {
76
        super(rowFiche);
77
        init();
78
    }
79
 
80
    // Nom du fichier généré
81
    public static String getFileName(int id, int type) {
82
        return getFileName(tableFiche.getRow(id), type);
83
    }
84
 
85
    // génération d'une fiche de paye
86
    public static void generation(int id) {
87
        generation(tableFiche.getRow(id));
88
    }
89
 
90
    public static void generation(SQLRow row) {
91
        generation(row, true);
92
    }
93
 
94
    public static void generation(SQLRow row, boolean visu) {
95
        FichePayeSheet fSheet = new FichePayeSheet(row.getID());
96
        new SpreadSheetGeneratorGestComm(fSheet, FichePayeSheet.getFileName(row, FichePayeSheet.typeNoExtension), false, visu);
97
    }
98
 
99
    // impression d'une fiche de paye
100
    public static void impression(int id) {
101
        impression(tableFiche.getRow(id));
102
    }
103
 
104
    public static void impression(SQLRow row) {
105
        final File f = getFile(row, typeOO);
106
        if (f.exists()) {
107
            try {
108
                final OOConnexion ooConnexion = ComptaPropsConfiguration.getOOConnexion();
109
                if (ooConnexion == null) {
110
                    return;
111
                }
112
                final Component doc = ooConnexion.loadDocument(f, true);
113
 
114
                Map<String, Object> map = new HashMap<String, Object>();
115
                map.put("Name", PrinterNXProps.getInstance().getStringProperty("FichePayePrinter"));
116
                doc.printDocument(map);
117
                doc.close();
118
            } catch (Exception e) {
119
                // TODO Auto-generated catch block
120
                e.printStackTrace();
121
                ExceptionHandler.handle("Impossible de charger le document OpenOffice", e);
122
            }
123
        }
124
    }
125
 
126
    // visualisation d'une fiche
127
    public static void visualisation(SQLRow r, int type) {
128
        final File f = getFile(r, type);
129
        if (f.exists()) {
130
            try {
131
                final OOConnexion ooConnexion = ComptaPropsConfiguration.getOOConnexion();
132
                if (ooConnexion == null) {
133
                    return;
134
                }
135
                ooConnexion.loadDocument(f, false);
136
            } catch (Exception e) {
137
                // TODO Auto-generated catch block
138
                e.printStackTrace();
139
                ExceptionHandler.handle("Impossible de charger le document OpenOffice", e);
140
            }
141
        }
142
    }
143
 
144
    public static File getFile(SQLRow r, int type) {
145
        return new File(getLocation(r, type), getFileName(r.getID(), type));
146
    }
147
 
148
    public static String getFileName(SQLRow r, int type) {
149
 
150
        SQLRow rowSal = tableSalarie.getRow(r.getInt("ID_SALARIE"));
151
        SQLRow rowMois = tableMois.getRow(r.getInt("ID_MOIS"));
152
        if (type == FichePayeSheet.typeOO) {
153
            return ("FichePaye_" + rowSal.getString("CODE") + "_" + rowMois.getString("NOM") + "_" + r.getString("ANNEE") + ".ods");
154
        } else {
155
            if (type == FichePayeSheet.typePDF) {
156
                return ("FichePaye_" + rowSal.getString("CODE") + "_" + rowMois.getString("NOM") + "_" + r.getString("ANNEE") + ".pdf");
157
            } else {
158
                return ("FichePaye_" + rowSal.getString("CODE") + "_" + rowMois.getString("NOM") + "_" + r.getString("ANNEE"));
159
            }
160
        }
161
    }
162
 
163
    // Emplacement des fichiers générés
164
    public static String getLocation(int id, int type) {
165
        return getLocation(tableFiche.getRow(id), type);
166
    }
167
 
25 ilm 168
    public static final String TEMPLATE_ID = "Fiche de paye";
169
    public static final String TEMPLATE_PROPERTY_NAME = "LocationFichePaye";
18 ilm 170
 
25 ilm 171
    @Override
172
    public String getTemplateId() {
173
        return TEMPLATE_ID;
18 ilm 174
    }
175
 
25 ilm 176
    @Override
177
    protected String getYear() {
178
        // TODO Auto-generated method stub
179
        return this.row.getString("ANNEE");
180
    }
181
 
18 ilm 182
    public static String getLocation(SQLRow r, int type) {
25 ilm 183
        DocumentLocalStorageManager storage = DocumentLocalStorageManager.getInstance();
184
        String path;
185
        if (type == FichePayeSheet.typeOO) {
186
            path = storage.getDocumentOutputDirectory(TEMPLATE_ID).getAbsolutePath();
187
        } else {
188
            path = storage.getPDFOutputDirectory(TEMPLATE_ID).getAbsolutePath();
189
        }
18 ilm 190
 
25 ilm 191
        return path + File.separator + r.getString("ANNEE");
18 ilm 192
    }
193
 
194
    private void init() {
195
        this.modele = "FichePaye.ods";
196
        this.printer = PrinterNXProps.getInstance().getStringProperty("FichePayePrinter");
197
    }
198
 
199
    protected void createMap() {
200
 
201
        this.styleMapRow = new HashMap();
202
        this.mapReplace = new HashMap();
203
 
204
        this.mCell = new HashMap();
205
 
206
        // Infos societe
207
        SQLRow rowSociete = ((ComptaPropsConfiguration) Configuration.getInstance()).getRowSociete();
208
        this.mCell.put("B1", rowSociete.getObject("TYPE") + " " + rowSociete.getObject("NOM"));
209
        SQLRow rowAdrSociete = tableAdresseCommon.getRow(rowSociete.getInt("ID_ADRESSE_COMMON"));
210
        this.mCell.put("B2", rowAdrSociete.getObject("RUE"));
211
        this.mCell.put("B3", getVilleCP(rowAdrSociete.getString("VILLE")) + " " + getVille(rowAdrSociete.getString("VILLE")));
212
 
213
        this.mCell.put("D5", rowSociete.getObject("NUM_SIRET"));
214
        this.mCell.put("D6", rowSociete.getObject("NUM_APE"));
215
        this.mapReplace.put("D8", rowSociete.getObject("NUMERO_URSSAF"));
216
 
217
        // Infos Salarie
218
        SQLRow rowSal = tableSalarie.getRow(this.row.getInt("ID_SALARIE"));
219
        SQLRow rowEtatCivil = tableEtatCivil.getRow(rowSal.getInt("ID_ETAT_CIVIL"));
220
        this.mCell.put("G8", rowSal.getObject("NOM") + " " + rowSal.getObject("PRENOM"));
221
        SQLRow rowAdrSal = tableAdresse.getRow(rowEtatCivil.getInt("ID_ADRESSE"));
222
        this.mCell.put("G9", rowAdrSal.getObject("RUE"));
223
        this.mCell.put("G11", getVilleCP(rowAdrSal.getString("VILLE")) + " " + getVille(rowAdrSal.getString("VILLE")));
224
 
225
        this.mCell.put("D13", rowEtatCivil.getObject("NUMERO_SS"));
226
 
227
        SQLRow rowInfosPaye = tableInfosPaye.getRow(rowSal.getInt("ID_INFOS_SALARIE_PAYE"));
228
        SQLRow rowContrat = tableContrat.getRow(rowInfosPaye.getInt("ID_CONTRAT_SALARIE"));
229
 
230
        if (this.row.getString("NATURE_EMPLOI").trim().length() == 0) {
231
            this.mCell.put("D14", rowContrat.getObject("NATURE"));
232
        } else {
233
            this.mCell.put("D14", this.row.getString("NATURE_EMPLOI"));
234
        }
235
 
236
        SQLRow rowCC;
237
        if (this.row.getInt("ID_IDCC") > 1) {
238
            rowCC = tableConventionC.getRow(this.row.getInt("ID_IDCC"));
239
        } else {
240
            rowCC = tableConventionC.getRow(rowInfosPaye.getInt("ID_IDCC"));
241
        }
242
        this.mCell.put("D15", rowCC.getString("NOM"));
243
 
244
        // Bulletin du
245
        // Bulletin de paie du
246
        Date du = (Date) this.row.getObject("DU");
247
        Date au = (Date) this.row.getObject("AU");
248
        this.mCell.put("F1", "Bulletin de paie du " + dateFormat.format(du) + " au " + dateFormat.format(au));
249
 
250
        // Paiement le
251
        SQLRow rowRegl;
252
        if (this.row.getInt("ID_REGLEMENT_PAYE") <= 1) {
253
            rowRegl = tableReglementPaye.getRow(rowSal.getInt("ID_REGLEMENT_PAYE"));
254
        } else {
255
            rowRegl = tableReglementPaye.getRow(this.row.getInt("ID_REGLEMENT_PAYE"));
256
        }
257
        SQLRow rowModeRegl = tableModeRegl.getRow(rowRegl.getInt("ID_MODE_REGLEMENT_PAYE"));
258
 
259
        Calendar c = Calendar.getInstance();
260
 
261
        c.set(Calendar.MONTH, this.row.getInt("ID_MOIS") - 2);
262
        c.set(Calendar.YEAR, Integer.parseInt(this.row.getString("ANNEE")));
263
 
264
        if (rowRegl.getInt("LE") != 31) {
265
 
266
            c.set(Calendar.MONTH, c.get(Calendar.MONTH) + 1);
267
        }
268
 
269
        int max = c.getActualMaximum(Calendar.DAY_OF_MONTH);
270
        int day = Math.min(rowRegl.getInt("LE"), max);
271
 
272
        c.set(Calendar.DAY_OF_MONTH, day);
273
 
274
        this.mCell.put("H3", dateFormat.format(c.getTime()));
275
        this.mCell.put("I3", "Par " + rowModeRegl.getObject("NOM"));
276
 
277
        // Congés
278
        // "G3";
279
        SQLRow rowConges;
280
        if (this.row.getInt("ID_CUMULS_CONGES") <= 1) {
281
            rowConges = tableCumulsConges.getRow(rowSal.getInt("ID_CUMULS_CONGES"));
282
        } else {
283
            rowConges = tableCumulsConges.getRow(this.row.getInt("ID_CUMULS_CONGES"));
284
        }
285
 
286
        SQLRow rowVarSal;
287
        if (this.row.getInt("ID_VARIABLE_SALARIE") <= 1) {
288
            rowVarSal = tableVarPeriode.getRow(rowSal.getInt("ID_VARIABLE_SALARIE"));
289
        } else {
290
            rowVarSal = tableVarPeriode.getRow(this.row.getInt("ID_VARIABLE_SALARIE"));
291
        }
292
 
293
        float congesPris = rowVarSal.getFloat("CONGES_PRIS");
294
        float congesRestant = rowConges.getFloat("RESTANT") - congesPris;
295
        float congesAcquis = rowConges.getFloat("ACQUIS") + this.row.getFloat("CONGES_ACQUIS");
296
        this.mCell.put("G14", new Float(congesPris));
297
        this.mCell.put("H14", new Float(congesRestant));
298
        this.mCell.put("I14", new Float(congesAcquis));
299
 
300
        // Element Devis
301
        SQLSelect selElt = new SQLSelect(base);
302
        selElt.addSelect(tableFicheElt.getField("ID"));
303
        selElt.setWhere("FICHE_PAYE_ELEMENT.ID_FICHE_PAYE", "=", this.row.getID());
304
 
305
        String req = selElt.asString() + " ORDER BY \"FICHE_PAYE_ELEMENT\".\"POSITION\"";
306
        List l = (List) base.getDataSource().execute(req, new ArrayListHandler());
307
        int pos = 20;
308
        for (Iterator i = l.iterator(); i.hasNext();) {
309
            Object[] o = (Object[]) i.next();
310
            SQLRow rowTmp = tableFicheElt.getRow(Integer.parseInt(o[0].toString()));
311
 
312
            if (rowTmp.getBoolean("IMPRESSION") && rowTmp.getBoolean("IN_PERIODE")) {
313
 
314
                Object nomTmp = rowTmp.getObject("NOM");
315
                this.mCell.put("B" + pos, nomTmp);
316
 
317
                // Base
318
                Object baseTmp = rowTmp.getObject("NB_BASE");
319
 
320
                if (baseTmp != null) {
321
                    float base = ((Float) baseTmp).floatValue();
322
                    if (base != 0) {
323
                        this.mCell.put("E" + pos, baseTmp);
324
                    } else {
325
                        this.mCell.put("E" + pos, "");
326
                    }
327
                } else {
328
                    this.mCell.put("E" + pos, baseTmp);
329
                }
330
 
331
                // Taux Sal
332
                Object tauxSalTmp = rowTmp.getObject("TAUX_SAL");
333
 
334
                if (tauxSalTmp != null) {
335
                    float tauxSal = ((Float) tauxSalTmp).floatValue();
336
                    if (tauxSal != 0) {
337
                        this.mCell.put("F" + pos, tauxSalTmp);
338
                    } else {
339
                        this.mCell.put("F" + pos, "");
340
                    }
341
                } else {
342
                    this.mCell.put("F" + pos, tauxSalTmp);
343
                }
344
 
345
                // Montant Sal Aj
346
                Object montantSalAjTmp = rowTmp.getObject("MONTANT_SAL_AJ");
347
                if (montantSalAjTmp != null) {
348
                    float montantSalAj = ((Float) montantSalAjTmp).floatValue();
349
                    if (montantSalAj != 0) {
350
                        this.mCell.put("G" + pos, montantSalAjTmp);
351
                    } else {
352
                        this.mCell.put("G" + pos, "");
353
                    }
354
                } else {
355
                    this.mCell.put("G" + pos, montantSalAjTmp);
356
                }
357
 
358
                // Montant Sal ded
359
                Object montantSalDedTmp = rowTmp.getObject("MONTANT_SAL_DED");
360
                if (montantSalDedTmp != null) {
361
                    float montantSalDed = ((Float) montantSalDedTmp).floatValue();
362
                    if (montantSalDed != 0) {
363
                        this.mCell.put("H" + pos, montantSalDedTmp);
364
                    } else {
365
                        this.mCell.put("H" + pos, "");
366
                    }
367
                } else {
368
                    this.mCell.put("H" + pos, montantSalDedTmp);
369
                }
370
 
371
                // Taux Pat
372
                Object tauxPatTmp = rowTmp.getObject("TAUX_PAT");
373
                if (tauxPatTmp != null) {
374
                    float tauxPat = ((Float) tauxPatTmp).floatValue();
375
                    if (tauxPat != 0) {
376
                        this.mCell.put("I" + pos, tauxPatTmp);
377
                    } else {
378
                        this.mCell.put("I" + pos, "");
379
                    }
380
                } else {
381
                    this.mCell.put("I" + pos, tauxPatTmp);
382
                }
383
 
384
                // Montant Pat
385
                Object montantPatTmp = rowTmp.getObject("MONTANT_PAT");
386
                if (montantPatTmp != null) {
387
                    float montantPat = ((Float) montantPatTmp).floatValue();
388
                    if (montantPat != 0) {
389
                        this.mCell.put("J" + pos, montantPatTmp);
390
                    } else {
391
                        this.mCell.put("J" + pos, "");
392
                    }
393
                } else {
394
                    this.mCell.put("J" + pos, montantPatTmp);
395
                }
396
 
397
                if (rowTmp.getString("SOURCE").equalsIgnoreCase("RUBRIQUE_COMM")) {
398
                    this.mapStyleRow.put(new Integer(pos), "Titre 1");
399
                } else {
400
                    this.mapStyleRow.put(new Integer(pos), "Normal");
401
                }
402
 
403
                pos++;
404
            }
405
        }
406
 
407
        // Totaux
408
        float netApayerCumul = this.row.getFloat("NET_A_PAYER");
409
        float salBrutCumul = this.row.getFloat("SAL_BRUT");
410
        float cotSalCumul = this.row.getFloat("COT_SAL");
411
        float cotPatCumul = this.row.getFloat("COT_PAT");
412
        float netImpCumul = this.row.getFloat("NET_IMP");
413
        this.mCell.put("I61", this.row.getObject("NET_A_PAYER"));
414
        this.mCell.put("D61", this.row.getObject("SAL_BRUT"));
415
        this.mCell.put("E61", this.row.getObject("COT_SAL"));
416
        this.mCell.put("F61", this.row.getObject("COT_PAT"));
417
        this.mCell.put("H61", this.row.getObject("NET_IMP"));
418
 
419
        SQLRow rowCumulsPaye;
420
 
421
        if (this.row.getInt("ID_CUMULS_PAYE") == 1) {
422
            rowCumulsPaye = tableCumulsPaye.getRow(rowSal.getInt("ID_CUMULS_PAYE"));
423
        } else {
424
            rowCumulsPaye = tableCumulsPaye.getRow(this.row.getInt("ID_CUMULS_PAYE"));
425
        }
426
 
427
        netApayerCumul += rowCumulsPaye.getFloat("NET_A_PAYER_C");
428
        salBrutCumul += rowCumulsPaye.getFloat("SAL_BRUT_C");
429
        cotSalCumul += rowCumulsPaye.getFloat("COT_SAL_C");
430
        cotPatCumul += rowCumulsPaye.getFloat("COT_PAT_C");
431
        netImpCumul += rowCumulsPaye.getFloat("NET_IMP_C");
432
 
433
        this.mCell.put("D62", new Float(salBrutCumul));
434
        this.mCell.put("E62", new Float(cotSalCumul));
435
        this.mCell.put("F62", new Float(cotPatCumul));
436
        this.mCell.put("H62", new Float(netImpCumul));
437
    }
438
 
439
    private static String getVille(final String name) {
440
 
441
        Ville ville = Ville.getVilleFromVilleEtCode(name);
442
        if (ville == null) {
443
            SwingUtilities.invokeLater(new Runnable() {
444
                public void run() {
445
                    JOptionPane.showMessageDialog(null, "La ville " + "\"" + name + "\"" + " est introuvable! Veuillez corriger l'erreur!");
446
                }
447
            });
448
            return null;
449
        }
450
        return ville.getName();
451
    }
452
 
453
    private static String getVilleCP(String name) {
454
        Ville ville = Ville.getVilleFromVilleEtCode(name);
455
        if (ville == null) {
456
 
457
            return null;
458
        }
459
        return ville.getCodepostal();
460
    }
461
 
462
}