OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 156 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
91 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
 
144 ilm 16
import org.openconcerto.erp.core.common.element.StyleSQLElement;
91 ilm 17
import org.openconcerto.erp.generationDoc.AbstractSheetXMLWithDate;
149 ilm 18
import org.openconcerto.erp.preferences.PayeGlobalPreferencePanel;
91 ilm 19
import org.openconcerto.erp.preferences.PrinterNXProps;
20
import org.openconcerto.sql.Configuration;
21
import org.openconcerto.sql.model.SQLRow;
144 ilm 22
import org.openconcerto.sql.model.SQLRowAccessor;
23
import org.openconcerto.sql.model.SQLRowListRSH;
24
import org.openconcerto.sql.model.SQLRowValues;
25
import org.openconcerto.sql.model.SQLSelect;
26
import org.openconcerto.sql.model.SQLTable;
149 ilm 27
import org.openconcerto.sql.preferences.SQLPreferences;
144 ilm 28
import org.openconcerto.utils.cc.ITransformer;
91 ilm 29
 
144 ilm 30
import java.math.BigDecimal;
31
import java.util.ArrayList;
142 ilm 32
import java.util.Calendar;
144 ilm 33
import java.util.HashMap;
34
import java.util.HashSet;
35
import java.util.List;
36
import java.util.Map;
37
import java.util.Set;
142 ilm 38
 
144 ilm 39
import javax.swing.JOptionPane;
40
import javax.swing.SwingUtilities;
41
 
