OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 18 | Rev 73 | 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;
17
import org.openconcerto.erp.generationDoc.SheetInterface;
18
import org.openconcerto.erp.generationDoc.SheetXml;
19
import org.openconcerto.erp.preferences.PrinterNXProps;
20
import org.openconcerto.sql.Configuration;
21
import org.openconcerto.sql.model.SQLRow;
22
import org.openconcerto.sql.model.SQLRowValues;
23
import org.openconcerto.sql.model.SQLSelect;
24
import org.openconcerto.sql.model.SQLTable;
25
import org.openconcerto.sql.model.Where;
26
import org.openconcerto.utils.Tuple2;
27
 
28
import java.io.File;
29
import java.text.DateFormat;
30
import java.util.Date;
31
import java.util.HashMap;
32
import java.util.List;
33
import java.util.Map;
34
 
35
import org.apache.commons.dbutils.handlers.ArrayListHandler;
36
 
37
public class EtatChargesPayeSheet extends SheetInterface {
38
 
39
    private static int debutFill, endFill;
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 tableCaisse = Configuration.getInstance().getBase().getTable("CAISSE_COTISATION");
44
    private final static SQLTable tableRubCot = Configuration.getInstance().getBase().getTable("RUBRIQUE_COTISATION");
45
 
46
    private final static DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.MEDIUM);
47
    private int moisDu, moisAu;
48
    private String annee;
49
 
50
    public static void setSize(int debut, int fin) {
51
        debutFill = debut;
52
        endFill = fin;
53
 
54
    }
55
 
56
    static {
57
        setSize(7, 66);
58
    }
59
 
25 ilm 60
    public static String TEMPLATE_ID = "Etat des charges";
61
    public static String TEMPLATE_PROPERTY_NAME = "LocationEtatChargesPaye";
18 ilm 62
 
25 ilm 63
    @Override
64
    public String getTemplateId() {
65
        return TEMPLATE_ID;
18 ilm 66
    }
67
 
25 ilm 68
    @Override
69
    protected String getYear() {
70
        return "";
71
    }
72
 
18 ilm 73
    public EtatChargesPayeSheet(int moisDu, int moisAu, String annee) {
74
        super();
75
 
76
        this.printer = PrinterNXProps.getInstance().getStringProperty("EtatChargesPayePrinter");
77
        this.modele = "EtatChargesPaye.ods";
78
        this.moisAu = moisAu;
79
        this.moisDu = moisDu;
80
        this.annee = annee;
81
 
82
        this.nbRowsPerPage = 68;
83
 
84
        createMap();
85
    }
86
 
87
    private void makeEntete(int row) {
88
        SQLRow rowSociete = ((ComptaPropsConfiguration) Configuration.getInstance()).getRowSociete();
89
        this.mCell.put("A" + row, rowSociete.getObject("NOM"));
90
        this.mCell.put("F" + row, "Edition du " + dateFormat.format(new Date()));
91
        // this.mCell.put("D" + (row + 2), "Impression Journaux");
92
        System.err.println("MAKE ENTETE");
93
    }
94
 
95
    private void makeBasPage(int row) {
96
        SQLRow rowMoisDu = tableMois.getRow(this.moisDu);
97
        SQLRow rowMoisAu = tableMois.getRow(this.moisAu);
98
 
99
        this.mCell.put("A" + row, "Période de " + rowMoisDu.getString("NOM") + " à " + rowMoisAu.getString("NOM") + " " + this.annee);
100
    }
101
 
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 w6 = (new Where(tableFichePayeElement.getField("SOURCE"), "=", "RUBRIQUE_COTISATION"));
115
        // Where w2 = (new Where(tableFichePaye.getField("ID_SALARIE"), "=",
116
        // tableSalarie.getField("ID")));
117
        Where w3 = (new Where(tableFichePaye.getField("ID_MOIS"), new Integer(this.moisDu), new Integer(this.moisAu)));
118
        Where w4 = (new Where(tableFichePaye.getField("ANNEE"), "=", new Integer(this.annee)));
119
        Where w5 = (new Where(tableFichePaye.getField("VALIDE"), "=", Boolean.TRUE));
