.. -*- mode: rst -*-
.. -*-  coding: utf-8 -*-

.. role:: toml(code)
   :language: toml

.. default-role:: toml

============================================
Outil de construction des fichiers d’imports
============================================

.. raw:: html

  <style>
    body { font-family: sans-serif; }
    p, li { line-height: 1.5em; }
    pre.code, code {
      border: 1px solid #d2d3d7;
      background-color: #f5f6f7;
    }
    pre.code { padding: 1em; margin: 2em 1.5em 2em 1.5em; }
    code { display: inline-block; padding: 0.1em; }
    table { border-collapse: collapse; }
    thead { color: white; background-color: gray; }
    td { border: none; }
    table:not(.option-list) tr:not(:last-child):not(.field) { border-bottom: 1px solid lightgray; }

    .collapse_wrap > input {
        display: none;
        height: 0px;
    }

    .collapse_wrap > label::before {
        content: "\25B6";
        padding-right: 10px;
    }
    .collapse_wrap input:checked ~ label::before {
        content: "\25BC";
        padding-right: 10px;
    }

    .align-center {
        display: block;
        margin-left: auto;
        margin-right: auto;
    }

    .collapse_wrap .collapse {
      overflow: hidden;
      display: none;
      transition: 0.5s;
      box-shadow: 1px 2px 4px rgba(0, 0, 0, 0.3);
    }

    .collapse_wrap > input:checked ~ .collapse {
      display: block;
      height: unset;
      overflow: auto;
    }
  </style>


  <script>
    document.addEventListener("DOMContentLoaded", function() {
      const collapsables = document.getElementsByClassName("collapse");
      for (let i = 0; i < collapsables.length; i++) {
        var wrap = document.createElement("div");
        wrap.classList.add("collapse_wrap");

        var radio = document.createElement("input");
        radio.setAttribute('type', 'checkbox');
        radio.setAttribute('id', 'radio_' + i);

        var label = document.createElement("label");
        label.setAttribute('for', 'radio_' + i);
        label.innerHTML = "Afficher";

        wrap.appendChild(radio);
        wrap.appendChild(label);

        collapsables[i].parentNode.insertBefore(wrap, collapsables[i]);
        wrap.appendChild(collapsables[i]);
      }
    });
  </script>

.. contents::
   :depth: 2

L’application permet de construire un fichier d’import à partir d’une série de
règles indiquant comment assembler les données. L’outil est capable d’aller
chercher les données dans plusieurs fichiers différents et appliquer des règles
simples pour mettre en forme le résultat.

Invocation
==========

--conf

    Le chemin vers le fichier de configuration. Cet argument est obligatoire.

--print-conf

    Écrit la configuration chargée sur la sortie courante et selon la dernière
    syntaxe de l’application.

--no-bom

    .. class:: collapse

    Ne pas insérer un BOM_ dans le fichier CSV. Cet indicateur permet
    d’informer Excel que le fichier CSV est en UTF-8, ce qui devrait être le
    cas. Dans le cas où cette chaine de caractère pose problème, il est
    possible de désactiver ce mécanisme.

 .. _BOM: https://fr.wikipedia.org/wiki/Indicateur_d%27ordre_des_octets

Sortie
======

L’exécution du programme va générer trois fichiers, qui sont tous basés sur le
nom du fichier de configuration :


sortie.csv

    Il s’agit du fichier produit par l’application, et dont les colonnes sont
    celles définies dans le fichier de configuration.

sortie.sqlite

    Il s’agit d’une base de données reprenant toutes les données utilisées pour
    construire le résultat.

sortie_error.csv

    Il s’agit d’un fichier CSV généré si l’application rencontre des erreurs.
    Celles-ci concernent les liaisons entre les différents fichiers qui n’ont
    pas pu être réalisées

Fichier de configuration
========================

Les informations générales
--------------------------

version

  Il s’agit de la version de la syntaxe du fichier de configuration. Valeur par
  défaut : `1`

