Dokos
Documentation
Cas d'usage

Rapports SQL

Créer un rapport à partir d'une requête SQL, avec l'exemple d'une liste quotidienne des événements et de leurs participants.

Les rapports SQL vous permettent de créer des rapports qui seront définis par les requêtes SQL. On peut ajouter diverses données qui seront stockées dans le logiciel DOKOS pour obtenir ces rapports.

Cas d'usage <Mappemonde>

Vous avez un besoin de traiter les différentes réservations chaque jour. Et vous n'avez pas toutes les informations sur les événements.

Il vous faut une liste qui puisse clairement vous indiquer ou à vos clients le jour, le type événement, le lieu, les participants, le thème, etc.

Solution pour la création du rapport

1.1 Créer événement

Ouvrez la liste Événement (icône loupe de la barre latérale gauche ou Ctrl+K), puis cliquez sur + Créer un·e Événement.

Remplissez les champs suivant :

  • L'objet
  • Commence le
  • Se termine le
  • Les participants (onglet Participants et références)
  • La description

Enregistrez l'événement. Vous pouvez ensuite ajouter les personnes assignées avec Assigner dans la barre latérale de droite.

2.1 Créer un rapport

  1. Cliquez sur l'icône loupe de la barre latérale gauche (ou Ctrl+K) et saisissez Rapport.
  2. Cliquez sur Nouveau(elle) Rapport. Vous pouvez aussi cliquer sur + Créer un·e Rapport dans la liste des rapports.

Une fois sur la page de création du rapport

  1. Saisissez un Nom du Rapport
  2. Sélectionnez dans Doctype de Réf. le type de document que vous voulez rattacher au rapport Pour ce cas, choisissez Événement (Event).
  3. Dans Type de rapport, mettez Rapport de Requête
  4. Enregistrez

Le rapport est maintenant créé.

Cela permet de créer un rapport qui peut gérer des codes SQL. (SQL est une requête de base de données et un langage de programmation, principalement utilisé pour accéder aux données et aux requêtes.)

L'avantage de l'utilisation de ce langage est que les utilisateurs peuvent fortement personnaliser le format, le contenu de la table et peuvent connecter plusieurs tables de données.

2.2 Requête SQL

2.2.1 Présentation des données des documents sur DOKOS

Comment récupérer les donnnées du document ?

  1. Première façon
  • Cliquez sur les trois points en haut à droite.
  • Faites Personnaliser pour aller sur la page du formulaire.

  • Dans l'onglet Détails, le champ Entrez le type de formulaire contient Événement (Event)

Dans l'onglet Formulaire, vous pouvez voir tous les champs de l'événement. Sélectionnez un champ pour afficher son nom (Nom) dans le panneau de droite.

  1. Deuxième façon
  • Allez dans Type de document, vous trouverez toutes les données de chaque document.

Pour accéder à la liste Type de document, allez sur :

Cliquez sur l'icône loupe de la barre latérale gauche (ou Ctrl+K) et recherchez Type de document.

Attention les noms des documents sont en anglais

Pour aller sur le formulaire Événement, il faut taper Event dans le filtre ID de la liste.

Rédigez les codes pour définir le contenu de votre rapport.

2.2.2 Les codes SQL et ajout des colonnes

Voici le format SQL :

SELECT 'les colonnes que vous voulez choisir' FROM `tableau`

La table d'un type de document s'appelle tab suivi du nom anglais du type : tabEvent pour Événement, tabSales Invoice (entre accents graves) pour Facture de vente.

Un rapport de requête n'accepte que les requêtes de lecture : elles commencent par SELECT ou EXPLAIN. Une autre requête provoque l'erreur « Seules les requêtes en lecture seule sont autorisées ».

Si l'événement contient les champs suivants :

  • Sujet
  • Commence le
  • Termine le
  • Les personnes attribuées
  • La description

Il faut écrire sous "Requête" :

SELECT name, subject, starts_on, ends_on, _assign, description
FROM tabEvent
Les champs name et _assign existent sur tous les documents. Ils ne s'affichent pas dans le formulaire.

