OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 132 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 * 
 * Copyright 2011 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.employe.report;

import org.openconcerto.erp.config.ComptaPropsConfiguration;
import org.openconcerto.erp.core.humanresources.payroll.report.FichePayeSheetXML;
import org.openconcerto.erp.generationDoc.DocumentLocalStorageManager;
import org.openconcerto.sql.Configuration;
import org.openconcerto.sql.element.SQLElement;
import org.openconcerto.sql.model.SQLRow;
import org.openconcerto.sql.model.SQLRowListRSH;
import org.openconcerto.sql.model.SQLSelect;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.Where;
import org.openconcerto.utils.ProductInfo;
import org.openconcerto.utils.StringUtils;

import java.io.File;
import java.io.IOException;
import java.io.PrintStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.Normalizer;
import java.text.Normalizer.Form;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.swing.JOptionPane;
import javax.swing.SwingUtilities;

public class N4DS {

    private DateFormat format = new SimpleDateFormat("ddMMyyyy");
    private ComptaPropsConfiguration conf = ((ComptaPropsConfiguration) Configuration.getInstance());
    private double masseSalarialeBrute;
    private static final byte[] retour = "'\n".getBytes();
    private PrintStream stream = null;

    DecimalFormat decimalFormat = new DecimalFormat("0.00");

    Date dateDebut, dateFin;

    // FIXME Salarie renvoye

    /**
     * Déclaration normale (type 51)
     */
    public N4DS() {
        DecimalFormatSymbols symbol = new DecimalFormatSymbols();
        symbol.setDecimalSeparator('.');
        this.decimalFormat.setDecimalFormatSymbols(symbol);

    }

    public File createDocument() {
        this.masseSalarialeBrute = 0;
        File dir = DocumentLocalStorageManager.getInstance().getDocumentOutputDirectory(FichePayeSheetXML.TEMPLATE_ID);
        dir.mkdirs();
        File f = new File(dir, "N4DS_" + format.format(new Date()) + ".txt");

        // File f = new File("N4DS_" + format.format(new Date()) + ".txt");

        try {

            this.stream = new PrintStream(f, "ISO-8859-1");

            SQLElement eltSalarie = this.conf.getDirectory().getElement("SALARIE");

            // Infos emetteur
            SQLRow rowSociete = this.conf.getRowSociete();

            writeS10(this.stream, rowSociete);

            writeS20(this.stream, rowSociete);

            SQLSelect sel = new SQLSelect();
            sel.addSelect(eltSalarie.getTable().getKey());

            final SQLElement infosElt = eltSalarie.getForeignElement("ID_INFOS_SALARIE_PAYE");
            // Date d1 = new Date(116, 0, 1);
            Date d2 = new Date(116, 11, 31);

            Where w = new Where(infosElt.getTable().getKey(), "=", eltSalarie.getTable().getField("ID_INFOS_SALARIE_PAYE"));
            w = w.and(new Where(infosElt.getTable().getField("DATE_ARRIVE"), "<=", d2));
            w = w.and(new Where(infosElt.getTable().getField("DATE_SORTIE"), "=", (Date) null).or(new Where(infosElt.getTable().getField("DATE_SORTIE"), "<=", d2)));

            sel.setWhere(w);

            @SuppressWarnings("unchecked")
            List<SQLRow> l = (List<SQLRow>) this.conf.getBase().getDataSource().execute(sel.asString(), new SQLRowListRSH(eltSalarie.getTable()));

            for (SQLRow row : l) {
                N4DSSalarie s = new N4DSSalarie(this);
                s.write(row, rowSociete);

            }
            writeS80(this.stream, rowSociete);
            writeS90(this.stream);

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (this.stream != null) {
                this.stream.close();
            }
        }
        return f;
    }