source
  La clef `source` indique quel est le fichier source : pour chaque ligne
  présente dans ce fichier, une ligne sera générée en sortie.

    :file:  le fichier à charger
    :tab:   optionnellement l’onglet concerné
    :name:  le nom sous lequel le fichier sera associé.

locale
  Permet de définir la locale à utiliser lors de la génération du fichier CSV.
  Utilise la valeur du système si non renseigné.

  Exemples de valeurs possibles : `C`, `fr_FR.UTF-8`

Le chargement des dépendances
-----------------------------

La recherche de valeurs dans d’autres fichiers Excel nécessite de déclarer les
fichiers à charger. La liste `externals` décrit tous les fichiers qui doivent
être chargés, et comment les données doivent être associées avec le fichier
source.

.. code:: toml

  [externals.target]
    intern_key = ":source.A"
    extern_key = ":A"
    file = "financial.xlsx"
    allow_missing = false

Les clefs suivantes sont nécessaires pour lier les données venant d’un autre
fichier :

intern_key
    Il s’agit de la colonne servant à faire la liaison dans la source.
file
    Le fichier à charger
tab
    optionnellement l’onglet concerné
extern_key
    Il s’agit de la colonne servant à faire la liaison dans le fichier devant
    être lié. La clef doit doit etre unique, l’application supprimera les
    doublons pour ne garder qu’une seule valeur par clef. Il n’est donc pas
    possible de faire un produit cartésien.
allow_missing
    Cette clef optionnelle indique s’il faut autoriser les valeurs manquantes
    lors dans une liaison. Déclarer `allow_missing` va également autoriser les
    doublons sur les valeurs de clef.

Une fois la dépendance vers un fichier externe déclaré, il est possible
d’utiliser les colonnes de ce fichier en les référençant directement. Cela
revient à faire un `RECHERCHEV` dans Excel à chaque fois.

Il est possible d’utiliser une référence externe dans une autre source externe,
afin de construire des chemins sur plusieurs niveaux :

.. code:: toml

  [externals.acheteur_annuaire]
    intern_key = ":I"
    extern_key = ":A"
    file = "ANNUAIRE.xlsx"

  [externals.acheteur_societe]
    intern_key = ":acheteur_annuaire.BJ"
    extern_key = ":A"
    file = "SOCIETES.xlsx"


Les valeurs présentes dans ces colonnes sont pré-traitées pour éviter les
erreurs générales lors des imports : les espaces en fin de texte sont
supprimés et le texte est passé en capitale.

Définition des colonnes
-----------------------

Une fois les différents fichiers chargés, il est possible de construire le
schéma du fichier de sortie en listant toutes les colonnes à générer :

.. code:: toml

  """function
    ( :A ^ '_' ^ :target.E
    , :E
    )"""

======================= ======================================= ======================
Type d’élément          Interprétation                          Exemple
======================= ======================================= ======================
Texte                   Un texte libre. Celui-ci doit être      `'_'`
                        placé entre quote (`'` ou `"`).

Un nombre               La valeur correspondante                `1234`

Une référence           Une colonne. Celle-ci peut être définie `:A`
                        via un fichier externe ou directement
                        par rapport au fichier source.          `:target.E`

                        Elle ne doit pas être placée entre
                        quote. Le nom du fichier peut être omis
                        (et est alors considéré comme étant le
                        fichier source)

Opérateur `^`           Concaténation d’éléments                `"contract_" ^ :A`

Opérateur mathématique  `+` `-` `/` `=` `<>`                    `:Q + 1`
                                                                `:A = 2000`

Fonction                Applique la fonction sur les            `nvl(:I, :N, "Defaut")`
                        éléments donnés. La fonction ne doit
                        pas être placée entre quote             `trim(:N)`

                        La fonction prend en paramètre
                        les arguments sous forme de             `trim("prefixe_" ^ :C)`
                        liste, séparés par des virgules.

======================= ======================================= ======================


