1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SELECT s.id as "svc_id",
CASE YEAR(s.created) WHEN 0 THEN 'import-TTN' ELSE YEAR(s.created) END as "svc_annee_premiere_saisie",
IF( ( '2015-01-01' < s.date_fin OR ISNULL(s.date_fin) ) AND '2015-12-31' > s.date_debut, 'oui', 'non' ) as "actif2015",
IF( ( '2016-01-01' < s.date_fin OR ISNULL(s.date_fin) ) AND '2016-12-31' > s.date_debut, 'oui', 'non' ) as "actif2016",
IF( ( '2017-01-01' < s.date_fin OR ISNULL(s.date_fin) ) AND '2017-12-31' > s.date_debut, 'oui', 'non' ) as "actif2017",
IF( (UNIX_TIMESTAMP(s.modified) < UNIX_TIMESTAMP('2015-01-01 00:00:00')), 'jamais', s.modified ) as "svc_derniere_modif",
CONCAT('CHD',a.id) as "adt", at.title as "adt_type",
a.raison, a.nom, a.prenom,
st.title as "service_type", st.prix_base_ht, s.prix_ht,
p.title as "periodicite", s.date_debut, s.date_fin, sst.title as "statut", a.proprio, s.description, a.notes
FROM services s
LEFT JOIN adherents a ON ( a.id = s.adherent_id )
LEFT JOIN adherent_types at ON ( at.id = a.adherent_type_id )
LEFT JOIN service_types st ON (st.id = s.service_type_id)
LEFT JOIN service_statuts sst ON (sst.id = s.service_statut_id)
LEFT JOIN periodicites p ON (p.id = st.periodicite_id)
WHERE s.service_statut_id <> 1
ORDER BY a.id, s.id
|
