Version du 10 avril 2013 à 10:38

Présentation

Cette fonction est disponible à partir de la version 3.0

Le module d'import permet d'importer et de synchroniser de manière automatique la table des utilisateurs OpenFlyers ainsi que leurs validités à partir d'un fichier externe.

Ce module trouve notamment son application pour la synchronisation avec le fichier SMILE de la Fédération Française Aéronautique.

Ce module est décomposé en 3 étapes :

1. Le rapprochement qui permet de faire correspondre les lignes d'un fichier à importer avec la base de données OpenFlyers
2. Le test de mise à jour qui permet de déterminer, pour les lignes rapprochées, si une mise à jour de la ligne correspondante dans la base OpenFlyers est préconisée ou non
3. La mise à jour qui effectue des requêtes déterminées dans la base OpenFlyers pour les lignes qui ont été déterminées comme devant être mises à jour

Le processus d'import peut fonctionner de façon complétement automatique (via une synchronisation journalière à partir d'une source de données extérieure accessible via internet), de façon semi-automatique (la récupération des données est transparente et déclenchée par une simple validation sur un bouton, puis un tableau liste les lignes à synchroniser et une nouvelle validation permet de déclencher la mise à jour) ou de façon complétement manuellement (la récupération des données se fait alors par upload manuel et les lignes à mettre à jour peuvent être sélectionner ou dé-sélectionner manuellement).

Il est vivement conseiller d'éditer son fichier d'import pour remplacer tous les caractères accentués présents dans les noms des colonnes.

Formule de rapprochement

Le but est de trouver les utilisateurs se trouvant dans la base de données ayant des données semblables à ceux se trouvant dans le fichier. Nous allons donc compléter une formule de rapprochement pour effectuer cela.

Nous allons faire un rapprochement sur le nom et le prénom par une égalité. La formule de rapprochement est donc :

person::stripChars(CONCAT(#nom,#prnom)) = stripChars(CONCAT(person.last_name,person.first_name))

#nom correspond au champ "Nom" du fichier. #prnom correspond au champ "Prénom" du fichier. Ces variables sont affichées sur le tableau, en seconde ligne d'entête.

stripChars est une fonction faite maison permettant de retirer tout ce qui est tiret et espace d'un nom afin de faire un meilleur rapprochement entre le fichier et la base de données.

Lors d'un import, des lignes rouges du tableau généré passeront en orange. Les lignes oranges représenteront les utilisateurs rapprochés.

Formule de test de mise à jour

Une fois que nous avons les utilisateurs rapprochés, nous allons appliquer un autre filtrage : nous voulons les utilisateurs ayant une validité X qui a expiré. Il est possible d'utiliser des variables comme $validityId (en supposant que la variable $validityId a été précédemment créée et qu'une valeur lui a été affecté).

La requête de filtrage, appelée formule de test de mise à jour, est ainsi :

SELECT person.id, IF(validity.expire_date > '2010-12-31',1,0)
FROM person
LEFT JOIN validity
ON validity.person_id=person.id
WHERE validity.validity_type_id=$validityId

Le résultat fera rafraîchir le tableau généré. Des lignes oranges passeront en couleur verte et seront automatiquement cochées. Ces lignes vertes représentent les utilisateurs dont leur validité a expirée.

Ainsi, nous nous retrouvons avec 3 couleurs possibles qui correspondent à un code couleur.

Formule de mise à jour

Ensuite, nous allons mettre à jour les validités de ces utilisateurs. Nous laissons le tableau comme tel, nous ne cocherons ou décocherons aucune ligne. Seules les lignes vertes seront mises à jour (si une ligne orange est cochée manuellement, elle passera en verte pour indiquer quelle sera affectée par la mise à jour).

Nous supposons que la variable $validityId a déjà été créée et qu'elle correspond à la validité "FFA". La variable $person_id prendra l'id de l'utilisateur en base (c'est une variable prédéfinie dans notre moteur). La formule de mise à jour qui va être utilisée est :

