Activité Comprendre le SGBD (Système de Gestion de Base de

Activité Comprendre le SGBD (Système de Gestion de Base de
données) du PGI
1. Architecture client serveur
1.1
Extrait schéma architecture SI
Serveur de fichiers
Profs élèves
Serveur d’impression
Win 2008
Reseau serveurs
Cr-cegid-srv
Base sql server 2008 R2
Wind 2008
1.2
ère
1
Serveur VMWare
Virtualisation
Postes Clients légers
phase : connexion à la base de données avec authentification de l’utilisateur et vérification de ses droits d’accès
1.3
Déroulement d’une transaction de consultation d’une facture via le module de Gestion commerciale
La consultation de la facture est faite via le formulaire du module de gestion commerciale : Ventes – Pièces – saisie du tiers et du
numéro de facture dans les contrôles de formulaire prévu à cet effet. La requête SQL est envoyée au SGBD de façon
transparente à l’utilisateur. Le résultat de la consultation est mis en page sous forme de formulaire d’affichage de la facture.
1 C. Guédat
1.4
Déroulement de l’exécution d’une requête SQL saisie par l’utilisateur via le moniteur SQL
Le moniteur SQL peut être lancé depuis n’importe quelle interface : gestion commerciale, Comptabilité, administration des
sociétés,… et à partir de n’importe quel menu.
L’icone de lancement du moniteur est en haut à droite de l’écran :
L’utilisateur saisit sa requête dans la zone « éditeur » du moniteur SQL et le résultat de son exécution est affiché en tableau sans
mise en forme particulière : jeu d’enregistrements résultant de la requête.
2 C. Guédat
2.
Schéma de la base de données – Consulter les tables du PGI
Pas de schéma physique visuel et pour cause : Plus de 1900 tables. Pour voir la liste des tables plusieurs possibilités :
2.1
Outil logiciel « Administrateur de base de données » (Celui qui a permis de créer la base)
Pour trouver la liste des tables du schéma : Outils – Tables – Onglet tables
Pour trouver les champs d’une table (i.e. les propriétés d’une relation dans le modèle relationnel) ici la table ECRITURE.
2.2
Liste des tables via la consultation de la tables DETABLES au choix dans le module Gestion commerciale ou
Comptabilité
ème
Bouton Moniteur SQL en haut à droite de l’écran
. Créer et exécuter la requête. En 7
ème
10 colonne son nom abrégé sur 3 lettres, puis les index.
colonne le nom de la table et en
Afficher par exemple uniquement le préfixe de la table « CHOIXCOD » :
3 C. Guédat
SELECT DT_PREFIX FROM DETABLES
WHERE DT_NOMTABLE="CHOIXCOD" ;
On trouve « CC »
2.3
Liste des champs via la consultation de la tables DECHAMPS au choix dans le module Gestion commerciale ou
Comptabilité
Afficher Les noms des champs et les types des champs de la table CHOIXCOD
SELECT * FROM DECHAMPS
WHERE DH_PREFIXE="CC" ;
Cette table contient des valeurs des familles d’articles, de taux TVA,…
Pour en connaître maintenant les valeurs :
SELECT * FROM CHOIXCOD
WHERE CC_TYPE="FN1" OR CC_TYPE="TRC" ;
3.
Préalable à SQL: connaître les spécificités de la syntaxe SQL Server
3.1
Afficher les données de la table ECRITURE et les exporter dans Excel
Étudier la signification des champs de cette table et leur valeur notamment :
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
E_JOURNAL
E_DATECOMPTABLE
E_NUMEROPIECE
E_NUMLIGNE
E_GENERAL
E_AUXILIAIRE
E_DEBIT
E_CREDIT
E_REFINTERNE
E_LIBELLE
E_DATECREATION
E_UTILISATEUR
E_DATEMODIF
E_CONTREPARTIEGEN
E_CONTREPARTIEAUX
E_MODEPAIE
4 C. Guédat
• E_DATEECHEANCE
Pour cela copier/coller la requête suivante dans le moniteur SQL :
SELECT E_JOURNAL, E_DATECOMPTABLE, E_NUMEROPIECE, E_NUMLIGNE, E_GENERAL, E_AUXILIAIRE, E_DEBIT, E_CREDIT,
E_REFINTERNE, E_LIBELLE , E_DATECREATION, E_UTILISATEUR, E_DATEMODIF, E_CONTREPARTIEGEN, E_CONTREPARTIEAUX ,
E_MODEPAIE, E_DATEECHEANCE
FROM ECRITURE
WHERE E_JOURNAL= "VTE";
Quels champs de cette table forment la clé primaire de la table ECRITURE ? (cette clé doit être unique)
E_JOURNAL + E_DATECOMPTABLE + E_NUMEROPIECE + E_NUMLIGNE
3.2
Connaître le format des champs pour écrire les requêtes SQL avec la bonne syntaxe
3.2.1 Champs texte VARCHAR
Avec le moniteur SQL de l’administrateur de base de données SQL standard :
SELECT * FROM LIGNE
WHERE GL_NATUREPIECEG='FAC'
SELECT * FROM LIGNE
WHERE GL_LIBELLE LIKE 'Cacao%'
Avec le moniteur SQL de gestion commerciale ou comptabilité :
SELECT * FROM LIGNE
WHERE GL_NATUREPIECEG="FAC"
SELECT * FROM LIGNE
WHERE GL_LIBELLE LIKE "Cacao%"
3.2.2 Champs de type réel NUMERIC
SELECT * FROM LIGNE
WHERE GL_TOTALHT=28.48
3.2.3 Champs DATETIME
Avec le moniteur SQL de l’administrateur de base de données SQL standard :
Les champs date sont codés en DATETIME SQL Server. Selon le paramétrage du serveur :
•
•
français : JJ-MM-AAAA hh:mm:ss[.nnn]
anglo-saxon : AAAA-MM-JJ hh:mm:ss[.nnn]
L’heure fait donc partie intégrante de la date. Ainsi la restriction sur une date : WHERE GL_DATEMODIF BETWEEN '15-06-2015
00:00:00.0' AND '16-06-2015 00:00:00.0'
Avec le moniteur SQL de gestion commerciale ou comptabilité :
Solution de contournement : YEAR(E_DATECOMPTABLE)=2015 AND MONTH(E_DATECOMPTABLE)=9 AND
DAY(E_DATECOMPTABLE)=15
Exemple avec MONTH : SELECT * FROM LIGNE WHERE MONTH(GL_DATELIVRAISON)=9
Pour accéder à la date système : Now()
5 C. Guédat
4.
Dictionnaire des données extraites à partir de requêtes SQL telles que vues dans le paragraphe 2
(à titre indicatif et sous réserve)
Propriété
Nom du champ
Commentaire
Table
Code tiers
T_AUXILIAIRE
Compte auxiliaire client
TIERS
Nature tiers
T_NATUREAUXI
CLI : Client ou FOU: fournisseur
TIERS
pays
T_PAYS
TIERS
Raison sociale
T_LIBELLE
TIERS
Adresse
T_ADRESSE1
TIERS
CodePostal
T_CODEPOSTAL
TIERS
Ville
T_VILLE
TIERS
Abrégé
T_ABREGE
Téléphone
T_TELEPHONE
TIERS
Fax
T_FACTURE
TIERS
Email
T_EMAIL
TIERS
Devise
T_REGIMETVA
TIERS
Date création
T_DATECREATION
TIERS
Date dernier mouvement
T_DATEDERNMVT
Date du dernier mouvement effectué par le tiers
TIERS
Compte collectif
T_COLLECTIF
Compte client collectif
TIERS
Représentant
T_REPRESENTANT
Commercial qui suit le client
TIERS
Code famille tarif
T_TARIFTIERS
Libellé famille tarif
Code mode règlement
Raison sociale abrégée
TIERS
TIERS
Voir CHOIXCOD
T_MODEREGLE
Libellé mode règlement
mode de règlement habituel du client
TIERS
Voir MODEREGL
Nature pièce (facture)
GP_NATUREPIECEG FAC : facture, AVS : avoir, BLC, CC,...
PIECE
Date pièce (facture)
GP_DATEPIECE
PIECE
Souche pièce (facture)
GP_SOUCHE
Numéro facture
GP_NUMERO
AVO, GLC, GCC, GAC
L'identifiant d'une facture est constitué d'un groupe de champ :
nature de la pièce + date de la pièce + code souche + numéro
chronologique de pièce
PIECE
PIECE
Référence interne
GP_REFINTERNE
Référence interne utilisée pour la pièce exemple : V160901 pour
la facture du 16/09 du client Bouchez
PIECE
Port-frais
GP_TRANSPORT
PIECE
% Remise
GP_REMISEPIED
PIECE
% escompte
GP_ESCOMPTE
PIECE
Le total TTC comme bien d'autre champ est enregistré dans la
base et non recalculé.
Total TTC Facture
GP_TOTALTTC
Acompte
GP_ACOMPTE
Référence article
GA_ARTICLE
Désignation
GA_LIBELLE
ARTICLE
Base tarif HT
GA_PVHT
ARTICLE
Code famille
GA_FAMILLENIV1
Code famille d'articles
ARTICLE
Code famille de taxe
GA_FAMILLETAXE1
TN, TR,…
ARTICLE
Quantité physique (en stock)
GQD_PHYSIQUE
Num ordre ligne
Quantité (article facturé)
GL_NUMLIGNE
GL_QTEFACT
PIECE
PIECE
Identifiant d'un article
ARTICLE
DISPODETAIL
Numéro chronologique de ligne dans la facture. Comme toutes
les factures possèdent une ligne 1, 2 3… Ce numéro est relatif
par rapport à l'identifiant d'une pièce. Donc en réalité
l'identifiant d'une ligne facture est constitué de : nature de la
pièce + date de la pièce + code souche + numéro chronologique
de pièce + numéro chronologique de ligne!
Quantité d'article facturé apparaissant sur un une ligne de la
facture
LIGNE
LIGNE
6 C. Guédat
Souche pièce
AVO : avoir, GLC : Bon Livraison client, GCC commande client,
GAC
SH_SOUCHE
SOUCHE
Libellé souche
SH_LIBELLE
Utilisateur
US_UTILISATEUR
Libellé utilisateur
US_LIBELLE
Code TVA
TV_CODETAUX
Code TVA est en fait constitué du code taux : TN, TR, TSR +
TXCPTTVA
Régime TVA
TV_REGIME
Régime TVA : FRA pour France
TXCPTTVA
Taux TVA vente
TV_TAUXVTE
Taux
TXCPTTVA
Code mode règlement
MR_MODEREGLE
Code mode règlement : C01, LC1
MODEREGLE
Libellé mode règlement
MR_LIBELLE
Libellé mode règlement : chèque comptant,…
MODEREGLE
Type code famille
CC_TYPE
Les codes familles niveau 1 article ont un CC_TYPE="FN1";
Les codes familles tarif client ont un CC_TYPE="TRC"
CHOIXCOD
Code famille
CC_CODE
CHOIXCOD
Libellé de la famille
CC_LIBELLE
Pour les familles articles, CC_CODE=001 ,002,003….
Pour les familles tarif client CC_CODE=CON,DIV,PAT,REV
Pour les familles articles, CC_LIBELLE=cacao si CC_CODE=001 ….
Pour les familles tarif client CC_LIBELLE=confiseurs si
CC_CODE=CON,…
SOUCHE
Utilisateur créateur du client
UTILISAT
UTILISAT
CHOIXCOD
5.
Dictionnaire des données relatives aux tables ECRITURE et GENERAUX extraites à partir de
requêtes SQL telles que vues dans le paragraphe 2
Propriété
Nom du champ
Commentaire
Table
G_GENERAL
Compte général
GENERAUXL
G_LIBELLE
Libellé
GENERAUXL
G_ABREGE
Libellé abrégé
GENERAUXL
G_NATUREGENE
Nature du compte
GENERAUXL
G_TOTALDEBIT
Total débit
GENERAUXL
G_TOTALCREDIT
Total crédit
GENERAUXL
Numéro
d'exercice
E_EXERCICE
Exercice
ECRITURE
Code journal
E_JOURNAL
"ACH", "VTE","OD",…
ECRITURE
E_DATECOMPTABLE
Date comptable
ECRITURE
E_NUMEROPIECE
N° de pièce
ECRITURE
E_NUMLIGNE
N° de ligne
ECRITURE
E_GENERAL
Compte général
ECRITURE
E_AUXILIAIRE
Compte auxiliaire
ECRITURE
E_DEBIT
Débit
ECRITURE
E_CREDIT
Crédit
ECRITURE
E_REFINTERNE
Référence interne
ECRITURE
E_LIBELLE
Libellé
ECRITURE
E_DATECREATION
Date création
ECRITURE
E_UTILISATEUR
Dern. utilisateur
ECRITURE
E_DATEMODIF
Date dern. modif.
ECRITURE
E_CONTREPARTIEGEN
Compte général de contrepartie
ECRITURE
E_MODEPAIE
Mode de paiement
ECRITURE
E_CONTREPARTIEAUX
Compte auxiliaire de contrepartie
ECRITURE
E_DATEECHEANCE
Date échéance
ECRITURE
7 C. Guédat