0
Dans notre base de données de test (bdtest), nous créons la table opérations en exécutant l'instruction SQL:

CREATE TABLE opérations
(reference CHAR(9) PRIMARY KEY,
date_exec          DATE,
montant            NUMERIC(13,2),
contrepartie       CHAR(5))
;

S'agissant d'une base de données PostgreSQL, il nous suffit par exemple de coller cette instruction dans un terminal psql connecté à bdtest. Si l'instruction est contenue dans un fichier, nous pouvons également procéder avec la méta-commande \i:

bdtest=> \i fichier

Il nous reste à peupler cette table. Nous avons à cette fin constitué un fichier nommé opérations contenant quelques données:

2012-0458 2012-12-30 487.33  RN2
2013-0001 2013-01-03 1874.56 RN1
2013-0002 2013-01-03 -42.57  ceci
2013-0003 2013-01-14 -119.23 cela
2013-0006 2013-01-31 -956.00 Loyer
2013-0007 2013-01-31 1874.56 RN1
2013-0008 2013-02-01 -127.23 GS
2013-0009 2013-02-09 -47.00  Autre
2013-0010 2013-02-11 -845.99 TV
2013-0011 2013-02-14 -25.47  Div
2013-0012 2013-02-15 -49.00  Ceci
2013-0013 2013-02-20 -314.00 VOIT
2013-0014 2013-02-27 -103.49 Tél
2013-0015 2013-02-28 -956.00 Loyer
2013-0016 2013-02-28 1874.56 RN2
2013-0017 2013-03-01 -117.23 GS
2013-0018 2013-03-06 -43.00  Autre
2013-0019 2013-03-06 -76.84  Div
2013-0004 2013-01-14 -314.00 VOIT
2013-0005 2013-01-31 -114.23 Tél

Le caractère de séparation entre les colonnes est le caractère de tabulation et nous utilisons la méta-commande \copy:

bdtest=> \copy opérations from opérations

Si au lieu de travailler dans une base de données PostgreSQL nous préférons utiliser la base de données intégrées à Libreoffice base, nous pouvons tant pour la création de la table que pour son remplissage procéder comme montré dans ce message.

Nous allons créer une vue qui placera les montants positifs (crédits) dans une colonne et les montants négatifs (débits) dans une autre colonne.
Nous aurons aussi besoin de l'année et du mois de chaque opération. Ceux-ci peuvent être extraits de la date d'exécution comme ceci:
extract(year from date_exec) pour l'année
extract(mont from date_exec) pour le mois.

Mais comme montré ici:


le résultat n'est peut-être pas très heureux en ce qui concerne les numéros de mois: 01 au lieu de 1 serait plus esthétique.
Aussi, il est préférable d'utiliser la fonction to_char:
to_char(date_exec, 'YYYY') pour l'année
to_char(date_exec, 'MM') pour le numéro du mois
et nous pouvons même obtenir le nom du mois avec
to_char(date_exec, 'TMMONTH'):


Cela étant, créons la vue opérationsv à l'aide de l'instruction SQL suivante, en procédant comme pour la création de la table.

CREATE VIEW opérationsv
AS
SELECT
     to_char(date_exec, 'YYYY') AS an,
     to_char(date_exec, 'MM') AS mois_n,
     to_char(date_exec, 'TMMONTH') AS mois,
     reference,
     date_exec,
     CASE
       WHEN montant > 0 THEN montant
       ELSE 0.00
     END AS crédit,
     CASE
       WHEN montant < 0 THEN -montant
       ELSE 0.00
     END AS débit,
     montant AS solde,
     contrepartie
FROM opérations

Si nous travaillons dans la base de données intégrée à libreoffice, l'expression to_char(date_exec, 'TMMONTH') n'est pas reconnue. Il convient alors de la remplacer par:

CASE
  WHEN extract(month from date_exec) = 1 THEN 'Janvier'
  WHEN extract(month from date_exec) = 2 THEN 'Février'
  WHEN extract(month from date_exec) = 3 THEN 'Mars'
  WHEN extract(month from date_exec) = 4 THEN 'Avril'
  .....
END AS mois,

Et nous voilà paré pour sortir des bilans, par exemple avec une simple instruction SQL comme celle-ci:

select an, mois, count(*) as opérations,
sum(crédit) as crédit,
sum(débit) as débit,
sum(solde) as solde
from opérationsv
group by an, mois_n, mois
order by an, mois_n
;

Voici le résultat dans un terminal psql:


Il est possible d'améliorer l'output obtenu.
Plaçons dans un fichier bilan-mensuel.sql les commandes