    private void writeS80(PrintStream stream, SQLRow rowSociete) throws IOException {

        // final String nic =
        // StringUtils.limitLength(rowSociete.getString("NUM_SIRET").replaceAll(" ", ""), 9);
        final String siret = rowSociete.getString("NUM_SIRET").replaceAll(" ", "");
        String siren = StringUtils.limitLength(siret, 9);
        String nic = siret.substring(siren.length(), siret.length());

        // // SIREN
        // write("S80.G01.00.001.001", siren);

        // NIC
        write("S80.G01.00.001.002", nic);

        SQLRow rowAdr = rowSociete.getForeignRow("ID_ADRESSE_COMMON");
        String voie = rowAdr.getString("RUE");

        // Complement adresse
        if (voie.contains("\n")) {
            String[] sVoies = voie.split("\n");
            if (sVoies.length > 0) {
                voie = sVoies[0];
                String complement = "";
                for (int i = 1; i < sVoies.length; i++) {
                    complement += sVoies[i].trim() + " ";
                }
                if (complement.length() > 0) {
                    complement = complement.substring(0, complement.length() - 1);
                }

                write("S80.G01.00.003.001", normalizeString2(complement));
            }
        }

        // Voie
        write("S80.G01.00.003.006", normalizeString2(voie));

        // TODO Code INSEE, facultatif
        // stream.write("S80.G01.00.003.007",voie);

        // FIXME Service de distribution
        // stream.write("S80.G01.00.003.009",ville.getName());

        // Code postal
        write("S80.G01.00.003.010", rowAdr.getString("CODE_POSTAL"));

        // Localité
        write("S80.G01.00.003.012", normalizeString2(rowAdr.getString("VILLE")));

        // Code Pays, ne doit pas être renseigné pour une adresse en France
        // TODO support des autres pays
        // write("S80.G01.00.003.013", "");

        // FIXME effectif déclaré
        write("S80.G01.00.004.001", String.valueOf(getEffectifDeclare()));

        // TODO Code établissement sans salarié
        // write( "S80.G01.00.004.001", "2");

        // TODO Code assujettis taxe sur salaires
        write("S80.G01.00.005", "01");

        // Code NAF etablissement
        write("S80.G01.00.006", rowSociete.getString("NUM_APE"));

        // FIXME Code section prud'homale
        write("S80.G01.00.007.001", "04");

        // TODO stecion principale dérogatoire
        // write( "S80.G01.00.004.001", "2");

        // TODO Institution Prevoyance sans salarie
        // write( "S80.G01.01.001", "P0012");
        // write( "S80.G01.01.002", "0003");

        // TODO Institution retraite sans salarie
        // write( "S80.G01.02.001", "G022");

        // FIXME Code assujettissement taxe et contribution apprentissage
        write("S80.G62.00.001", "01");
        double totalApprentissage = this.masseSalarialeBrute * 0.0068;
        System.err.println(this.masseSalarialeBrute);
        write("S80.G62.00.002", this.decimalFormat.format(totalApprentissage));

        write("S80.G62.00.003", "02");
        // FIXME Code assujettissement formation professionnelle continue
        write("S80.G62.00.005", "01");
        double totalFormation = this.masseSalarialeBrute * 0.0055;
        write("S80.G62.00.007", this.decimalFormat.format(totalFormation));

        write("S80.G62.00.008", "02");

    }

    private void writeS90(PrintStream stream) throws IOException {
        // Nombre total de rubrique + S90
        write("S90.G01.00.001", String.valueOf(this.nbRubrique + 2));

        // Nombre total de rubrique S20
        write("S90.G01.00.002", String.valueOf(1));
    }

    private int nbRubrique = 0;

    public void write(String rubriqueName, String value) throws IOException {
        String tmp = rubriqueName + ",'";
        this.stream.write(tmp.getBytes());
        this.stream.write(value.getBytes());
        this.stream.write(retour);

        // if (rubriqueName.startsWith("S20")) {
        // this.nbRubriqueS20++;
        // }
        this.nbRubrique++;
    }

