Comment créer les stamp à ajouter sur PortaBilling
Update on 2009-09-22 by Nicolas Jorand
Created on 2009-09-02 by Nicolas Jorand
Switzernet
Le but est d’ajouter le stamp < order on=… /> sur PortaBilling aux clients qui ont été parrainés. Au préalable il faut récupérer les e-mails http://switzernet.com/public/090818-export-outlook-thunderbird-headers-to-excel/. Il faut ensuite mettre les informations recueillies au bon format. Avec celles-ci on va rechercher les clients qui sont enregistrés sur PortaBilling à l’aide d’une requête SQL qu’on aura généré. Et enfin, comparer les informations extraites des e-mails avec celles obtenues avec la requête SQL et pour finir créer le stamp.
Fonctions Excel utilisées
Requête SQL
Création des stamp
[fonctions_excel] Fonction Excel utilisées
Les fonctions Excel sont utilisées notamment pour créer la requête SQL, comparer deux listes de clients et créer le stamp. Pour ceci, il est nécessaire de combiner plusieurs de ces fonctions. Voici la description de chacune d’elles.
Concatenate
Description : cette fonction permet d’assemble plusieurs morceaux de texte en un seul
Syntaxe : Concatenate(texte1,texte2,texte3,…)
Paramètres : on donne en paramètre toutes les parties de texte que l’on souhaite assembler et on les sépare par une virgule
Exemple :
Mid
Description : cette fonction permet de sélectionner une partie d’un texte
Syntaxe : Mid(texte,position départ,nombre caractères)
Paramètres : on donne le texte dans lequel on veut récupérer une partie seulement, puis on donne le numéro du caractère à partir duquel on veut commencer à sélectionner le texte et enfin le nombre de caractères que l’on veut avoir
Exemple :
If
Description : cette fonction permet de choisir entre deux valeurs suivant si la condition donnée en paramètre est vraie ou fausse
Syntaxe : If(condition,valeur si vrai,valeur si faux)
Paramètres : on entre la condition ainsi que les valeurs à retourner si elle est remplie ou non
Exemple :
And
Description : cette fonction retourne TRUE si toutes les conditions sont remplies et FALSE si une ou plusieurs de ces conditions ne sont pas remplies
Syntaxe : And(condition1,condition2,condition3,…)
Paramètres : on donne toutes les conditions que l’on doit tester
Exemple :
IsNA
Description : cette fonction test s’il y la valeur en paramètre est invalide (#N/A) et dans ce cas elle retourne TRUE. Si la valeur est valide, elle retourne FALSE
Syntaxe : Isna(valeur)
Paramètres : on entre la valeur que l’on désir tester
Exemple :
VLookup
Description : cette fonction permet de rechercher une valeur dans un tableau. Si celle-ci est trouvée, la fonction retourne la valeur de la case se trouvant sur la même ligne que la valeur trouvée dans le tableau et à la colonne entrée en paramètre. Attention, le tableau de recherche doit être trié dans le sens ascendant !!!
Syntaxe : Vlookup(valeur,tableau,colonne,valeur pas exacte)
Paramètres : on entre la valeur à rechercher ainsi que le tableau dans lequel elle est recherchée. Il faut aussi donner l’index de colonne dans le tableau de la valeur à retourner et enfin si on recherche la valeur exacte (mettre FALSE) ou une valeur approximative
Exemple :
Text
Description : cette fonction retourne un texte contenant la valeur entrée dans un format que l’on a spécifié
Syntaxe : Text(valeur,format)
Paramètres : on donne la valeur que l’on veut mettre dans le format également entré en paramètre
Exemple :
Hyperlink
Description : cette fonction créer un lien avec un document ou une adresse d’un site web
Syntaxe : Hyperlink(chemin ou Url,nom)
Paramètres : on entre soit le chemin du document ou l’adresse du site web (Url) ainsi que le nom sous lequel on veut le lien
Exemple :
""">"""> Cellule<>""
Description : test si la case contient quelque chose ou si elle est vide
Exemple :
Symbole $
Description : sert à bloquer une cellule dont les informations sont utilisées dans une formule.
Exemple :
Quand on écrit une formule en prenant une valeur contenue dans une autre cellule, par exemple en B1 on met =A1, et que l’on copie vers le bas, on aura en B2 la formule =A2 (en B3, =A3 etc).
Si on copie vers la droite, on aura en =B1 en C1 (=C1 en D1 etc).
On peut ajouter le $ de trois manières différentes :
-
$A$1, on garde toujours la valeur de la même cellule quelque soit le sens de la copie
-
$A1, on garde la colonne uniquement
-
A$1 on garde la ligne uniquement
[requete] Requête SQL
Pour rappel le fichier Excel contenant les informations récupérées des e-mails était le suivant :
Il faut maintenant récupérer le numéro de téléphone du Subject et le mettre au format 412x5xxxxxx, ainsi que mettre la date au format yymmdd.
La description de chaque fonction Excel qui sont utilisées par la suite est dans la partie Fonction Excel utilisées. Commencez par sauvegarder ce fichier sous un notre nom (File->Save As).
Mettre la date au format yymmdd
Pour mettre la date dans le format voulu, il faut sélectionner la colonne entière puis clique droite->Format Cell. Dans l’onglet Number, allez dans Custom et taper le format de la date désiré puis Ok :
 
Récupérer et mettre le numéro au format 412x5xxxxxx
Pour ceci, nous allons utiliser deux fonctions Excel : Concatenate et Mid
Voici la formule complète :
=IF(Aj<>"",CONCATENATE("41",MID(Aj,7,2),MID(Aj,10,3),MID(Aj,14,4)),"")
j est le numéro de la ligne
1er MID : on récupère 2x
2ème MID : on récupère 5xx
3ème MID : on récupère xxxx
Création de la requête SQL
Avant de faire la requête complète, on commence par créer la partie de la requête SQL qui concerne une seule ligne (donc un seul client).
Nous avons besoin de deux formules, une pour la première ligne et une seconde pour les autres :
=IF(AND(B2<>"",D2<>""),CONCATENATE("and ((A.id like '%",D2,"%' and (C.email='",B2,"' or C.cont2='",B2,"'))"),"")
=IF(AND(Bj<>"",Dj<>""),CONCATENATE(" or (A.id like '%",Dj,"%' and (C.email='",Bj,"' or C.cont2='",Bj,"'))"),"")
j est le numéro de la ligne
La seule différence est que pour le premier le début de la requête commence par un and (voir dans le CONCATENATE) et que pour les autres on a un espace suivit d’un or. On voit que les fonctions IF, AND, \">cellule<>"" et CONCATENATE sont utilisées.
Voici ce que l’on obtient :
Le début et la fin de la requête complète sont toujours pareils, seules les parties ci-dessus (donc celles avec les informations des clients) changent. On a donc toujours pour le début :
select C.i_customer, C.name, C.email,C.cont2 from Customers as C, Accounts as A where A.i_customer=C.i_customer
et pour la fin :
) into outfile '/Chemin.../.../nom_fichier.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
il faut spécifier le chemin ainsi que le nom que vous voulez donner au fichier CSV de sortie.
 Les cellules dans Excel ont une taille limitée. La requête SQL étant très longue suivant le nombre de client, celle-ci ne pourra pas être contenue dans une seule cellule. Il faut donc faire une requête pour un nombre maximum de 300 clients, après quoi il faudra faire une nouvelle requête. Ceci se fait très simplement, il suffit d’ajouter une ligne vide tous les 300 et de changer le fichier CSV de sortie.