.. class:: collapse

    :Operator: `+` | `-` | `<` | `>` | `/` | `^`
    :Equality: `=` | `<>`

    :Reference:
        | `:`\ COLUMN
        | `:`\ TABLE\ `.`\ COLUMN

    :Expression:
        | LITERAL
        | NUMERIC
        | *Reference*
        | *Expression* *Operator* *Expression*
        | *Expression* *Equality* *Expression*
        | *Expression* *Equality* *Group*
        | IDENTIFIER `(` *Expression*\ + `)`
        | IDENTIFIER `(` *Expression*\ , *Group*, *Group* `)`
        | IDENTIFIER `(` *Group*, *Group* `)`
    :Group:
        | `[` Expression* `]`

Tri des données
---------------

.. code:: toml

  sort = [
    ":mouvements.B",
    ":lib_titres.B",
    "date('%d/%m/%Y', :mouvements.C)",
    ":A",
  ]

Les données peuvent être triées à l’aide de la section `sort`. Il est possible
de définir plusieurs critères de tri qui seront traités par ordre de priorité.
Des fonctions peuvent également être utilisées (dans l’exemple ci-dessus, nous
avons un tri sur des dates au format dd/mm/yyyy, si la donnée n’était pas
convertie en date, elle serait triée sur le jour avant d’être triée sur
l’année).

Filtrer les données
-------------------

.. code:: toml

   filters = [
    ":t_operation.B <> ['Cession', 'Restitution', 'Prêt de titres']",
    ":lib_titres.B <> 0",
    ":societe.A <> 0",
  ]

Un filtre peut être mis en place pour ne conserver que les lignes souhaitées.
Les conditions doivent être toutes validées pour que la ligne soit retenue.

Les filtres sont appliqués dans leur ordre d’apparition. Cela peut changer le
résultat selon que l’on filtre :

1. d’abord sur la plus grande valeur de la colonne A, puis les lignes avec la
   colonne B ≠ 0
2. d’abord sur la colonne B ≠ 0, puis la plus grande valeur de la colonne A.

Exemple complet
===============


Cet exemple (issu d’un cas réel), crée un fichier des personnes morales à
partir de plusieurs tables.

- Le fichier source donne des identifiants pour les formes juridiques (qu’il
  faut donc aller dans une table à part) et les devises

- le numéro de TVA communautaire doit être chargé dans la table
  immatriculation, la colonne A doit correspondre au code de la société, et la
  colonne D doit avoir la valeur `4`

- l’adresse est reconstituée en concaténant cinq colonnes depuis la table
  annuaire.

- la ville et le code postal peuvent être présent sur deux tables : la table
  annuaire si l’adresse n’a pas été modifiée depuis la saisie initiale de la
  fiche, ou la source qui contient la dernière valeur, mais peut être nulle si
  celle-ci n’a pas été modifiée…

.. class:: collapse

    .. code:: toml

        [source]
          file = "20220222_SOCIETES.xlsx"
          name = "société"

        # Des commentaires peuvent etre ajoutés tout au long du fichier.
        # Ici la table actif permet de faire une correspondance entre les valeurs
        # du client et nos valeurs :
        # 1 -> Actif
        # 0 -> Inactif
        [externals.actifs]
          intern_key = ":E"
          file = "actifs.xlsx"
          extern_key = ":A"
          allow_missing = false

        [externals.legalform]
          intern_key = ":J"
          file = "mapping.xlsx"
          tab = 2
          extern_key = ":A"
          allow_missing = false

        [externals.currency]
          intern_key = ":BF"
          file = "mapping.xlsx"
          tab = 6
          extern_key = ":A"
          allow_missing = false

        # le fichier annuaire contient des informations supplémentaires
        [externals.annuaire]
          intern_key = ":A"
          file = "20220222_ANNUAIRE.xlsx"
          extern_key = ":BJ"
          allow_missing = false

        # La table des immatriculations.
        # Seules les lignes avec la colonne D = 4 nous intéressent
        [externals.immat]
          intern_key = ":A ^ '_4'"
          file = "20220222_SO_IMMATRICULATION.xlsx"
          extern_key = ":B ^ '_' ^ :D"
          allow_missing = true

        [sheet]
          columns = [
            "'companyDirectory_' ^ :A",
            ":G",
            "'internalExternalList_Internal'",
            ":actifs.B",
            ":H",
            ":I",
            "",
            ":legalform.S",
            ":annuaire.I",
            ":T",
            ":BP",
            ":currency.D",
            "",
            ":annuaire.CC",
            ":CQ",
            ":CO",
            ":immat.C",
            # Utilise la fonction join pour assembler les éléments
            # De cette manière, si l’un des éléments est vide, il n’y aura pas de
            # double espace ('  ') dans le résultat
            "join(' ', :annuaire.CP, :annuaire.CQ, :annuaire.CR, :annuaire.L, :annuaire.M)",
            "nvl(:CM, :annuaire.N)",
            "nvl(:CS, :annuaire.AB)", ":CR"
          ]
          filters = []
          sort = []
          uniq = []