120
 
121
        sel.setWhere(w);
122
        // sel.andWhere(w2);
123
        sel.andWhere(w3);
124
        sel.andWhere(w4);
125
        sel.andWhere(w5);
126
        sel.andWhere(w6);
127
        sel.setDistinct(true);
128
        String req = sel.asString();
129
 
130
        System.err.println(req);
131
 
132
        // Liste des rubriques de chaque salaries
133
        List l = (List) base.getDataSource().execute(req, new ArrayListHandler());
134
 
135
        // Association idCaisse, Map rowValsRubCotCumulé
136
        Map mapCaisse = new HashMap();
137
        Map mapFiche = new HashMap();
138
 
139
        // Cumuls des rubriques de cotisations par caisse
140
        for (int i = 0; i < l.size(); i++) {
141
            Object[] tmp = (Object[]) l.get(i);
142
            mapFiche.put(tmp[0], "");
143
            int idFicheElt = Integer.parseInt(tmp[1].toString());
144
 
145
            SQLRow rowFicheElt = tableFichePayeElement.getRow(idFicheElt);
146
            SQLRow rowRub = tableRubCot.getRow(rowFicheElt.getInt("IDSOURCE"));
147
 
148
            // On recupere la map de la caisse
149
            Map mapValueRub;
150
            if (mapCaisse.containsKey(new Integer(rowRub.getInt("ID_CAISSE_COTISATION")))) {
151
                mapValueRub = (Map) mapCaisse.get(new Integer(rowRub.getInt("ID_CAISSE_COTISATION")));
152
            } else {
153
                mapValueRub = new HashMap();
154
                mapCaisse.put(new Integer(rowRub.getInt("ID_CAISSE_COTISATION")), mapValueRub);
155
            }
156
 
157
            // on recupere la rowvalues de la rubrique
158
            SQLRowValues rowVals;
159
            if (mapValueRub.containsKey(rowFicheElt.getObject("IDSOURCE"))) {
160
                rowVals = (SQLRowValues) mapValueRub.get(rowFicheElt.getObject("IDSOURCE"));
161
                // on cumule les données
162
                if (rowFicheElt.getObject("NB_BASE") != null) {
163
                    Object o = rowVals.getObject("NB_BASE");
164
                    float base = (o == null) ? 0.0F : ((Float) o).floatValue();
165
                    base += rowFicheElt.getFloat("NB_BASE");
166
                    rowVals.put("NB_BASE", new Float(base));
167
                }
168
                if (rowFicheElt.getObject("MONTANT_PAT") != null) {
169
                    Object o = rowVals.getObject("MONTANT_PAT");
170
                    float montant = (o == null) ? 0.0F : ((Float) o).floatValue();
171
                    montant += rowFicheElt.getFloat("MONTANT_PAT");
172
                    rowVals.put("MONTANT_PAT", new Float(montant));
173
                }
174
                if (rowFicheElt.getObject("MONTANT_SAL_DED") != null) {
175
                    Object o = rowVals.getObject("MONTANT_SAL_DED");
176
                    float montant = (o == null) ? 0.0F : ((Float) o).floatValue();
177
                    montant += rowFicheElt.getFloat("MONTANT_SAL_DED");
178
                    rowVals.put("MONTANT_SAL_DED", new Float(montant));
179
                }
180
            } else {
181
                rowVals = new SQLRowValues(tableFichePayeElement);
182
                rowVals.loadAllSafe(rowFicheElt);
183
                float montantPat, montantSal;
184
 
185
                Object o = rowVals.getObject("MONTANT_PAT");
186
                montantPat = (o == null) ? 0.0F : ((Float) o).floatValue();
187
 
188
                o = rowVals.getObject("MONTANT_SAL_DED");
189
                montantSal = (o == null) ? 0.0F : ((Float) o).floatValue();
190
 
191
                if (montantPat != 0 || montantSal != 0) {
192
                    mapValueRub.put(rowFicheElt.getObject("IDSOURCE"), rowVals);
193
                }
194
            }
195
 
196
        }
