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

/*

 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.

 *

 * Copyright 2011-2019 OpenConcerto, by ILM Informatique. All rights reserved.

 *

 * The contents of this file are subject to the terms of the GNU General Public License Version 3

 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a

 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific

 * language governing permissions and limitations under the License.

 *

 * When distributing the software, include this License Header Notice in each file.

 */

 package org.openconcerto.erp.core.humanresources.payroll.report;

import org.openconcerto.erp.config.ComptaPropsConfiguration;

import org.openconcerto.erp.generationDoc.SheetInterface;

import org.openconcerto.erp.preferences.PrinterNXProps;

import org.openconcerto.sql.Configuration;

import org.openconcerto.sql.model.SQLRow;

import org.openconcerto.sql.model.SQLRowValues;

import org.openconcerto.sql.model.SQLSelect;

import org.openconcerto.sql.model.SQLTable;

import org.openconcerto.sql.model.Where;

import java.math.BigDecimal;

import java.text.DateFormat;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class EtatChargesPayeSheet extends SheetInterface {

    private static int debutFill, endFill;

    private final static SQLTable tableFichePaye = base.getTable("FICHE_PAYE");

    private final static SQLTable tableFichePayeElement = base.getTable("FICHE_PAYE_ELEMENT");

    private final static SQLTable tableMois = base.getTable("MOIS");

    private final static SQLTable tableCaisse = Configuration.getInstance().getBase().getTable("CAISSE_COTISATION");

    private final static SQLTable tableRubCot = Configuration.getInstance().getBase().getTable("RUBRIQUE_COTISATION");

    private final DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.MEDIUM);

    private int moisDu, moisAu;

    private String annee;

    public static void setSize(int debut, int fin) {

        debutFill = debut;

        endFill = fin;

    }

    static {

        setSize(7, 66);

    }

    public static String TEMPLATE_ID = "Etat des charges";

    public static String TEMPLATE_PROPERTY_NAME = "LocationEtatChargesPaye";

    @Override

    public String getTemplateId() {

        return TEMPLATE_ID;

    }

    @Override

    protected String getYear() {

        return "";

    }

    public EtatChargesPayeSheet(int moisDu, int moisAu, String annee) {

        super();

        this.printer = PrinterNXProps.getInstance().getStringProperty("EtatChargesPayePrinter");

        this.modele = "EtatChargesPaye.ods";

        this.moisAu = moisAu;

        this.moisDu = moisDu;

        this.annee = annee;

        this.nbRowsPerPage = 68;

        createMap();

    }

    private void makeEntete(int row) {

        SQLRow rowSociete = ((ComptaPropsConfiguration) Configuration.getInstance()).getRowSociete();

        this.mCell.put("A" + row, rowSociete.getObject("NOM"));

        this.mCell.put("F" + row, "Edition du " + dateFormat.format(new Date()));

        // this.mCell.put("D" + (row + 2), "Impression Journaux");

        System.err.println("MAKE ENTETE");

    }

    private void makeBasPage(int row) {

        SQLRow rowMoisDu = tableMois.getRow(this.moisDu);

        SQLRow rowMoisAu = tableMois.getRow(this.moisAu);

        this.mCell.put("A" + row, "Période de " + rowMoisDu.getString("NOM") + " à " + rowMoisAu.getString("NOM") + " " + this.annee);

    }

    protected void createMap() {

        this.mapReplace = new HashMap();

        this.mCell = new HashMap();

        this.mapStyleRow = new HashMap();

        SQLSelect sel = new SQLSelect(base);

        sel.addSelect(tableFichePaye.getField("ID"));

        sel.addSelect(tableFichePayeElement.getField("ID"));

        // sel.addSelect(tableSalarie.getField("ID"));

        Where w = (new Where(tableFichePayeElement.getField("ID_FICHE_PAYE"), "=", tableFichePaye.getField("ID")));

        Where w6 = (new Where(tableFichePayeElement.getField("SOURCE"), "=", "RUBRIQUE_COTISATION"));

        // Where w2 = (new Where(tableFichePaye.getField("ID_SALARIE"), "=",

        // tableSalarie.getField("ID")));

        Where w3 = (new Where(tableFichePaye.getField("ID_MOIS"), new Integer(this.moisDu), new Integer(this.moisAu)));

        Where w4 = (new Where(tableFichePaye.getField("ANNEE"), "=", new Integer(this.annee)));

        Where w5 = (new Where(tableFichePaye.getField("VALIDE"), "=", Boolean.TRUE));

        sel.setWhere(w);

        // sel.andWhere(w2);

        sel.andWhere(w3);

        sel.andWhere(w4);

        sel.andWhere(w5);

        sel.andWhere(w6);

        sel.setDistinct(true);

        String req = sel.asString();

        System.err.println(req);

        // Liste des rubriques de chaque salaries

        List l = (List) base.getDataSource().execute(req, new ArrayListHandler());

        // Association idCaisse, Map rowValsRubCotCumulé

        Map mapCaisse = new HashMap();

        Map mapFiche = new HashMap();

        // Cumuls des rubriques de cotisations par caisse

        for (int i = 0; i < l.size(); i++) {

            Object[] tmp = (Object[]) l.get(i);

            mapFiche.put(tmp[0], "");

            int idFicheElt = Integer.parseInt(tmp[1].toString());

            SQLRow rowFicheElt = tableFichePayeElement.getRow(idFicheElt);

            SQLRow rowRub = tableRubCot.getRow(rowFicheElt.getInt("IDSOURCE"));

            // On recupere la map de la caisse

            Map mapValueRub;

            if (mapCaisse.containsKey(new Integer(rowRub.getInt("ID_CAISSE_COTISATION")))) {

                mapValueRub = (Map) mapCaisse.get(new Integer(rowRub.getInt("ID_CAISSE_COTISATION")));

            } else {

                mapValueRub = new HashMap();

                mapCaisse.put(new Integer(rowRub.getInt("ID_CAISSE_COTISATION")), mapValueRub);

            }

            // on recupere la rowvalues de la rubrique

            SQLRowValues rowVals;

            if (mapValueRub.containsKey(rowFicheElt.getObject("IDSOURCE"))) {

                rowVals = (SQLRowValues) mapValueRub.get(rowFicheElt.getObject("IDSOURCE"));

                // on cumule les données

                if (rowFicheElt.getObject("NB_BASE") != null) {

                    Object o = rowVals.getObject("NB_BASE");

                    BigDecimal base = (o == null) ? BigDecimal.ZERO : ((BigDecimal) o);

                    base = base.add(rowFicheElt.getBigDecimal("NB_BASE"));

                    rowVals.put("NB_BASE", base);

                }

                if (rowFicheElt.getObject("MONTANT_PAT") != null) {

                    Object o = rowVals.getObject("MONTANT_PAT");

                    BigDecimal montant = (o == null) ? BigDecimal.ZERO : ((BigDecimal) o);

                    montant = montant.add(rowFicheElt.getBigDecimal("MONTANT_PAT"));

                    rowVals.put("MONTANT_PAT", montant);

                }

                if (rowFicheElt.getObject("MONTANT_SAL_DED") != null) {

                    Object o = rowVals.getObject("MONTANT_SAL_DED");

                    BigDecimal montant = (o == null) ? BigDecimal.ZERO : ((BigDecimal) o);

                    montant = montant.add(rowFicheElt.getBigDecimal("MONTANT_SAL_DED"));

                    rowVals.put("MONTANT_SAL_DED", montant);

                }

            } else {

                rowVals = new SQLRowValues(tableFichePayeElement);

                Configuration.getInstance().getDirectory().getElement(tableFichePayeElement).loadAllSafe(rowVals, rowFicheElt);

                BigDecimal montantPat, montantSal;

                Object o = rowVals.getObject("MONTANT_PAT");

                montantPat = (o == null) ? BigDecimal.ZERO : ((BigDecimal) o);

                o = rowVals.getObject("MONTANT_SAL_DED");

                montantSal = (o == null) ? BigDecimal.ZERO : ((BigDecimal) o);

                if (montantPat.signum() != 0 || montantSal.signum() != 0) {

                    mapValueRub.put(rowFicheElt.getObject("IDSOURCE"), rowVals);

                }

            }

        }

        // Fill

        int posLine = 1;

        int firstLine = 1;

        System.err.println("Dump fiche " + mapFiche);

        System.err.println("NB Pages = " + mapCaisse.keySet().size());

        for (int n = 0; n < mapCaisse.keySet().size(); n++) {

            // entete

            makeEntete(posLine);

            posLine += (debutFill - 1);

            Map mapValue = (Map) mapCaisse.get(mapCaisse.keySet().toArray()[n]);

            BigDecimal totalMontantSal = BigDecimal.ZERO;

            BigDecimal totalMontantPat = BigDecimal.ZERO;

            SQLRow rowCaisse = tableCaisse.getRow(Integer.parseInt(mapCaisse.keySet().toArray()[n].toString()));

            this.mCell.put("A" + posLine, "Caisse " + rowCaisse.getObject("NOM"));

            this.mCell.put("B" + posLine, "");

            this.mCell.put("C" + posLine, "");

            this.mCell.put("D" + posLine, "");

            this.mCell.put("E" + posLine, "");

            this.mCell.put("F" + posLine, "");

            this.mapStyleRow.put(new Integer(posLine), "Titre 1");

            posLine++;

            for (int i = 0; i < mapValue.keySet().size(); i++) {

                SQLRowValues rowVals = (SQLRowValues) mapValue.get(mapValue.keySet().toArray()[i]);

                this.mCell.put("A" + posLine, rowVals.getObject("NOM"));

                this.mCell.put("B" + posLine, rowVals.getObject("NB_BASE"));

                BigDecimal txSal = rowVals.getBigDecimal("TAUX_SAL");

                txSal = (txSal == null) ? BigDecimal.ZERO : txSal;

                BigDecimal txPat = rowVals.getBigDecimal("TAUX_PAT");

                txPat = (txPat == null) ? BigDecimal.ZERO : txPat;

                this.mCell.put("C" + posLine, txSal.add(txPat));

                // System.err.println(rowVals.getObject("MONTANT_SAL_DED").getClass());

                BigDecimal montantSal = rowVals.getBigDecimal("MONTANT_SAL_DED");

                montantSal = (montantSal == null) ? BigDecimal.ZERO : montantSal;

                BigDecimal montantPat = rowVals.getBigDecimal("MONTANT_PAT");

                montantPat = (montantPat == null) ? BigDecimal.ZERO : montantPat;

                this.mCell.put("D" + posLine, montantPat);

                this.mCell.put("E" + posLine, montantSal);

                this.mCell.put("F" + posLine, montantSal.add(montantPat));

                totalMontantPat = totalMontantPat.add(montantPat);

                totalMontantSal = totalMontantSal.add(montantSal);

                this.mapStyleRow.put(new Integer(posLine), "Normal");

                posLine++;

            }

            this.mCell.put("A" + posLine, "Total");

            this.mCell.put("B" + posLine, "");

            this.mCell.put("C" + posLine, "");

            this.mCell.put("D" + posLine, totalMontantPat);

            this.mCell.put("E" + posLine, totalMontantSal);

            this.mCell.put("F" + posLine, totalMontantPat.add(totalMontantSal));

            this.mapStyleRow.put(new Integer(posLine), "Titre 1");

            // pied de page

            posLine = firstLine + endFill - 1;

            posLine += 2;

            makeBasPage(posLine);

            posLine++;

            firstLine = posLine;

        }

        this.nbPage = mapCaisse.size();

        if (this.nbPage < 1) {

            this.nbPage = 1;

        }

        System.err.println("Nombre de page " + this.nbPage);

    }

}