Comparaison des valeurs
=======================

Comparaison par groupe
----------------------

.. code:: toml

    ":t_operation.B <> ['Cession', 'Restitution', 'Prêt de titres']"

Les opérateurs de comparaison `=` et `<>` peuvent accepter un groupe de valeur
plutot qu’une valeur unique. Dans ce cas, la condition est vraie si l’une des
valeurs est présente.

Le cas des cellules vides
-------------------------

.. code:: toml

    ":B > 0",
    "'prefix_' ^ :B",

Les cellules vides peuvent apparaitres dans des colonnes où l’on s’attend à
trouver des champs texte, ou des nombres. L’application va traiter ces valeurs
vides en fonction du contexte et les considérer comme `0` si elles sont
associées avec un numérique, ou `""` si elles sont associées à un texte.


Fonctions disponibles
=====================

Liste non exhaustive.

Fonctions de branchement
------------------------

`if`
    Choisi la valeur de la cellule en fonction d’une condition :

    .. code:: toml

      """if(
          :E = 1
        , 'Vrai'
        , 'Faux'
      )"""

`cmp`

    .. code:: toml

      """cmp(
          :A
        , :B
        , 'A is smaller than B'
        , 'A is equal to B'
        , 'A is greater than B'
      )"""

    Compare les deux premiers arguments, puis évalue suivant le résultat :

    - inférieur à
    - égal à
    - supérieur à


`nvl`
    Sélectionne la première valeur non nulle de la liste

    .. code:: toml

      "nvl(:I, :N, 'Defaut')"

Fonctions sur les nombres
-------------------------

`abs`
    Renvoie la valeur absolue d’un nombre.

`int`
    Transforme une valeur en nombre entier.

Fonctions sur le texte
----------------------

`join`
    .. code:: toml

      "join('-', :A, :target.E, :B)"

    Concatène tous les champs en utilisant le premier paramètre comme
    séparateur. Les valeurs vides sont ignorées, et dans ce cas le séparateur
    n’est pas répété.

`match`
    Extrait un motif sur la base d’une `expression régulière`_.

    .. code:: toml

      "match('hello ([A-Za-z]+)', ':B')"

.. _expression régulière:
    https://fr.wikipedia.org/wiki/Expression_r%C3%A9guli%C3%A8re#Op%C3%A9rateurs

`substring`
    Extrait une sous-chaine en indiquant la position et la longueur.

    .. code:: toml

      "substring(:H, 1, 5)"

`trim`
    Supprime les espaces en début et fin de chaine

    .. code:: toml

      "trim(:C)"

`upper`
    Passe le texte en capitale

    .. code:: toml

      "upper(:A)"

Fonctions sur les dates
-----------------------

Normalement, les dates sont représentées dans Excel comme un nombre. On peut
donc utiliser les opérations standard (addition…) sans avoir à se poser de
question sur leur résultat. Toutefois, il est parfois nécessaire de faire des
opérations avancées.