Client de 1 à 300 :
) into outfile '/Chemin.../.../nom_fichier1.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
De 301 à 600 :
) into outfile '/Chemin.../.../nom_fichier2.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Etc…
Il ne reste plus qu’à assembler le tout : début, partie(s) client(s) et fin. Pour ceci nous avons également besoin de deux formules (première ligne et les autres) :
=IF(F2<>"",IF(F3<>"",CONCATENATE($J$3,F2),CONCATENATE($J$3,F2,$J$4)),"")
=IF(Fj<>"",IF(Fk<>"",CONCATENATE(Hi,Fj),CONCATENATE(Hi,Fj,$J$k)),"")
i, j et k sont des numéros de ligne
Au final, on a ceci :
La requête SQL complète qu’il faut prendre est celle de la dernière ligne de chaque partie de 300 clients, les autres au-dessus n’ont pas la fin : ) into outfile …
Il est utile pour la suite de trier les informations dans l’ordre alphabétique des e-mails, pour ceci sélectionnez Subject, ToAddress, Received et tel puis aller dans l’onglet Data->Filter->AutoFilter :
Un bouton apparaît à côté de Subject,…,tel :
Cliquez sur celui à côté de ToAddress et choisissez Sort Ascending :
On peut amélioré la visibilité en rajoutant un peu de couleur, sélectionner la colonne entière puis clique droit->Format Cells->Onglet Patterns :
Vous pouvez prendre le fichier Excel contenant déjà les formules sur [xls].
Voilà à quoi ressemble la requête SQL :
select C.i_customer, C.name, C.email,C.cont2 from Customers as C, Accounts as A where A.i_customer=C.i_customer and ((A.id like '%412x5xxxxxx%' and (C.email='nom@email.ch' or C.cont2='nom@email.ch')) or (A.id like '%412x5xxxxxx%' and (C.email='nom@email.ch' or C.cont2='nom@email.ch')) or (A.id like '%41225041234%' and (C.email='nom@email.ch' or C.cont2='nom@email.ch')) or (A.id like '%412x5xxxxxx%' and (C.email='nom@email.ch' or C.cont2='nom@email.ch')) or (A.id like '%412x5xxxxxx%' and (C.email='nom@email.ch' or C.cont2='nom@email.ch')) or (A.id like '%412x5xxxxxx%' and (C.email='nom@email.ch' or C.cont2='nom@email.ch')) or (A.id like '%412x5xxxxxx%' and (C.email='nom@email.ch' or C.cont2='nom@email.ch')) or (A.id like '%412x5xxxxxx%' and (C.email='nom@email.ch' or C.cont2='nom@email.ch')) or (A.id like '%412x5xxxxxx%' and (C.email='nom@email.ch' or C.cont2='nom@email.ch'))) into outfile '/Chemin.../.../nom_fichier.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Exécution et récupération du résultat de la requête SQL
Pour exécuter la requête, connectez vous à la base de données SQL et copiez/collez la requête créée avec Excel. Le résultat se trouve dans le fichier CSV à l’endroit que vous avez spécifié. On peut récupérer ce fichier sur notre ordinateur à l’aide de la commande SCP. On peut exécuter celle-ci avec Cygwin. La commande est la suivante :
scp nom_utilisateur@serveur:/Chemin/.../fichier_a_copier.csv nom_copie.csv
Le fichier nom_copie.csv se trouve dans le dossier de Cygwin->home->Administrator. Déplacez-le dans un de vos dossiers. Voici à quoi ressemble ce fichier :
Il est tout à fait normal qu’il y ait moins de clients récupérés avec la requête SQL qu’avec les e-mails
Comme pour le fichier précédent, il faut trier les informations dans l’ordre alphabétique des e-mails (colonne C). Insérez une ligne avant les données récupérées et nommer chaque colonne :
[stamp] Création des stamp
Il nous faut créer un stamp pour chacun des clients récupérés avec la requête SQL. Nous avons besoin de combiner les informations des deux fichiers Excel vu ci-dessus. Ouvrez un nouveau fichier Excel et copiez-y les colonnes ToAddress, Received et Tel du fichier où la requête SQL est créée. Du fichier contenant le résultat de la requête SQL, copiez les colonnes en respectant l’ordre suivant : mail1, mail2, i_customer et enfin name. Le fichier obtenu est comme suit :
Nous pouvons maintenant récupérer les e-mails des clients qui ont été retrouvés par la requête SQL à l’aide de la formule suivante :
=IF(IF(ISNA(VLOOKUP(A2,E:E,1,FALSE)),"",VLOOKUP(A2,E:E,1,FALSE))="",IF(ISNA(VLOOKUP(A2,F:F,1,FALSE)),"",VLOOKUP(A2,F:F,1,FALSE)),VLOOKUP(A2,E:E,1,FALSE))
En deux mots cette formule cherche l’e-mail de ToAddress dans mail1 puis dans mail2 s’il n’est pas trouvé dans mail1. S’il l’e-mail est trouvé, il est affiché, sinon la case reste vide. Voici ce qu’on obtient avec l’exemple ci-dessus :
On voit que b, g et j n’ont pas été trouvés ni dans mail1 ni dans mail2. Par contre, d est absent dans mail1, mais trouvé dans mail2.
Une fois que les e-mails sont trouvés, il faut récupérer la date, le téléphone, le i_customer et le nom de chaque client. Les formules à utiliser sont les suivantes :
Date :
=IF(J2<>"",TEXT(VLOOKUP(J2,A2:B103,2,FALSE),"yymmdd"),"")
Tel :
=IF(J2<>"",VLOOKUP(J2,A2:C103,3,FALSE),"")
i_customer:
=IF(ISNA(IF(J2<>"",VLOOKUP(J2,E:G,3,FALSE),"")),VLOOKUP(J2,F:G,2,FALSE),IF(J2<>"",VLOOKUP(J2,E:G,3,FALSE),""))
Name:
=IF(ISNA(IF(J2<>"",VLOOKUP(J2,E:H,4,FALSE),"")),VLOOKUP(J2,F:H,3,FALSE),IF(J2<>"",VLOOKUP(J2,E:H,4,FALSE),""))
Le fichier est le suivant :
Maintenant que nous avons toutes les informations que nous voulons, nous pouvons créer le stamp. Pour facilité l’ajout de ce stamp, nous pouvons créer un lien qui va ouvrir une page web directement dans la partie du client concerné dans PortaBilling :
Stamp :
=IF(J2<>"",CONCATENATE(""),"")
Page Web :
=IF(J2<>"","http://unappel.ch/public/090831-link-stamp-order/?i_customer="&M2,"")
Lien :
=IF(J2<>"",HYPERLINK(Q2,"go"),"")
Remarque : si les go ne se mettent pas en bleu et souligner, vous pouvez le faire manuellement comme ça on voit tout de suite que c’est un lien cliquable.
Avant de cliquer sur le lien, il faut être connecté sur Portabilling
Voici le fichier obtenu :
On peut également ajouter de la couleur pour améliorer la lisibilité :
Vous pouvez prendre le fichier Excel contenant déjà les formules sur [xls]
* * *
Dostları ilə paylaş: |