    private int getEffectifDeclare() {
        // FIXME ne pas inclure les intérimaires
        SQLElement eltSalarie = this.conf.getDirectory().getElement("SALARIE");
        SQLElement eltInfos = this.conf.getDirectory().getElement("INFOS_SALARIE_PAYE");
        SQLSelect sel = new SQLSelect();
        sel.addSelect(eltSalarie.getTable().getKey());
        // Date d1 = new Date(116, 0, 1);
        Date d2 = new Date(116, 11, 31);
        Where w = new Where(eltSalarie.getTable().getField("ID_INFOS_SALARIE_PAYE"), "=", eltInfos.getTable().getKey());
        w = w.and(new Where(eltInfos.getTable().getField("DATE_SORTIE"), "=", (Date) null).or(new Where(eltInfos.getTable().getField("DATE_SORTIE"), "<=", d2)));
        w = w.and(new Where(eltInfos.getTable().getField("DATE_ARRIVE"), "=", (Date) null).or(new Where(eltInfos.getTable().getField("DATE_ARRIVE"), "<=", d2)));

        sel.setWhere(w);
        System.err.println(sel.asString());
        List<SQLRow> l = (List<SQLRow>) this.conf.getBase().getDataSource().execute(sel.asString(), new SQLRowListRSH(eltSalarie.getTable()));

        return (l == null ? 0 : l.size());
    }

    public static String normalizeString2(String s) {
        s = s.toUpperCase();
        String temp = Normalizer.normalize(s, Form.NFC);
        temp = temp.replaceAll("-", " ");
        temp = temp.replaceAll("é", "e");
        temp = temp.replaceAll("è", "e");
        temp = temp.replaceAll("ê", "e");
        temp = temp.replaceAll(",", "");
        return temp.replaceAll("[^\\p{ASCII}]", "");
    }

    private void writeS20(PrintStream stream, SQLRow rowSociete) throws IOException {

        // Siren

        final String siret = rowSociete.getString("NUM_SIRET").replaceAll(" ", "");
        String siren = StringUtils.limitLength(siret, 9);
        String nic = siret.substring(siren.length(), siret.length());
        write("S20.G01.00.001", siren);

        // Raison sociale
        write("S20.G01.00.002", normalizeString2(rowSociete.getString("NOM")));

        // FIXME Debut periode
        write("S20.G01.00.003.001", "01012016");

        // FIXME Fin periode
        write("S20.G01.00.003.002", "31122016");

        // Code nature
        write("S20.G01.00.004.001", "01");

        // FIXME Code type (complement, Rectificatif, ...)
        write("S20.G01.00.004.002", "51");

        // fraction
        write("S20.G01.00.005", "11");

        // TODO debut periode rattachement
        // stream.write("S20.G01.00.006.001,'","11");

        // fin periode rattachement
        // stream.write("S20.G01.00.006.002,'","11");

        // Code devise de la déclaration
        write("S20.G01.00.007", "01");

        // NIC
        write("S20.G01.00.008", nic);

        SQLRow rowAdr = rowSociete.getForeignRow("ID_ADRESSE_COMMON");
        String voie = rowAdr.getString("RUE");

        // Complement adresse
        if (voie.contains("\n")) {
            String[] sVoies = voie.split("\n");
            if (sVoies.length > 0) {
                voie = sVoies[0];
                String complement = "";
                for (int i = 1; i < sVoies.length; i++) {
                    complement += sVoies[i].trim() + " ";
                }
                if (complement.length() > 0) {
                    complement = complement.substring(0, complement.length() - 1);
                }

                write("S20.G01.00.009.001", normalizeString2(complement));
            }
        }

        // Voie
        write("S20.G01.00.009.006", normalizeString2(voie));

        // Code postal
        write("S20.G01.00.009.010", rowAdr.getString("CODE_POSTAL"));

        // Localité
        write("S20.G01.00.009.012", normalizeString2(rowAdr.getString("VILLE")));

        write("S20.G01.00.013.002", "1");

        // Code periodicite
        write("S20.G01.00.018", "A00");

        write("S20.G01.05.014.001", siren);
        write("S20.G01.05.014.002", nic);
        write("S20.G01.05.016.001", rowSociete.getString("MAIL"));

    }

