diff options
| author | Ludovic Pouzenc <ludovic@pouzenc.fr> | 2017-12-17 18:54:22 +0100 |
|---|---|---|
| committer | Ludovic Pouzenc <ludovic@pouzenc.fr> | 2017-12-17 18:54:22 +0100 |
| commit | f4e2e322c979d8131736b52c507efee8d35e4bda (patch) | |
| tree | 30b4b01a157f4e364e11aaa52cef06f8b90a1bcb /schema | |
| parent | f2567b22b5d81e1d8bc14164ddd7d6098afd0e9c (diff) | |
| download | chd_gestion-f4e2e322c979d8131736b52c507efee8d35e4bda.zip chd_gestion-f4e2e322c979d8131736b52c507efee8d35e4bda.tar.gz chd_gestion-f4e2e322c979d8131736b52c507efee8d35e4bda.tar.bz2 | |
Ajout début de script pour rapporcher les prélèvements effectués et les
services de l'appli de gestion.
Diffstat (limited to 'schema')
| -rw-r--r-- | schema/v_rapprochement_compta.sql | 24 |
1 files changed, 24 insertions, 0 deletions
diff --git a/schema/v_rapprochement_compta.sql b/schema/v_rapprochement_compta.sql new file mode 100644 index 0000000..ab9358d --- /dev/null +++ b/schema/v_rapprochement_compta.sql @@ -0,0 +1,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 + + |