\set QUIET
\pset numericlocale on
\pset footer off
\pset linestyle u
\pset title 'Bilan 2013'
\pset border 2
\o | awk -f bilan-mensuel.awk | tee rapport.txt
select mois_n, mois, count(*) as opérations,
sum(crédit) as crédit,
sum(débit) as débit,
sum(solde) as solde
from opérationsv
where an = '2013'
group by mois_n, mois
UNION
select 'TOTAL', '', count(*) as opérations,
sum(crédit) as crédit,
sum(débit) as débit,
sum(solde) as solde
from opérationsv
where an = '2013'
order by mois_n
;
\o
\pset footer
\pset title
\pset border 1
\unset QUIET


Et voici le résultat:


L'output a été redirigé vers la commande awk pour exécution du script bilan-mensuel.awk

# Impression du titre
NR == 1 {
print "\n" $0 "\n"
}
# Sauvegarde ligne de séparation dans y
NR == 4 {
y=$0
}
# Imression d'une ligne de séparation si
# la ligne en input contient TOTAL en position 3
/^..TOTAL/ {
print y
}
# Impression des lignes en input
# sauf le titre déjà imprimé
NR > 1

dont le rôle essentiel est d'imprimer une ligne de séparation avant la ligne 'TOTAL' et accessoirement d'imprimer une ligne vide avant et après le titre.
De plus, grâce à la commande 'tee', le fichier rapport.txt contient une copie de ce qui a été envoyé à l'écran. Ce fichier rapport.txt peut tout à fait être intégré dans un traitement de texte.
Nous avons déjà traité de la technique utilisée ici pour améliorer la présentation au niveau d'un terminal psql dans ce billet et le suivant.
Pour confectionner un rapport mieux présenté et où figurent en plus les lignes de détails, il convient de travailler dans libreoffice base avec ORB 'Oracle report builder'. Pour disposer de cette extension il suffit le plus souvent d'installer un paquet dont le nom est (ou ressemble à) 'libreoffice-report-builder'.
Si nos données figurent dans bdtest, il est évidemment nécessaire de connecter libreoffice à cette base de données, opération pour laquelle il est grandement conseillé d'installer le paquet 'libreoffice-sdbc-postgresql' (ou un paquet dont le nom ressemble à celui-là).
Dans le rapport que nous envisageons de confectionner, il est relativement inutile de placer dans les lignes 'détails' le contenu du champ 'solde' tel qu'il est défini dans la vue opérationsv, puisque que celui-ci est toujours égal à un des deux montants crédit ou débit. Par contre y mettre le solde résultant de toutes les opérations réalisées depuis le début du mois est nettement plus intéressant.
A cette fin, nous allons dans libreoffice base créer en mode SQL la requête opérationsr:

SELECT "A".*,
( SELECT SUM( "solde" )
  FROM "opérationsv" "B"
  WHERE "B"."reference" <= "A"."reference"
  AND "B"."an" = "A"."an"
  AND "B"."mois_n" = "A"."mois_n" 
) AS "solde_c"
FROM "opérationsv" "A"

Après avoir choisi 'Créer un rapport en mode ébauche', nous sélectionnons cette requête en tant que de source de données que nous filtrons sur une année:



Pour le reste,  nous allons procéder comme expliqué dans ce billet, le rapport réalisé alors nous servant de modèle.
Nous n'allons pas ici entrer dans les détails, mais fixer simplement quelques points de repères.

Dans la  fenêtre 'Tri et groupements' nous plaçons mois-n et reference:

Les données sont groupées suivant mois_n, mais cela ne nous empêche pas de faire figurer le nom du mois en clair dans l'en-tête de groupe:


Les valeurs dans la colonne 'solde' sont tirées de solde_c sauf pour la fonction 'Accumulation' qui porte sur  'solde'.

Et voici le résultat obtenu:


Dans le cas d'un rapport plus conséquent, il peut être utile de forcer une nouvelle page pour chaque changement de mois:



Nous pouvons également envisager d'introduire un groupement supplémentaire sur la date:


Établir un rapport tel que celui-ci


tenant compte de ce nouveau groupe ne pose aucun problème particulier.

La requête 'source de données' conduisant à ce résultat est cette fois:


SELECT "A".*,
( SELECT SUM( "solde" )
FROM "opérationsv" "B"
WHERE "B"."reference" <= "A"."reference"
AND   "B"."date_exec" = "A"."date_exec" ) AS "solde_c"
FROM "opérationsv" "A"



Comme auparavant les valeurs de la colonne 'solde' sont tirées de 'solde_c' sauf pour les fonctions 'Accumulation' (Total mois et Total) où intervient 'solde':


x

Post a Comment

 
Top