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
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.
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:
Et nous voilà paré pour sortir des bilans, par exemple avec une simple instruction SQL comme celle-ci:
Il est possible d'améliorer l'output obtenu.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
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,
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:
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:
Et voici le résultat obtenu:
(EDIT: voir variante ici)
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
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