Pour ajouter les colonnes il faut :

  • Dans la section Colonnes, cliquez sur Ajouter un rang
  • Indiquez le Nom du champ
  • Indiquez une Étiquette
  • Sélectionnez un Type de champ
  • Enregistrer
Astuce — Recherche de champ par autocomplétionLors de l'ajout d'une colonne dans un rapport, le champ de sélection du nom de champ propose une autocomplétion : tapez les premières lettres du nom recherché pour filtrer la liste. C'est particulièrement utile sur les types de documents comportant de nombreux champs.

Le nombre de colonnes doit être égal au nombre de champs sélectionnés après SELECT.

  • Une fois le rapport enregistré, cliquez sur Afficher le rapport en haut à droite pour afficher les résultats de la requête.

Pour voir le résultat de la requête en format PDF :

  • Cliquez sur les trois points en haut à droite et cliquez sur PDF pour générer un PDF.

2.3 Réserver une ressource et connectez les tableaux différents

Pour afficher les informations de la ressource réservée dans la liste.

  • Réservez une salle depuis l'Événement : Dokos crée une Réservation de ressource.
  • Ajoutez des codes SQL pour connecter le tableau Réservation de ressource et le tableau Event.
Les captures d'écran de cette section montrent l'ancienne Réservation d'articles (Item Booking). Depuis la v5, ce type de document est remplacé par la Réservation de ressource (Resource Booking) de l'application Bookings.