`date`
    Converti une date depuis le format texte. Le résultat de la fonction est le
    nombre de jours écoulés depuis le 31 décembre 1899 (`1` correspond au 1er
    janvier 1900). Cela correspond à la manière dont Excel enregistre une
    valeur de type date.

    .. code:: toml

      "date('%d/%m/%Y', :B)"


`year`
    Extrait l’année (sur 4 chiffres) à partir d’une date. Si la date est donnée
    directement, il vaut mieux utiliser `substring` qui est plus simple
    d’accès. `year` devient utile s’il y a des calculs associés :

    .. code:: toml

      "year( date('%d/%m/%Y', :M) + date('%d/%m/%Y', '31/12/1999') )",


Les fonctions de groupes
------------------------

Les fonctions suivantes permettent de grouper les données ou de les mettre en
correspondance avec d’autres lignes du fichier.

Elles prennent se basent sur deux paramètres supplémentaires :

1. le premier étant les colonnes à identifier pour faire le regroupement,
2. le second étant l’ordre dans lequel les lignes doivent être triées.

    `counter([regroupement, …],[tri, …])`

   Le tri est ignoré si le regroupement est omi.

.. note::

    Quand une fonction de groupe est utilisée en combinaison du paramètre
    `uniq`, le critère de regroupement doit correspondre à la règle d’unicité.


`counter`
    Crée un compteur qui s’incrémente tant que les lignes sont identiques.

    L’exemple suivant va remettre le compteur à `1` dès que la société ou le
    titre change, et utilisera la date pour ordonner les valeurs dans ce
    groupe.

    .. code:: toml

      """counter(
            [:societe.H, :lib_titres.B],
            [date('%d/%m/%Y', :mouvements.C), :A]
        )"""


    La fonction peut également être utilisée pour ne conserver que les lignes
    uniques au sein d’une plage donnée (via un post-traitement où l’on ne garde
    que les valeurs à `1`)

    .. code:: toml

      "counter([:C], [:A]) = 1"

    Il est possible de construire plusieurs compteurs sur des groupes et des
    tris différents au sein d’un même fichier.

    Si le paramètre de tri n’est pas donné, l’application va compter le nombre
    d’éléments pour chaque groupe: toutes les cellules auront le même résultat.

`previous`
    La fonction `previous` permet d’aller chercher l’élément précédent dans les
    données.

    En fonction du critère de regroupement, la valeur renverra vide (si l’on
    est sur groupe différent), et la valeur précédente sera recherchée en
    fonction du paramètre de tri donné.

    `previous(expression, [regroupement, …], [tri, …])`

    .. code:: toml

      """previous(
        :ths.G,
        [:socs.EB, :socs.L, :ths.E],
        [:ths.H])"""

`sum`
    La fonction `sum` permet de calculer la somme d’une colonne donnée sur un
    critère de regroupement.

    Si le paramètre de tri n’est pas donné, l’application va calculer la somme
    totale en fonction du critère de regroupement : toutes les cellules auront
    le même résultat.

    `sum(expression, [regroupement, …], [])`

    .. code:: toml

      """sum(
        :shares.K,
        [:shares.A, :shares:D],
        [])"""

    Au contraire, si un tri est donné, l’application va accumuler la somme tout
    au long des valeurs rencontrées, et selon l’ordre du tri.

`min`
    La fonction `min` permet d’aller chercher le premier élément du groupe
    (c’est-à-dire le plus petit selon l’ordre de tri)


    `min(expression, [regroupement, …], [tri, …])`

    .. code:: toml

      """min(
        :mouvements.C,
        [:societe.A, :I],
        [:mouvements.C])"""

    exemple d’utilisation : rechercher la première date d’achat.

`max`
    La fonction `max` permet d’aller chercher le dernier élément du groupe.

    `max(expression, [regroupement, …], [tri, …])`