91 ilm 42
public class FichePayeSheetXML extends AbstractSheetXMLWithDate {
43
 
149 ilm 44
    public static final String TEMPLATE_ID = "FichePayeSimplifiee";
91 ilm 45
    public static final String TEMPLATE_PROPERTY_NAME = "LocationFichePaye";
144 ilm 46
    final Map<Integer, String> cotisationSimplifieeLink = new HashMap<>();
91 ilm 47
 
144 ilm 48
    public FichePayeSheetXML(final SQLRow row) {
91 ilm 49
        super(row);
50
        this.printer = PrinterNXProps.getInstance().getStringProperty("FichePayePrinter");
51
        this.elt = Configuration.getInstance().getDirectory().getElement("FICHE_PAYE");
149 ilm 52
        SQLPreferences prefs = new SQLPreferences(elt.getTable().getDBRoot());
53
        boolean prefBulletinSimpl = !prefs.getBoolean(PayeGlobalPreferencePanel.NOT_PAYE_SIMPL, Boolean.FALSE);
54
 
144 ilm 55
        if (prefBulletinSimpl) {
56
            SQLSelect sel = new SQLSelect();
57
            sel.addSelect(row.getTable().getDBRoot().findTable("RUBRIQUE_COTISATION").getField("LIGNE_PAYE_SIMPLIFIEE"));
58
            sel.addSelect(row.getTable().getDBRoot().findTable("RUBRIQUE_COTISATION").getKey());
59
            List<SQLRow> rubCotis = SQLRowListRSH.execute(sel);
91 ilm 60
 
144 ilm 61
            for (SQLRow sqlRow : rubCotis) {
62
                cotisationSimplifieeLink.put(sqlRow.getID(), sqlRow.getString("LIGNE_PAYE_SIMPLIFIEE"));
63
            }
64
 
65
            final String codeStatu = row.getForeign("ID_SALARIE").getForeign("ID_INFOS_SALARIE_PAYE").getForeign("ID_CONTRAT_SALARIE").getForeign("ID_CODE_STATUT_CATEGORIEL").getString("CODE");
66
            final boolean cadre = codeStatu.equals("01") || codeStatu.equals("02");
67
 
68
            final Set<Integer> ignoredRubriqueComm = new HashSet<Integer>();
69
            // Total nat
70
            ignoredRubriqueComm.add(2);
71
            // Total brut
72
            // ignoredRubriqueComm.add(3);
73
            // Ligne vide
74
            ignoredRubriqueComm.add(7);
75
            // Cotisations sal
76
            ignoredRubriqueComm.add(4);
77
            // Cotisation pat
78
            ignoredRubriqueComm.add(5);
79
 
80
            this.setPostProcess(new ITransformer<List<SQLRowAccessor>, List<SQLRowAccessor>>() {
81
 
82
                @Override
83
                public List<SQLRowAccessor> transformChecked(List<SQLRowAccessor> input) {
84
 
85
                    List<SQLRowAccessor> result = new ArrayList<SQLRowAccessor>(input.size());
86
                    List<SQLRowAccessor> resultCom = new ArrayList<SQLRowAccessor>(input.size());
87
                    List<SQLRowAccessor> resultNet = new ArrayList<SQLRowAccessor>(input.size());
88
 
89
                    List<SQLRowAccessor> resultCotis = new ArrayList<SQLRowAccessor>(input.size());
90
                    Map<String, SQLRowValues> mapLigneSimplifiee = initCotisations(cadre, resultCotis);
91
                    for (SQLRowAccessor sqlRowAccessor : input) {
92
                        if (sqlRowAccessor.getBoolean("IN_PERIODE")) {
93
                            if (sqlRowAccessor.getString("SOURCE").equals("RUBRIQUE_BRUT")) {
94
                                if (result.size() == 0) {
95
                                    SQLRowValues rowValsTitle = new SQLRowValues(sqlRowAccessor.getTable());
96
                                    rowValsTitle.put("ID_STYLE", eltStyle.getAllStyleByName().get("Titre 1"));
97
                                    rowValsTitle.put("NOM", "Rubrique de brut");
98
                                    rowValsTitle.put("IMPRESSION", Boolean.TRUE);
99
                                    rowValsTitle.put("IN_PERIODE", Boolean.TRUE);
100
                                    result.add(rowValsTitle);
101
                                }
102
                                SQLRowValues rowValsToAdd = sqlRowAccessor.asRowValues();
103
                                rowValsToAdd.put("ID_STYLE", 2);
104
                                result.add(rowValsToAdd);
105
                            }
106
                            if (sqlRowAccessor.getString("SOURCE").equals("RUBRIQUE_NET") && sqlRowAccessor.getBoolean("IMPRESSION") && sqlRowAccessor.getBoolean("IN_PERIODE")) {
107
                                if (resultNet.size() == 0) {
108
                                    SQLRowValues rowValsTitle = new SQLRowValues(sqlRowAccessor.getTable());
109
                                    rowValsTitle.put("ID_STYLE", eltStyle.getAllStyleByName().get("Titre 1"));
110
                                    rowValsTitle.put("NOM", "Rubrique de net");
111
                                    rowValsTitle.put("IMPRESSION", Boolean.TRUE);
112
                                    rowValsTitle.put("IN_PERIODE", Boolean.TRUE);
113
                                    resultNet.add(rowValsTitle);
114
                                }
115
                                SQLRowValues rowValsToAdd = sqlRowAccessor.asRowValues();
116
                                rowValsToAdd.put("ID_STYLE", 2);
117
                                resultNet.add(rowValsToAdd);
118
                            }
119
 
120
                            if (sqlRowAccessor.getString("SOURCE").equals("RUBRIQUE_COMM") && !ignoredRubriqueComm.contains(sqlRowAccessor.getInt("IDSOURCE"))) {
121
                                if (resultNet.size() == 0) {
122
                                    result.add(sqlRowAccessor);
123
                                } else {
124
                                    resultNet.add(sqlRowAccessor);
125
                                }
126
 
127
                            }
128
                            if (sqlRowAccessor.getString("SOURCE").equals("RUBRIQUE_COTISATION")) {
129
                                cumuls(sqlRowAccessor, mapLigneSimplifiee, cadre);
130
                            }
131
                        }
132
                    }
133
                    result.addAll(resultNet);
134
                    result.addAll(resultCom);
135
                    result.addAll(resultCotis);
136
                    return result;
137
                }
138
            });
139
        }
91 ilm 140
    }
141
 
144 ilm 142
    private void cumuls(final SQLRowAccessor sqlRowAccessor, Map<String, SQLRowValues> mapLigneSimplifiee, final boolean cadre) {
143
        // "TAUX_PAT" numeric(16,6) DEFAULT 0,
144
        // "TAUX_SAL" numeric(16,6) DEFAULT 0,
145
        // "NB_BASE" numeric(16,6) DEFAULT 0,
146
        // "MONTANT_SAL_AJ" numeric(16,2) DEFAULT 0,
147
        // "MONTANT_PAT" numeric(16,2) DEFAULT 0,
148
        // "MONTANT_SAL_DED" numeric(16,2) DEFAULT 0,
149
        String idLigneSimplifiee = this.cotisationSimplifieeLink.get(sqlRowAccessor.getInt("IDSOURCE"));
150
        if (idLigneSimplifiee == null || idLigneSimplifiee.trim().length() == 0) {
151
            final SQLRow rowRub = sqlRowAccessor.getTable().getDBRoot().findTable("RUBRIQUE_COTISATION").getRow(sqlRowAccessor.getInt("IDSOURCE"));
152
 
153
            SwingUtilities.invokeLater(new Runnable() {
154
 
155
                @Override
156
                public void run() {
157
                    JOptionPane.showMessageDialog(null, "Aucune liaison définie vers une ligne de bulletin simplifié pour la cotisation " + rowRub.getString("CODE") + " - " + rowRub.getString("NOM")
158
                            + ".\n La liaison sera faite sur autres contributions dues par l'employeur!");
159
                }
160
            });
161
            idLigneSimplifiee = LignePayeSimplifiee.AUTRES_CONTRIBUTIONS.getId();
162
        }
163
 
164
        if (!idLigneSimplifiee.equals(LignePayeSimplifiee.IGNORE.getId())) {
165
            SQLRowValues ligneSimplifiee = mapLigneSimplifiee.get(idLigneSimplifiee);
166
            if (ligneSimplifiee == null) {
167
 
168
                final LignePayeSimplifiee fromID = LignePayeSimplifiee.fromID(idLigneSimplifiee);
169
                SwingUtilities.invokeLater(new Runnable() {
170
 
171
                    @Override
172
                    public void run() {
173
                        JOptionPane.showMessageDialog(null,
174
                                "Aucune ligne simplifiée " + fromID + " dans le bulletin " + (cadre ? "cadre!" : "non cadre!") + "\n La ligne " + sqlRowAccessor.getString("NOM") + " a été ignoré!");
175
                    }
176
                });
177
 
178
            } else {
179
                BigDecimal montantPatBulletinSimpl = BigDecimal.ZERO;
180
                BigDecimal montantSalBulletinSimpl = BigDecimal.ZERO;
181
                if (ligneSimplifiee.getBigDecimal("MONTANT_SAL_DED") != null) {
182
                    montantSalBulletinSimpl = ligneSimplifiee.getBigDecimal("MONTANT_SAL_DED");
183
                }
184
                if (ligneSimplifiee.getBigDecimal("MONTANT_PAT") != null) {
185
                    montantPatBulletinSimpl = ligneSimplifiee.getBigDecimal("MONTANT_PAT");
186
                }
187
 
188
                BigDecimal montantPatLigne = BigDecimal.ZERO;
189
                BigDecimal montantSalLigne = BigDecimal.ZERO;
190
                if (sqlRowAccessor.getBigDecimal("MONTANT_SAL_DED") != null) {
191
                    montantSalLigne = sqlRowAccessor.getBigDecimal("MONTANT_SAL_DED");
192
                }
193
                if (sqlRowAccessor.getBigDecimal("MONTANT_PAT") != null) {
194
                    montantPatLigne = sqlRowAccessor.getBigDecimal("MONTANT_PAT");
195
                }
149 ilm 196
                if (montantPatLigne.add(montantSalLigne).signum() != 0) {
197
                    ligneSimplifiee.put("NB_BASE", sqlRowAccessor.getBigDecimal("NB_BASE"));
198
                    ligneSimplifiee.put("MONTANT_SAL_DED", montantSalBulletinSimpl.add(montantSalLigne));
199
                    ligneSimplifiee.put("MONTANT_PAT", montantPatBulletinSimpl.add(montantPatLigne));
200
                }
174 ilm 201
 
202
                BigDecimal tauxPatBulletinSimpl = BigDecimal.ZERO;
203
                BigDecimal tauxSalBulletinSimpl = BigDecimal.ZERO;
204
                if (ligneSimplifiee.getBigDecimal("TAUX_SAL") != null) {
205
                    tauxSalBulletinSimpl = ligneSimplifiee.getBigDecimal("TAUX_SAL");
206
                }
207
                if (ligneSimplifiee.getBigDecimal("TAUX_PAT") != null) {
208
                    tauxPatBulletinSimpl = ligneSimplifiee.getBigDecimal("TAUX_PAT");
209
                }
210
 
211
                BigDecimal tauxPatLigne = BigDecimal.ZERO;
212
                BigDecimal tauxSalLigne = BigDecimal.ZERO;
213
                if (sqlRowAccessor.getBigDecimal("TAUX_SAL") != null) {
214
                    tauxSalLigne = sqlRowAccessor.getBigDecimal("TAUX_SAL");
215
                }
216
                if (sqlRowAccessor.getBigDecimal("TAUX_PAT") != null) {
217
                    tauxPatLigne = sqlRowAccessor.getBigDecimal("TAUX_PAT");
218
                }
219
 
220
                if (tauxSalLigne.signum() > 0) {
221
                    ligneSimplifiee.put("TAUX_SAL", tauxSalBulletinSimpl.add(tauxSalLigne));
222
                }
223
 
224
                if (tauxPatLigne.signum() > 0) {
225
                    ligneSimplifiee.put("TAUX_PAT", tauxPatBulletinSimpl.add(tauxPatLigne));
226
                }
144 ilm 227
            }
228
        }
229
    }
230
 
231
    private StyleSQLElement eltStyle = Configuration.getInstance().getDirectory().getElement(StyleSQLElement.class);
232
 
233
    private Map<String, SQLRowValues> initCotisations(boolean cadre, List<SQLRowAccessor> resultCotis) {
234
 
235
        SQLTable tableFichePayeElt = this.row.getTable().getDBRoot().findTable("FICHE_PAYE_ELEMENT");
236
        SQLRowValues rowValsCotis = new SQLRowValues(tableFichePayeElt);
237
 
238
        rowValsCotis.put("ID_STYLE", eltStyle.getAllStyleByName().get("Titre 2"));
156 ilm 239
        rowValsCotis.put("NOM", "Cotisations et contributions sociales");
144 ilm 240
        // rowValsCotis.put("NB_BASE", BigDecimal.ZERO);
241
        // rowValsCotis.put("TAUX_SAL", BigDecimal.ZERO);
242
        // rowValsCotis.put("MONTANT_SAL_AJ", BigDecimal.ZERO);
243
        // rowValsCotis.put("MONTANT_SAL_DED", BigDecimal.ZERO);
244
        // rowValsCotis.put("MONTANT_PAT", BigDecimal.ZERO);
245
        rowValsCotis.put("IMPRESSION", Boolean.TRUE);
246
        rowValsCotis.put("IN_PERIODE", Boolean.TRUE);
247
        resultCotis.add(rowValsCotis);
248
 
249
        Map<String, SQLRowValues> mapPayeSimplifiee = new HashMap<String, SQLRowValues>();
250
        List<GroupePayeSimplifiee> listGroupe = cadre ? StructurePayeSimplifiee.getStructureCadre() : StructurePayeSimplifiee.getStructureNonCadre();
251
        for (GroupePayeSimplifiee groupe : listGroupe) {
252
            SQLRowValues rowValsFichePaye = new SQLRowValues(tableFichePayeElt);
253
            rowValsFichePaye.put("NOM", groupe.getTranslation());
254
            rowValsFichePaye.put("IMPRESSION", Boolean.TRUE);
255
            rowValsFichePaye.put("IN_PERIODE", Boolean.TRUE);
256
            rowValsFichePaye.put("ID_STYLE", eltStyle.getAllStyleByName().get("Normal"));
257
            // rowValsFichePaye.put("NB_BASE", BigDecimal.ZERO);
258
            // rowValsFichePaye.put("TAUX_SAL", BigDecimal.ZERO);
259
            // rowValsFichePaye.put("MONTANT_SAL_AJ", BigDecimal.ZERO);
260
            // rowValsFichePaye.put("MONTANT_SAL_DED", BigDecimal.ZERO);
261
            // rowValsFichePaye.put("MONTANT_PAT", BigDecimal.ZERO);
262
            resultCotis.add(rowValsFichePaye);
263
            mapPayeSimplifiee.put(groupe.getId(), rowValsFichePaye);
264
            for (LignePayeSimplifiee lignePayeSimplifiee : groupe.getChildren()) {
265
 
266
                if (groupe.isShowChildren()) {
267
                    SQLRowValues rowValsLPaye = new SQLRowValues(tableFichePayeElt);
268
                    rowValsLPaye.put("NOM", lignePayeSimplifiee.getTranslation());
269
                    rowValsLPaye.put("IMPRESSION", Boolean.TRUE);
270
                    rowValsLPaye.put("IN_PERIODE", Boolean.TRUE);
271
                    rowValsLPaye.put("ID_STYLE", eltStyle.getAllStyleByName().get("Normal"));
272
                    // rowValsLPaye.put("NB_BASE", BigDecimal.ZERO);
273
                    // rowValsLPaye.put("TAUX_SAL", BigDecimal.ZERO);
274
                    // rowValsLPaye.put("MONTANT_SAL_AJ", BigDecimal.ZERO);
275
                    // rowValsLPaye.put("MONTANT_SAL_DED", BigDecimal.ZERO);
276
                    // rowValsLPaye.put("MONTANT_PAT", BigDecimal.ZERO);
277
                    mapPayeSimplifiee.put(lignePayeSimplifiee.getId(), rowValsLPaye);
278
                    resultCotis.add(rowValsLPaye);
279
                } else {
280
                    mapPayeSimplifiee.put(lignePayeSimplifiee.getId(), rowValsFichePaye);
281
                }
282
            }
283
        }
284
        return mapPayeSimplifiee;
285
    }
286
 
91 ilm 287
    @Override
288
    public String getDefaultTemplateId() {
289
        return TEMPLATE_ID;
290
    }
291
 
292
    @Override
293
    public String getName() {
149 ilm 294
        SQLPreferences prefs = new SQLPreferences(elt.getTable().getDBRoot());
295
        boolean prefNomSal = prefs.getBoolean(PayeGlobalPreferencePanel.SALARIE_NOM_FICHIER, Boolean.FALSE);
91 ilm 296
        SQLRow rowSal = row.getForeign("ID_SALARIE");
297
        SQLRow rowMois = row.getForeign("ID_MOIS");
142 ilm 298
        Calendar du = row.getDate("DU");
299
        String suffix = "";
149 ilm 300
        if (prefNomSal) {
301
            suffix = "_" + rowSal.getString("NOM");
302
        }
142 ilm 303
        if (du != null && du.get(Calendar.DAY_OF_MONTH) != 1) {
304
            suffix = "_" + du.get(Calendar.DAY_OF_MONTH);
305
        }
306
        return ("FichePaye_" + rowSal.getString("CODE") + suffix + "_" + rowMois.getString("NOM") + "_" + row.getString("ANNEE"));
91 ilm 307
    }
308
 
309
}