From f4e2e322c979d8131736b52c507efee8d35e4bda Mon Sep 17 00:00:00 2001
From: Ludovic Pouzenc <ludovic@pouzenc.fr>
Date: Sun, 17 Dec 2017 18:54:22 +0100
Subject: =?UTF-8?q?Ajout=20d=C3=A9but=20de=20script=20pour=20rapporcher=20?=
 =?UTF-8?q?les=20pr=C3=A9l=C3=A8vements=20effectu=C3=A9s=20et=20les=20serv?=
 =?UTF-8?q?ices=20de=20l'appli=20de=20gestion.?=
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 schema/v_rapprochement_compta.sql | 24 ++++++++++++++++++++++++
 1 file changed, 24 insertions(+)
 create mode 100644 schema/v_rapprochement_compta.sql

(limited to 'schema/v_rapprochement_compta.sql')

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
+
+
-- 
cgit v1.1