1. Première étape

  • Enregistrez l'événement, puis cliquez sur le bouton Réserver une ressource dans Événement.
  • Choisissez les salles (la salle doit être créée à l'avance comme Ressource réservable), puis cliquez sur Réserver des ressource(s). Dokos crée une réservation Non confirmé aux dates de l'événement.
  • Ouvrez la réservation de ressource liée à l'Événement.
  • Indiquez Commence le, Se termine le et le Statut
  • Enregistrez

2. Deuxième étape : changer les codes pour connecter les 2 tableau

  • Utiliser LEFT JOIN dans la requête SQL. Fonction qui permet de connecter le tab1 et tab2.

S'il y a deux colonnes identiques dans les deux tableaux, il faut utiliser LEFT JOIN ... ON... pour connecter ces deux tableaux.

Voici le code qu'il faut utiliser :

SELECT t1.n, t1.b, t1.c, t2.d, t2.e
FROM tab1 AS t1
LEFT JOIN tab2 AS t2
ON t1.n = t2.f

as : fonction pour nommer le tableau dans ces codes pour simplifier

Ici, car la colonne "e" dans tab1 et la colonne "n" dans tab2 ont les mêmes données. Nous utilisons donc LEFT JOIN...ON... pour joindre les deux tables. De cette façon, les t2.d et t2.e que nous lisons sont toutes des données qui correspondent à t1.n.

On pourrait utiliser t1.n pour indiquer la colonne n qui est à l'origine du tableau t1. De cette façon, on peut facilement distinguer différentes colonnes n.

(Ici, vous pouvez connaître plus d'informations de différents JOIN)

Dans notre cas actuel, on peut observer les données de Evénement et Réservation de ressource. Le nom de Evénement c'est 'EV00001', le "event" de Réservation de ressource c'est aussi 'EV00001'. Donc on peut l'utiliser en SQL.

Attention ! Si le nom de tableau est composé de plusieurs mots, il faut utiliser backtick pour le composer en ensemble.

Exemple, le tableau de Resource Booking est tabResource Booking. La ressource réservée est dans le champ booking_resource.

SELECT e.name, e.subject, e.starts_on, e.ends_on, e._assign, e.description, ib.booking_resource, ib.status
FROM tabEvent as e
LEFT JOIN `tabResource Booking` as ib
ON e.name = ib.event

Comme code ici, on pourrait obtenir le "booking_resource" et le "status" de «Réservation de ressource» quand son "event" est 'EV00001' Laisse regarder le résultat.

BINGO ! Il est aussi possible de connecter plus de tableaux qui nous permet de lire plus d'informations !
De cette façon, je peux avoir une liste très complète, c'est vraiment super !

2.4 Ajout du filtre

Sur la page Rapport, il y a toujours un filtre.

Vous pouvez placer les conditions que vous souhaitez filtrer. Vous pouvez sélectionner la date, le statut ou d'autres données que vous souhaitez filtrer. La méthode de filtrage peut être réalisée en sélectionnant des options, ou en sélectionnant des dates, etc.

Après avoir placé un filtre ici, vous pouvez utiliser la fonction WHERE...AND...AND... dans la Requête suivante pour utiliser le filtre que vous avez ajouté.

ex. Vous pouvez ajouter

WHERE nom du champ 1 = %("Nom du champ 2" de filtre)s

à la fin de la requête

Dans la situation actuelle, nous pouvons d'abord mettre en place un filtre sur starts_on et ends_on. Choisissez leur type de données comme Date, puis ajoutez-le à la fin de la requête.

WHERE e.starts_on >= %(starts_on)s AND e.ends_on <=%(ends_on)s

De cette façon, nous pouvons sélectionner l'heure de début et l'heure de fin dans les résultats affichés !

Concernant le status de salle, vous pouvez également ajouter un status dans Filtres. Dans Type de champ on sélectionne Sélectionner. Mais ce qui est un peu différent, c'est qu'il implique différentes options. Ici, vous devez cliquer sur l'icône crayon à droite du filtre pour ouvrir la ligne, puis l'ajouter dans Options 'Confirmed' 'Not confirmed'

Ensuite, comme précédemment, nous ajoutons du code à la fin

WHERE e.starts_on >= %(starts_on)s AND e.ends_on <=%(ends_on)s
AND ib.status = %(status)s

On voit ensemble les résultats d'affiches.

Attention! Quand vous ajoutez des filtres, le contenu de la page est modifié donc il faut recharger la page en cliquant sur le bouton dans le coin supérieur droit.

3. Améliorer le format

Vous pouvez rédiger le rapport que vous souhaitez pour améliorer le format.

Voici le code rédigé :

SELECT e.name, e.subject, e.event_category, REPLACE(CONCAT(CONCAT(e.starts_on,'<br>to<br>'), e.ends_on),'.000000',' '), e.description,

REPLACE((SELECT GROUP_CONCAT(tabUser.full_name)
FROM tabUser
WHERE locate(tabUser.name, e._assign) > 0), ",", "<br>"),

REPLACE((SELECT GROUP_CONCAT(ib.booking_resource, " : ", ib.status)
FROM `tabResource Booking` as ib
WHERE ib.event = e.name),',','<br>')

FROM tabEvent as e
WHERE e.starts_on >= %(starts_on)s AND e.ends_on <= %(ends_on)s

Après la fusion de 'start time' et 'end time' dans une nouvelle colonne 'Timetable'.

  • Connectez 'booking_resource' et 'status' en ':'
  • Remplacez les Emails en leur 'prénom''Nom'.

Utilisez les fonctions suivantes pour les réaliser :

Sous requête : Quand on sélectionne un élément qui n'existe pas dans le tableau principal. On peut utiliser sous-requête (SELECT *** FROM **)

REPLACE( , , ) Il peut réaliser le remplacement des informations dans le resultat Le lien REPLACE

CONCAT( , ) Il peut réaliser la connexion de 2 éléments que vous avez choisi, qui peut être une colonne ou un symbole. Le lien CONCAT

GROUP_CONCAT( , ) Il permet de regrouper les valeurs non nulles d'un groupe en une chaîne de caractère. Cela est utile pour regrouper des résultats en une seule ligne que d'avoir autant de ligne qu'il y a de résultat dans ce groupe. Quand notre resultat qui est affiché dans plusieurs lignes, cette fonction nous permet d'afficher en une ligne et séparé par une virgule Le lien GROUP_CONCAT

Locate() Il permet de chercher la position d'une chaîne de caractère dans un enregistrement. La fonction retourne zéro si la chaîne de caractère n'a pas été trouvée. Le lien Locate

<br> signifie le saut de la ligne dans le résultat.