    /**
     * Strucuture S10, N4DS
     */
    private void writeS10(PrintStream stream, SQLRow rowSociete) throws IOException {

        // Siren

        final String siret = rowSociete.getString("NUM_SIRET").replaceAll(" ", "");
        String siren = StringUtils.limitLength(siret, 9);
        String nic = siret.substring(siren.length(), siret.length());
        write("S10.G01.00.001.001", siren);

        // NIC
        write("S10.G01.00.001.002", nic);

        // Raison sociale
        write("S10.G01.00.002", normalizeString2(rowSociete.getString("NOM")));

        SQLRow rowAdr = rowSociete.getForeignRow("ID_ADRESSE_COMMON");
        String voie = rowAdr.getString("RUE");

        // Complement adresse
        if (voie.contains("\n")) {
            String[] sVoies = voie.split("\n");
            if (sVoies.length > 0) {
                voie = sVoies[0];
                String complement = "";
                for (int i = 1; i < sVoies.length; i++) {
                    complement += sVoies[i] + " ";
                }
                if (complement.length() > 0) {
                    complement = complement.substring(0, complement.length() - 1);
                }

                write("S10.G01.00.003.001", normalizeString2(complement));
            }
        }

        // Voie
        write("S10.G01.00.003.006", normalizeString2(voie));

        // TODO Code INSEE, facultatif
        // stream.write("S10.G01.00.003.007",voie);

        // TODO: Service de distribution
        write("S10.G01.00.003.009", normalizeString2(rowAdr.getString("VILLE")));

        // Code postal
        write("S10.G01.00.003.010", rowAdr.getString("CODE_POSTAL"));

        // Localité
        write("S10.G01.00.003.012", normalizeString2(rowAdr.getString("VILLE")));

        // Code Pays, ne doit pas être renseigné pour une adresse en France
        // TODO support des autres pays
        // write("S10.G01.00.003.013", "");

        // FIXME Référence de l'envoi
        // Incrémenté le numéro
        write("S10.G01.00.004,'", "1");

        // Nom du logiciel
        write("S10.G01.00.005", "OpenConcerto");

        // Nom de l'éditeur
        write("S10.G01.00.006", "ILM Informatique");

        // Numéro version
        write("S10.G01.00.007", ProductInfo.getInstance().getVersion());

        // Code service choisi
        write("S10.G01.00.009", "40");

        // Code envoi du fichier essai ou réel
        write("S10.G01.00.010", "02");

        // Norme utilisée
        write("S10.G01.00.011", "V01X11");

        // Code table char
        write("S10.G01.00.012", "01");

        // TODO Contact pour DADS
        // Code civilite
        write("S10.G01.01.001.001", "01");

        // Nom Contact
        SQLTable table = Configuration.getInstance().getRoot().findTable("CONTACT_ADMINISTRATIF");
        SQLSelect selContact = new SQLSelect(table.getBase());
        selContact.addSelectStar(table);
        selContact.setWhere(new Where(table.getField("N4DS"), "=", Boolean.TRUE));
        List<SQLRow> l = SQLRowListRSH.execute(selContact);
        if (l.size() == 0) {
            SwingUtilities.invokeLater(new Runnable() {

                @Override
                public void run() {
                    JOptionPane.showMessageDialog(null, "Aucun contact administratif pour la N4DS. Veuillez en définir un.");
                }
            });
        } else {
            SQLRow rowContact = l.get(0);

            // TODO Contact pour DADS
            write("S10.G01.01.001.002", rowContact.getString("NOM") + " " + rowContact.getString("PRENOM"));

            // Code domaine
            write("S10.G01.01.002", "03");

            // Adresse mail
            // TODO Contact pour DADS
            write("S10.G01.01.005", rowContact.getString("EMAIL"));

            // Tel
            String string = rowContact.getString("TEL_DIRECT");
            if (string.trim().length() == 0) {
                string = rowContact.getString("TEL_STANDARD");
            }

            write("S10.G01.01.006", string);

            // Fax
            write("S10.G01.01.007", rowContact.getString("FAX"));
        }
    }

    private String getNumeroVoie(String voie) {
        String numero = "";
        voie = voie.trim();
        for (int i = 0; i < voie.trim().length(); i++) {
            char c = voie.charAt(i);
            if (c >= '0' && c <= '9') {
                numero += c;
            } else {
                break;
            }
        }
        return numero;
    }

    private String getVoieWithoutNumber(String voie) {
        voie = voie.trim();
        String resultVoie = new String(voie);

        for (int i = 0; i < voie.trim().length(); i++) {
            char c = voie.charAt(i);
            if (c >= '0' && c <= '9') {
                resultVoie = resultVoie.substring(1);
            } else {
                break;
            }
        }
        return resultVoie.trim();
    }

    public void addMasseSalarialeBrute(double baseBrute) {
        this.masseSalarialeBrute += baseBrute;
    }

}