INSERT INTO `validity` (`person_id`, `validity_type_id`, `expire_date`, `ident_value`)
VALUES ('$person_id', '$validityId', '2011-12-31', '#nlicence')
ON DUPLICATE KEY UPDATE `expire_date` = '2011-12-31', `ident_value` = #nlicence;

Cette requête va mettre à jour la date d'expiration de la validité X choisie des utilisateurs (représentés en ligne verte sur le tableau). Si un utilisateur ne possédait pas de validité X, celle-ci lui sera créée en lui complétant le numéro de licence (variable #nlicence, la valeur est récupérée depuis le tableau généré).

Un retour de message alertera du nombre de validités mises à jour : ce nombre sera de 0 au minimum et du nombre de lignes vertes cochées au maximum. S'il y avait 5 lignes vertes et que l'alerte nous indique que 4 validités seulement ont été mises à jour, cela signifie que la validité d'un utilisateur n'avait pas besoin d'être remise à jour.

Tableau de synchronisation

Code couleur

Les lignes du tableau de synchronisation peuvent avoir 3 couleurs :

• vert-sélectionnée par défaut : rapprochement effectué, mise à jour recommandée
• rouge-non sélectionnable : rapprochement impossible : impossible d'associer la ligne du fichier importé avec une entrée utilisateur en base de données
• orange-sélectionnable mais non sélectionnée par défaut : rapprochement effectué, mais mise à jour non préconisée (l'utilisateur est déjà à jour)

Mise en place de requêtes d'import pour SMILE

Nous partons sur la base d'un fichier CSV à importer qui a cette structure :

"N° licence";"Date de saisie";"Code renouvellement";"Date renouvellement";"Nom";"Prénom";"Date de naissance";"Sexe";
417965;02/01/10;"OK-RAC";02/01/10;"DUPONT";"CHRISTOPHE";26/02/70;"M";

Le but est de mettre à jour la validité "Licence FFA" que nous allons créer pour chacun des utilisateurs renseignés dans ce fichier.

La synchronisation peut s'opérer de 2 façons possibles selon le processus défini au sein de la structure aéronautique :

1. Si les adhérents paient directement la FFA (chèque à l'ordre de la FFA), alors aucun mouvement comptable n'est à enregistrer dans OpenFlyers. Il en est de même, si OpenFlyers ne gère pas la comptabilité de la structure aéronautique. Dans ces cas de figure, la synchronisation consiste simplement à mettre à jour la validité FFA.
2. Si les adhérents paient leur cotisation FFA à la structure aéronautique (chèque à l'ordre de la structure aéronautique) et que la gestion des comptes pilotes est faite dans OpenFlyers, alors il faut enregistrer en comptabilité les mouvements permettant de suivre les flux financiers.

Dans les 2 cas, certaine opération ne change pas : la "prise de licence FFA" devra être saisie dans SMILE. Cette opération peut être soit réalisée par l'adhérent lui-même soit centralisée par la structure aéronautique (auquel cas, les adhérents doivent remplir un formulaire papier).

• Dans le cas "2" (=paiement de la licence FFA à la structure aéronautique), l'encaissement du paiement devra être saisi dans OpenFlyers (cette opération peut être faite par l'adhérent lui-même : il s'agit pour l'adhérent de saisir un simple encaissement sur son compte pilote)
• Dans les 2 cas la mise en place d'une synchronisation par import du fichier SMILE est recommandée. Elle passe par des actions communes puis des actions spécifiques qui ont pour objet :
  1. De mettre à jour uniquement la validité "licence FFA" (cas 1)
  2. De mettre à jour la validité "licence FFA" ET de générer les écritures de vente correspondante de cette même validité (cas 2)

De plus, afin de mieux sécuriser le rapprochement des bases de données, nous préconisons d'effectuer un premier rapprochement sur les couples (nom, prénom) afin de générer les validités "licence FFA" puis d'effectuer les opérations de mise à jour non plus sur ces couples mais sur le numéro de licence FFA.

Actions communes

Création de la validité "licence FFA"

Dans Admin > Types de validités :

• En bas de la page, dans le champ texte à gauche du bouton Ajouter un type de validité, renseigner "Licence FFA"
• Cliquer sur le bouton Ajouter un type de validité
• Activer pour ce type de validité l'option Soumise à échéance (permet de renseigner la date butoir de cette validité)
• Activer pour ce type de validité l'option Gestion du code identifiant (permet de renseigner le numéro de licence FFA)
• Éventuellement activer Validité bloquante pour la saisie des vols (intéressant uniquement en mode ouverture+fermeture de vols)

Création des autres "validités FFA"

Dans le cas où le but est uniquement de contrôler que l'adhérent est à jour de sa cotisation, il n'est pas indispensable de créer les autres "validités FFA". C'est néanmoins recommandé car cela permet d'avoir une visibilité aussi bien pour l'adhérent que pour les gestionnaires sur ces éléments.

Donc, dans le cas où on crée les autres "validités FFA", il faut répéter l'opération de création de "licence FFA" pour les validités suivantes mais sans activer la Gestion du code identifiant :

• Assurance IA FFA de base
• Abonnement INFO-PILOTE
• Assurance FFA Plus option A
• Assurance FFA Plus option B
• RC Instructeurs FI-FE
• Assurance individuelle FI-FE

Création du champ additionnel "licenceFFAId"

Dans Admin > Utilisateurs > Imports > Champ(s) additionnel(s) :

• Renseigner Nom (le champ précédé d'un $) avec "licenceFFAId"
• Renseigner Intitulé avec "licence FFA"
• Renseigner Type de valeur avec dbObject::ValidityType
• Cliquer sur Ajouter

Création des autres champs additionnels "FFA"

Dans le cas où les autres types de validités FFA ont été créées, il faut alors également créer les autre champs additionnels suivants sur le modèle du champ additionnel "licenceFFAId" :

• AssuranceIAFFAId
• AbonnementIPId
• AssuranceFFAAId
• AssuranceFFABId
• RCInstructeurId
• AIInstructeurId

Paramétrage des imports des numéros de licence FFA

Dans Admin > Utilisateurs > Imports > Créer/Modifier :

• Cliquer sur Ajouter
• Mettre comme intitulé "Import des numéros de licence FFA"
• Mettre comme formule de rapprochement :

person::stripChars(CONCAT(#nom,#prnom)) = stripChars(CONCAT(person.last_name,person.first_name))

• Mettre comme formule de test de mise à jour :

SELECT person.id, 1
FROM person
LEFT JOIN merge_data ON person.id=merge_data.person_id
LEFT JOIN validity ON merge_data.person_id=validity.person_id AND validity.validity_type_id=$licenceFFAId
WHERE merge_data.person_id IS NOT NULL

• Mettre comme formule de mise à jour :

INSERT INTO validity (person_id, validity_type_id, ident_value, expire_date )
VALUES ('$person_id', '$licenceFFAId', '#nlicence', '2010-12-31')
ON DUPLICATE KEY UPDATE `ident_value` = '#nlicence';

• Sélectionner Licence FFA pour $licenceFFAId (licence FFA)
• Répéter cette mise en correspondance pour les autres champs
• Valider

Import des numéros de licence FFA

• Exporter côté SMILE l'exercice de l'année.
• Retourner dans OpenFlyers
• Aller dans Admin > Utilisateurs > Imports > Import des numéros de licence FFA
• Sélectionner ISO-8859-1 dans le champ Jeu de caractère CSV par défaut
• Cliquer sur l'icône Importer à droite du champ
• Sélectionner le fichier précédemment exporté depuis SMILE

• Patienter une quinzaine de secondes (une icône animée aide à cela)
• Lorsque le tableau s'affiche (avec normalement quasiment que des lignes vertes), noter les noms des personnes sur les lignes rouges (il s'agit de personnes qui n'ont pas pu être rapprochées: écriture du nom ou du prénom différemment) et pour lesquelles il faudra justement créer à la main la validité "licence FFA" pour les rapprochements ultérieurs

• Aller tout en bas du tableau et cliquer sur le bouton Mettre à jour
• Il ne reste plus qu'à aller dans Utilisateurs > Types de validités, cliquer sur l'icône Liste du type de validité "Licence FFA" pour vérifier que tout c'est bien passé : chaque adhérent doit disposer maintenant d'une validité "licence FFA" avec son numéro de licence FFA et une date d'échéance au 31/12/2010.

Exporter depuis SMILE

• Se connecter à SMILE en tant que "club"
• Aller dans Exportation de données > liste complète des licenciés
• Cocher Licenciés récents
• Cliquer sur l'icône Excel
• Puis, depuis OpenOffice ou Excel, enregistrer l'export excel sous csv avec comme séparateur ";"

Mise à jour simple de la validité "licence FFA"

Les actions communes doivent avoir été réalisées au préalable.

Synchronisation de la validité "licence FFA" et génération des écritures comptables associées

Le but est de facturer l'achat et/ou le renouvellement de la validité "Licence FFA" pour chacun des adhérents concernés en plus de la mise à jour de leur validité "licence FFA".

Les actions communes doivent avoir été réalisées au préalable.

Procédure de création de tarification

Création du compte fournisseur "FFA"

Dans Admin > Comptes > Lister les comptes > Comptes fournisseur :

• Renseigner Nom avec "FFA"
• Renseigner Compte d'export avec "401FFA"
• Cliquer sur Ajouter

Création des types de vente

Dans Admin > Ventes > Types de vente, créer le type de vente "Licence FFA" :

• Renseigner Nom avec "Licence FFA"
• Laisser Unité sur Sans unité
• Laisser Echelle de précision sur 0
• Ne pas activer l'option Vente libre : cela donnerait la possibilité aux adhérents d'acheter directement la validité "licence FFA" par le biais d'OpenFlyers alors qu'il faut forcément faire une saisie dans SMILE pour que celle-ci soit prise en compte par la FFA.
• Cliquer sur Ajouter
• Répéter cette opération de création avec les types de vente suivant :
  • Abonnement INFO-PILOTE
  • Assurance FFA Plus option A
  • Assurance FFA Plus option B
  • Assurance IA FFA de base
  • Assurance individuelle FI-FE
  • RC Instructeurs FI-FE

Création des ventes

• Si cela n'est pas fait, activer le droit vente pour tous pour le profil utilisé pour cette mise en place

Dans Admin > Ventes > Tarification des ventes, créer la vente "Licence FFA" :

• Cliquer sur Ajouter une ligne de tarification
• Renseigner Nom par "Licence"
• Renseigner Formule par le prix tarifaire. Par exemple, 1 (la valeur ne sera pas prise en compte dans l'import : c'est le tarif indiqué dans le fichier csv qui sera utilisé)
• Sélectionner "Licence FFA" dans Type de vente
• Sélectionner Compte membre en Type de compte à débiter
• Sélectionner Compte fournisseur en Type de compte à créditer puis choisir "FFA" en Compte fournisseur à créditer
• Cliquer sur Valider
• Répéter cette opération pour les ventes :
  • Assurance IA FFA
  • Abonnement INFO-PILOTE
  • Assurance FFA Option A
  • Assurance FFA Option B
  • RC Instructeurs FI-FE
  • Assurance Individuelle Instructeur

Création des ventes de validités

Dans Admin > Ventes > Validités à vendre, créer la vente de validité "Licence FFA" :

• Renseigner Tarification avec "Licence"
• Renseigner Validité avec "Licence FFA"
• Renseigner Formule de création avec la date d'échéance à mettre par défaut lors de l'achat de la validité. Par exemple "2011-12-31" (format anglo-saxon)
• Renseigner Formule de mise à jour avec la date d'échéance à mettre par défaut lors du renouvellement de validité. Par exemple "2011-12-31" (format anglo-saxon)
• Cliquer sur Ajouter
• Répéter cette opération pour les ventes de validités :
  • Assurance IA FFA
  • Abonnement INFO-PILOTE
  • Assurance FFA Option A
  • Assurance FFA Option B
  • RC Instructeurs FI-FE
  • Assurance Individuelle Instructeur

Une validité ne peut être associée qu'à une seule règle de tarification.

Création de l'import SMILE avec tarification

Dans Admin > Utilisateurs > Imports > Créer/Modifier :

• Cliquer sur Ajouter
• Mettre comme intitulé Import SMILE avec écritures comptables
• Mettre comme formule de rapprochement :

validity::TRIM(LEADING '0' FROM #nlicence) = validity.ident_value 

• Mettre comme formule de test de mise à jour :

SELECT person.id, IF(validity.expire_date < '2011-01-01' OR validity.expire_date IS NULL,1,0)
FROM person
LEFT JOIN validity ON validity.person_id=person.id
WHERE validity.validity_type_id=$licenceFFAId
UNION ALL
SELECT person.id, 1
FROM person
LEFT JOIN  validity ON validity.person_id=person.id AND validity_type_id=$licenceFFAId
WHERE validity_type_id IS NULL

• Mettre comme formule de mise à jour :

CALL saleValidity($licenceFFAId, $person_id, '#lic');
CALL saleValidity($AssuranceIAFFAId, $person_id, '#assuranceiadebase');
CALL saleValidity($AbonnementIPId, $person_id, '#abonnementinfopilote');
CALL saleValidity($AssuranceFFAAId, $person_id, '#ffaplusoptionacapitalde50000');
CALL saleValidity($AssuranceFFABId, $person_id, '#ffaplusoptionbcapitalde100000');
CALL saleValidity($RCInstructeurId, $person_id, '#rcinstructeursfife');
CALL saleValidity($AIInstructeurId, $person_id, '#assuranceindividuellefife');

• Sélectionner "Licence FFA" pour "$licenceFFAId (licence FFA)"
• Répéter cette mise en correspondance pour les autres champs
• Valider

Import des utilisateurs

On part sur la base d'un fichier CSV ayant cette structure :

nom;prenom;email;adresse postale;code postal;ville;etat;pays;telephone fixe domicile;telephone travail;telephone portable;sexe;date naissance;nationalite
Dupont;Jean;jean.dupont@test.fr;1 rue Qui Vive;33000;Bordeaux;;France;0102030406;;0606060606;M;1960-05-14;FR
Durand;Pierre;pierre.durand@test.fr;;;;;;;;;;;FR

Remarques :

• Les utilisateurs qui vont s'afficher en vert sont ceux qui n'existaient pas. Ceux en orange représentent ceux déjà existant.
• Lors de l'ajout d'un nouvel utilisateur, l'identifiant créé automatiquement correspondra à la première lettre du prénom suivi du nom. Tout élément non alphanumérique sera éliminé de l'identifiant.
• Le mot de passe par défaut sera l'identifiant
• Lors de la mise à jour d'un utilisateur, seulement les données de leur fiche personnelle seront mis à jour (e-mail, adresse, numéro de téléphone, sexe, date de naissance, nationalité). Puis le profil à affecter sera ajouté en plus de ceux déjà existant.
• Dans le fichier d'import :
  • Le format du sexe doit correspondre à "M" pour un homme et "F" pour une femme.
  • La date de naissance doit être au format "AAAA-MM-JJ".

Création de l'import des utilisateurs

Dans Admin > Utilisateurs > Imports > Créer/Modifier :

• Cliquer sur Ajouter
• Mettre comme intitulé "Import des utilisateurs"
• Mettre comme formule de rapprochement :

person::stripChars(CONCAT(#nom,#prenom)) = stripChars(CONCAT(person.last_name,person.first_name))

• Mettre comme formule de test de mise à jour :

SELECT id, 0
FROM person
UNION
SELECT null, 1

• Mettre comme formule de mise à jour :

createOrUpdateUser($person_id, '#prenom', '#nom', '#email', '#adressepostale', '#codepostal', '#ville', '#etat', '#pays', '#telephonefixedomicile', '#telephonetravail', '#telephoneportable', '#sexe', '#datenaissance', '#nationalite');

• Valider

Utilisation de l'import des utilisateurs

Dans Admin > Utilisateurs > Imports > Import des utilisateurs :

• Sélectionner UTF-8 dans le champ Jeu de caractère CSV par défaut
• Importer le fichier CSV pour détecter qui doit être remis à jour
• Sélectionner les utilisateurs désirés
• Cliquer sur Mettre à jour pour déclencher l'ajout ou la mise à jour des utilisateurs désirés

Mise à jour des soldes des comptes Clients par écritures comptables

L'objectif de cet import est de permettre la mise à jour des soldes des comptes "Clients" en y générant des écritures comptables :

• Un mouvement va débiter le compte "Clients" si le solde est négatif ou va créditer le compte "Clients" si le solde est positif
• Une écriture de contre-partie va s'effectuer sur un autre compte qui sera à définir. On prendra comme exemple un compte bilan "Report à nouveau"

On part sur la base d'un fichier CSV ayant cette structure :

ID Openflyers;nom;prenom;solde
4;Dupont;Jean;100
5;Durand;Pierre;-150

Création du champ additionnel "Type de compte à mettre à jour"

Dans Admin > Utilisateurs> Imports > Champ(s) additionnel(s) :

• Renseigner Nom (le champ précédé d'un $) avec "accountTypeId"
• Renseigner Intitulé avec "Type de compte à mettre à jour"
• Renseigner Type de valeur avec dbObject::AccountType
• Cliquer sur Ajouter

Création du champ additionnel "Compte de contre-partie"

Dans Admin > Utilisateurs> Imports > Champ(s) additionnel(s) :

• Renseigner Nom (le champ précédé d'un $) avec "compensationAccountId"
• Renseigner Intitulé avec "Compte de contre-partie"
• Renseigner Type de valeur avec dbObject::Account
• Cliquer sur Ajouter

Création du champ additionnel "Date de flux"

Dans Admin > Utilisateurs> Imports > Champ(s) additionnel(s) :

• Renseigner Nom (le champ précédé d'un $) avec "flowDate"
• Renseigner Intitulé avec "Date de flux"
• Renseigner Type de valeur avec date
• Cliquer sur Ajouter

Création de l'import de mise à jour des soldes des comptes Clients via écritures comptable

Dans Admin > Utilisateurs > Imports > Créer/Modifier :

• Cliquer sur Ajouter
• Mettre comme intitulé "Mise à jour des soldes des comptes Clients via écritures comptable"
• Mettre comme formule de rapprochement :

• Dans le cas où on rapproche par le nom et le prénom :

person::stripChars(CONCAT(#nom,#prenom)) = stripChars(CONCAT(person.last_name,person.first_name))

• Dans le cas où on rapproche par l'ID :

person::#idopenflyers=person.id

• Mettre comme formule de test de mise à jour :

SELECT person.id, 1
FROM person
LEFT JOIN account ON (account.owner_id=person.id AND account.category=2)
WHERE account.account_type=$accountTypeId

• Mettre comme formule de mise à jour :

createAccountEntries($person_id, $accountTypeId, $compensationAccountId, #solde, '$flowDate');

• Sélectionner le type de compte Clients pour le champ $accountTypeId (Type de compte à mettre à jour)
• Sélectionner le compte bilan "Report à nouveau" pour le champ $compensationAccountId (Compte de contre-partie)
• Sélectionner la date des écritures pour le champ $flowDate (Date de flux)
• Valider

Utilisation de l'import de mise à jour des soldes des comptes Clients via écritures comptable

Dans Admin > Utilisateurs > Imports > Mise à jour des soldes des comptes Clients via écritures comptable :

• Sélectionner UTF-8 dans le champ Jeu de caractère CSV par défaut
• Importer le fichier CSV pour détecter qui doit être remis à jour
• Sélectionner les utilisateurs désirés
• Cliquer sur Mettre à jour pour déclencher la mise à jour des comptes Clients des utilisateurs désirés