-- Entretiens qui n'ont pas été fait depuis pus d'un an
SELECT id_client, email1, prenom, nom, tel1, newsletter,  bons.id_bon, bons.date_bon, (CURDATE()- INTERVAL 12 MONTH) AS date_rappel
FROM bons
INNER JOIN clients ON clients.id_client = bons.id_client_fk
WHERE bons.date_bon <= (CURDATE()- INTERVAL 12 MONTH)
ORDER BY bons.date_bon ASC
LIMIT 100

-- Entretiens qui n'ont pas été fait depuis pus d'un an
SELECT id_client, email1, prenom, nom, tel1, newsletter, ramonages.id_ramonage, ramonages.date, (CURDATE()- INTERVAL 12 MONTH) AS date_rappel
FROM ramonages
INNER JOIN clients ON clients.id_client = ramonages.id_client_fk
WHERE ramonages.date <= (CURDATE()- INTERVAL 12 MONTH)
ORDER BY ramonages.date DESC
LIMIT 100

-- Clients qui ont une note entre x et x
SELECT clients.id_client, clients.email1, clients.prenom, clients.nom, clients.tel1, clients.newsletter, (q1+q2+q3+q4+q5)/2.5 AS moyenne
FROM questionnaire
INNER JOIN bons ON bons.id_bon = questionnaire.id_ramonage_fk
INNER JOIN clients ON clients.id_client = bons.id_client_fk
WHERE (q1+q2+q3+q4+q5)/2.5 BETWEEN 5 AND 10
ORDER BY moyenne
LIMIT 100


-- Clients qui ont complété leur questionnaire
SELECT clients.id_client, clients.email1, clients.prenom, clients.nom, clients.tel1, clients.newsletter, bons.id_bon, questionnaire.id_questionnaire, questionnaire.q_date, questionnaire.commentaire
FROM questionnaire
INNER JOIN bons ON bons.id_bon = questionnaire.id_ramonage_fk
INNER JOIN clients ON clients.id_client = bons.id_client_fk
GROUP BY bons.id_bon
ORDER BY bons.id_bon DESC
LIMIT 100

-- Clients qui n'ont pas complété leur questionnaire
SELECT clients.id_client, clients.email1, clients.prenom, clients.nom, clients.tel1, clients.newsletter, bons.id_bon, questionnaire.id_questionnaire
FROM clients
INNER JOIN bons ON bons.id_client_fk = clients.id_client
LEFT JOIN questionnaire ON questionnaire.id_ramonage_fk = bons.id_bon
WHERE questionnaire.id_questionnaire IS NULL
ORDER BY bons.id_bon DESC
LIMIT 100


-- Clients qui nous recommanderaient
SELECT clients.id_client, clients.email1, clients.prenom, clients.nom, clients.tel1, clients.newsletter, bons.id_bon, questionnaire.id_questionnaire,  questionnaire.q6,  questionnaire.commentaire
FROM questionnaire
INNER JOIN bons ON bons.id_bon = questionnaire.id_ramonage_fk
INNER JOIN clients ON clients.id_client = bons.id_client_fk
WHERE questionnaire.q6=1
GROUP BY bons.id_bon
ORDER BY bons.id_bon DESC
LIMIT 100


-- Clients qui n'ont complété la marque de leur poêle 
SELECT clients.id_client, clients.email1, clients.prenom, clients.nom, clients.tel1, clients.newsletter, clients_appareils.id_appareil, clients_appareils.marque
FROM clients_appareils
INNER JOIN clients ON clients.id_client = clients_appareils.id_client_fk
WHERE (clients_appareils.marque IS NULL OR  clients_appareils.marque='')
LIMIT 100

-- Clients qui n'ont pas complété le modéle de leur appareil
SELECT clients.id_client, clients.email1, clients.prenom, clients.nom, clients.tel1, clients.newsletter, clients_appareils.id_appareil, clients_appareils.modele
FROM clients_appareils
INNER JOIN clients ON clients.id_client = clients_appareils.id_client_fk
WHERE (clients_appareils.modele IS NULL OR  clients_appareils.modele='')
ORDER BY clients.nom ASC
LIMIT 100


-- Bons conformes ou non
SELECT clients.id_client, clients.email1, clients.prenom, clients.nom, clients.tel1, clients.newsletter, bons.id_bon, bons.date_bon, bons.conforme
FROM bons
INNER JOIN clients ON clients.id_client = bons.id_client_fk
WHERE conforme = 1
ORDER BY bons.date_bon DESC
LIMIT 100

-- Clients qui n'ont pas de mot de passe
SELECT clients.id_client, clients.email1, clients.prenom, clients.nom, clients.tel1, clients.newsletter, clients.mdp
FROM clients
WHERE (clients.mdp IS NULL OR clients.mdp='') AND nom<>'rgpd'
ORDER BY clients.id_client DESC
LIMIT 100

-- Notes obtenues
SELECT clients.prenom, clients.nom, clients.email1, clients.tel1, questionnaire.*
FROM questionnaire
INNER JOIN bons ON bons.id_bon = questionnaire.id_ramonage_fk
INNER JOIN clients ON clients.id_client = bons.id_client_fk
GROUP BY bons.id_bon
ORDER BY questionnaire.id_questionnaire DESC
LIMIT 100

-- Interventions entre 2 dates
SELECT clients.prenom, clients.nom, clients.email1, clients.tel1, interventions.id_type_fk, interventions.rdv, interventions.heure_rdv, interventions.observations_client, interventions.pieces_changer, interventions.traite, interventions.planifie, interventions.datec, interventions.id_appareil_fk, valide_client, interventions.commentaire_client
FROM interventions
INNER JOIN clients ON clients.id_client = interventions.id_client_fk
WHERE interventions.datec BETWEEN '2024-01-01' AND '2024-12-12'
ORDER BY interventions.rdv DESC
LIMIT 100
