1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| select b.branchcode,case
when a.accounttype='Retar' then 'Pénalité de retard'
when a.accounttype='Abt' then 'Abonnement'
when a.accounttype='Dette' then 'Dette maximale'
when a.accounttype='Perdu' then 'Document perdu'
when a.accounttype='Lreco' then 'Lettre recommandée'
when a.accounttype='Badge' then 'Perte badge'
when a.accounttype='w' then 'Perte et profit'
when a.accounttype='PEB' then 'Prêt Entre Bib (PEB)'
else 'null'
end as type
,a.timestamp as 'date et heure',b2.surname,b2.firstname,b2.cardnumber
,a.note, a.description, substring_index(convert(sum(a.amount),DECIMAL(8,2)),'-',-1) as montant from borrowers b, borrowers b2, accountlines a where b.borrowernumber=a.manager_id and a.amount like '-%' and a.note not like '%SANSOBJET%'
and
a.accounttype in ('Retar','Abt','Dette','Perdu' ,'Lreco','Badge','PEB')
and a.date >=<<date sup. égale à |date>> and a.date <=<<date inf. égale à|date>>
and
b2.borrowernumber=a.borrowernumber
group by a.timestamp,type,b2.surname,b2.firstname,b2.cardnumber,a.note,a.description |