197
 
198
        // Fill
199
        int posLine = 1;
200
        int firstLine = 1;
201
 
202
        System.err.println("Dump fiche " + mapFiche);
203
        System.err.println("NB Pages = " + mapCaisse.keySet().size());
204
 
205
        for (int n = 0; n < mapCaisse.keySet().size(); n++) {
206
 
207
            // entete
208
            makeEntete(posLine);
209
            posLine += (debutFill - 1);
210
 
211
            Map mapValue = (Map) mapCaisse.get(mapCaisse.keySet().toArray()[n]);
212
            float totalMontantSal = 0.0F;
213
            float totalMontantPat = 0.0F;
214
 
215
            SQLRow rowCaisse = tableCaisse.getRow(Integer.parseInt(mapCaisse.keySet().toArray()[n].toString()));
216
            this.mCell.put("A" + posLine, "Caisse " + rowCaisse.getObject("NOM"));
217
            this.mCell.put("B" + posLine, "");
218
            this.mCell.put("C" + posLine, "");
219
            this.mCell.put("D" + posLine, "");
220
            this.mCell.put("E" + posLine, "");
221
            this.mCell.put("F" + posLine, "");
222
            this.mapStyleRow.put(new Integer(posLine), "Titre 1");
223
 
224
            posLine++;
225
 
226
            for (int i = 0; i < mapValue.keySet().size(); i++) {
227
                SQLRowValues rowVals = (SQLRowValues) mapValue.get(mapValue.keySet().toArray()[i]);
228
 
229
                this.mCell.put("A" + posLine, rowVals.getObject("NOM"));
230
                this.mCell.put("B" + posLine, rowVals.getObject("NB_BASE"));
231
 
232
                Float oTxSal = (Float) rowVals.getObject("TAUX_SAL");
233
                float txSal = (oTxSal == null) ? 0.0F : oTxSal.floatValue();
234
                Float oTxPat = (Float) rowVals.getObject("TAUX_PAT");
235
                float txPat = (oTxPat == null) ? 0.0F : oTxPat.floatValue();
236
                this.mCell.put("C" + posLine, new Float(txSal + txPat));
237
 
238
                System.err.println(rowVals.getObject("MONTANT_SAL_DED").getClass());
239
                Float oMontantSal = (Float) rowVals.getObject("MONTANT_SAL_DED");
240
                float montantSal = (oMontantSal == null) ? 0.0F : oMontantSal.floatValue();
241
                Float oMontantPat = (Float) rowVals.getObject("MONTANT_PAT");
242
                float montantPat = (oMontantPat == null) ? 0.0F : oMontantPat.floatValue();
243
                this.mCell.put("D" + posLine, new Float(montantPat));
244
                this.mCell.put("E" + posLine, new Float(montantSal));
245
                this.mCell.put("F" + posLine, new Float(montantSal + montantPat));
246
                totalMontantPat += montantPat;
247
                totalMontantSal += montantSal;
248
 
249
                this.mapStyleRow.put(new Integer(posLine), "Normal");
250
                posLine++;
251
            }
252
 
253
            this.mCell.put("A" + posLine, "Total");
254
            this.mCell.put("B" + posLine, "");
255
            this.mCell.put("C" + posLine, "");
256
            this.mCell.put("D" + posLine, new Float(totalMontantPat));
257
            this.mCell.put("E" + posLine, new Float(totalMontantSal));
258
            this.mCell.put("F" + posLine, new Float(totalMontantPat + totalMontantSal));
259
            this.mapStyleRow.put(new Integer(posLine), "Titre 1");
260
 
261
            // pied de page
262
            posLine = firstLine + endFill - 1;
263
            posLine += 2;
264
            makeBasPage(posLine);
265
 
266
            posLine++;
267
            firstLine = posLine;
268
        }
269
 
270
        this.nbPage = mapCaisse.size();
271
 
272
        System.err.println("Nombre de page " + this.nbPage);
273
 
274
        // on conserve la page d'origine du model
275
 
276
        if (this.nbPage > 0) {
277
            this.nbPage--;
278
        }
279
    }
280
}