Bonjour,

Ce sujet reprend la suite de celui-ci: [Résolu] Sommes conditionnelles et group by par jouvrard - OpenClassrooms
J'ai donc ma somme de prévisionnel par mois, super.

J'ai en plus de mes 2 tables "contrats" et "contrats_previ", la table "contrats_facturation":
- ID(pk), contrats_id(fk), facturation, date

Je souhaites avoir les sommes encore "engagées" pour tous les contrats courant sur le mois demandé, qui consiste à avoir la somme des prévisionnels des contrats en cours, moins la somme des facturations déjà passées.

Pour tester j'ai fait cette requête:

SELECT
    c.id,
    SUM(COALESCE(c.coutMoyenCorrige, cp.cout) * cp.heures) AS cout,
    SUM(cf.facturation) AS facturation
FROM
	contrats c
INNER JOIN
	contrats_previ cp on cp.contrats_id = c.id
LEFT JOIN
	contrats_facturation cf on cf.contrats_id = c.id and cf.date <= '2021-01-31'
WHERE
	c.is_devis = 0
    AND c.is_cancelled = 0
    AND c.debut <= '2021-01-31'
    AND c.fin >= '2021-01-01'
GROUP BY
	c.id

Problème, si j'ai plusieurs facturations qui ont été faites, alors la somme de celles-ci se retrouve multipliée par le nombre en question.
Pour cette requête précisément j'ai 2 facturations sur un contrats et je me retrouve avec une somme de 5060€ au lieu de 2530€

Bref, comme d'habitude je demande de l'aide là dessus...
J'ai commencé la lecture du document de conception de BDD de Benzouye mais je suis encore en apprentissage...

Merci !

Bonjour,

La lecture du document proposé ne t'aidera pas dans ce cas.

La comportement est normal, puisqu'un même contrat peut avoir plusieurs prévisionnels ET plusieurs facturations, cela crée un produit cartésien entre les deux tables (chaque prévisionnel est associé à chaque facturation).

Il y a plusieurs façons de voir les choses, mais la solution que je préconise réside dans l'utilisation d'une sous-requête pour faire la somme de facturation :

SELECT
	c.id,
	SUM( COALESCE( c.coutMoyenCorrige, cp.cout ) * cp.heures ) AS cout,
	cf.facturation
FROM
	contrats c
		INNER JOIN contrats_previ cp
			ON c.id = ct.contrats_id
		LEFT JOIN (
				-- Somme des facturations par contrat
				-- Avant le 31 janvier 2021
				SELECT contrats_id, SUM( facturation ) AS facturation
				FROM contrats_facturation
				WHERE date <= '2021-01-31'
				GROUP BY contrats_id
			) cf
			ON cf.contrats_id = c.id
WHERE
	c.is_devis = 0
	AND c.is_cancelled = 0
	AND c.debut <= '2021-01-31'
	AND c.fin >= '2021-01-01'
GROUP BY
	c.id,
	cf.facturation

Attention à faire varier la condition de date dans la sous-requête.

Ok merci beaucoup je ne plaçais pas la sous requête où il fallait mais j'avais l'idée générale c'est déjà ça...

Et heu... si je veux avoir cette requête avec avec des résultats sur un an (1 par mois), il y a une solution ou bien il faut passer par 12 requêtes?

J'ai déjà fait des répartitions pour avec des stats par tranches d'âges avec des CASE WHEN (il en faudrait donc ici 1 par mois) mais je ne sais pas si c'est applicable ici avec des CASE WHEN MONTH(:date), ...

Au passage et si tu peux avoir des doutes, je ne pose pas ces questions en rapport avec du professionnel, il s'agit de trucs persos justement pour m'entrainer.
En gros quand tu réponds à mes nombreuses questions tu ne bosses pas à ma place pendant que je suis payé.

jouvrard a écrit:

tu ne bosses pas à ma place pendant que je suis payé

ça m'est un peu égal ... Si nos échanges t'aident, c'est le principal ...

jouvrard a écrit:

si je veux avoir cette requête avec avec des résultats sur un an (1 par mois), il y a une solution ou bien il faut passer par 12 requêtes ?

Peux-tu poster un exemple de résultat attendu ? Et en utilisant le mois (et l'année) de quelle date ?

-
Edité par Benzouye 4 février 2021 à 9:55:57

L'idée serait d'avoir un résultat du genre, mais du coup sans distinction de contrat, juste la somme totale pour chaque mois.

[0] - 2021(année) - 1(mois) - 2500(cout) - 500(facturation)
[1] - 2021(année) - 2(mois) - 8400(cout) - 250(facturation)
[2] - 2021(année) - 3(mois) - 5710(cout) - 0(facturation)
[3] ......

J'aimerais bien rester avec comme paramètres la date de début et de fin, de cette façon il serait possible de faire pour une année complète, mais aussi juste pour 4 mois par exemple, ou bien 2 ans 1/2.
Après si c'est trop chi****, à préciser juste l'année et sortir le résultat pour les 12 mois de l'année en question.

Ok pour la somme des facturations, il y a une date, mais pour la somme des prévisionnels ? Quelle date utiliser ?

Les prévisionnels sont liés aux contrats et ne disposent pas d'une date propre à eux.

Un contrat a une date de début et de fin, et a des prévisionnels qui donnent les heures dudit contrat, et qui ont un "contrats_id" en FK

CONTRATS_PREVI:
ID(pk)
contrats_id(fk)
matieres_id(fk)
heures
cout (pris en compte pour le calcul si pas de coutMoyenCorrigé dans le contrat comme nous le faisons déjà)

... Et je suis en train de penser que limiter la sélection des contrats sur le mois demandé ne sera pas bon, il faudrait le faire avec la somme des prévis + facturation de tous les contrats pas encore commencés pour chaque mois car je peux avoir un contrat terminé depuis un moment mais pas encore totalement facturé par exemple...
il faudrait donc prendre en compte les contrats qui débutent avant la fin du mois demandé sans se préoccuper de la date de fin et donc virer "AND c.fin >= ...."

-
Edité par jouvrard 4 février 2021 à 11:20:08

jouvrard a écrit:

Les prévisionnels sont liés aux contrats et ne disposent pas d'une date propre à eux

Cela complique les choses ...

jouvrard a écrit:

[2] - 2021(année) - 3(mois) - 5710(cout) - 0(facturation)

Cette ligne est donc impossible à obtenir ... puisque la date est portée par la facturation, si aucune facturation alors aucune date ... Ta demande est caduque ...

Si l'on prend les données d'exemple suivantes :

Que souhaiterais-tu obtenir ?

-
Edité par Benzouye 4 février 2021 à 13:05:05

Au niveau de la table "contrats", je suppose que tu as mis les dates de début et de fin histoire de remplir. Dans les faits, on peut avoir par exemple le contrat 1 qui va du 23/01/2021 au 03/06/2021, etc... Mais aussi un contrat du 15/11/2020 au 17/04/2021, qu'il faut donc prendre en compte aussi à partir du mois de novembre.

Ce que je souhaite obtenir, un tableau de résultats avec:

- Année (qu'on aura de toute façon puisque l'année sera finalement le seul paramètre passée à la requête)
- Mois (de 1 à 12 quoi)
- La somme des couts des contrats courant sur le mois en question (avec prise en comte soit du cout du prévi, soit du coutMoyenCorrigé)
- La somme des facturation ayant été faites sur ces contrats, jusqu'au mois en question

Je n'ai pas besoin de la liste des contrats, juste des sommes des coûts mensuels des prévisionnels associés ainsi que des facturations faites jusqu'à chaque mois.

2020 - 1 - 52000 - 0
2020 - 2 - 52000 - 0
2020 - 3 - 52000 - 100
2020 - 4 - 52000 - 100
2020 - 5 - 52000 - 1100
2020 - 6 - 52000 - 1100
2020 - 7 - 52000 - 1100
2020 - 8 - 52000 - 2600
2020 - 9 - 52000 - 2600
2020 - 10 - 52000 - 2600
2020 - 11 - 52000 - 2600
2020 - 12 - 52000 - 2600

Ici, tous les contrats que tu as mis débutants le 01/01, la somme des prévisionnels est toujours la même.
Si on en avait un qui débutait en février par exemple, alors la somme de ses prévis s'ajouterait au total des autres.
Et si un contrat s'arrêtait avant la fin de l'année, ici pas de différence, on garde son prévi et ce qui est facturé.

Attention ça va piquer

SELECT
	A.annee,
	M.mois,
	SUM( COALESCE( C.coutMoyenCorrige, P.cout ) * P.heures ) AS cout,
	COALESCE( F.facturation, 0 ) AS facturation
FROM
	(
		-- Liste des mois
		SELECT '01' AS mois
		UNION SELECT '02'
		UNION SELECT '03'
		UNION SELECT '04'
		UNION SELECT '05'
		UNION SELECT '06'
		UNION SELECT '07'
		UNION SELECT '08'
		UNION SELECT '09'
		UNION SELECT '10'
		UNION SELECT '11'
		UNION SELECT '12'
	) M
		CROSS JOIN (
			-- Liste des années
			SELECT '2019' AS annee
			UNION SELECT '2020'
			UNION SELECT '2021'
		) A
		INNER JOIN contrats C
			ON DATE_FORMAT( C.debut, "%Y%m" ) <= CONCAT( A.annee, M.mois )
			AND DATE_FORMAT( C.fin, "%Y%m" ) >= CONCAT( A.annee, M.mois )
		INNER JOIN contrats_previ P
			ON C.id = P.contrats_id
		LEFT JOIN (
				-- Facturation par mois
				SELECT
					DATE_FORMAT( date, "%Y%m" ) AS date,
					SUM( facturation ) AS facturation
				FROM contrats_facturation
				GROUP BY DATE_FORMAT( date, "%Y%m" )
			) F
			ON CONCAT( A.annee, M.mois ) = F.date
WHERE
	C.is_devis = 0
	AND C.is_cancelled = 0
GROUP BY
	A.annee,
	M.mois,
	F.facturation

1. On crée la liste des mois possibles et la liste des années possibles.
2. On fait un produit cartésien (CROSS JOIN) pour avoir tous les couples année/mois possibles.
3. Puis on calcule le prévisionnel par année/mois
4. Et enfin on rajoute la facturation par année/mois

EDIT : Correction des alias dans le SELECT ...

-
Edité par Benzouye 5 février 2021 à 12:16:08

A la vache, cette surpuissance!
Je ne connaissais pas CROSS JOIN, encore un truc à apprendre.

Il restait juste les "cp" dans le SELECT que j'ai remplacés par "P" vu le FROM de la requête.
Je pense aussi que "AND DATE_FORMAT( C.fin, "%Y%m" ) >= CONCAT( A.annee, M.mois )" ne me sera pas utile car je veux les sommes/facturations même pour les contrats dont la date de fin est passée (les facturations pouvant arriver après).

Bref, après test tout est OK, c'est vraiment génial et tes explications à chaque fois me font beaucoup apprendre, même s'il y a encore du chemin.

... Et... heu...
Je m'entraine là dessus avec un ami (qui pose aussi pleeeein de questions de son côté, mais pas sur ce forum ;)), qui a ajouté à la table "contrats_facturation" un champ booléen "termine".
Ce champ permet de dire "Si je suis à true, alors on considère la facturation du contrat terminé, quoi qu'il reste encore à facturer normalement.

L'idée qui en sort est d'avoir une requête qui nous permettrait d'avoir "juste" ce qui reste à facturer par mois.
Donc en gros la somme des coûts des "contrats_previ" moins les facturations. Et si par exemple en février 2021 on a une facturation marquée "termine", alors la somme des couts des "contrats_previ" du contrat lié n'est plus comptée à partir de ce moment, elle est à 0, comme si on avait tout facturé quoi.

J'ai commencé à travailler sur une requête se basant sur celle que tu viens de me donner, mais je crains d'avoir "un peu" de mal, d'où encore une fois une demande d'un bon coup de pouce...

Tu préfères que je crée un nouveau sujet pour ça peut-être?
Je l'ai mis à la suite car on reste dans le même thème avec les mêmes données c'est pour ça.

Et normalement après ça je devrais te laisser tranquille un moment, même si les BDD ont clairement l'air d'être une passion chez toi.

La complexité devient assez grande pour parler d'une nouvelle notion, les vues.

https://openclassrooms.com/fr/courses/1959476-administrez-vos-bases-de-donnees-avec-mysql/1973943-vues

On va créer une vue pour le prévisionnel par contrat :

CREATE VIEW vue_previsionnel AS
SELECT
    C.id AS contrats_id,
    SUM( COALESCE( C.coutMoyenCorrige, P.cout ) * P.heures ) AS cout
FROM
    contrats C
        INNER JOIN contrats_previ P
            ON C.id = P.contrats_id
GROUP BY c.id;

On va également créer une vue pour la liste des années/mois :

CREATE VIEW vue_mois AS
SELECT
	A.annee,
	M.mois
FROM
	(
		-- Liste des mois
		SELECT '01' AS mois
		UNION SELECT '02'
		UNION SELECT '03'
		UNION SELECT '04'
		UNION SELECT '05'
		UNION SELECT '06'
		UNION SELECT '07'
		UNION SELECT '08'
		UNION SELECT '09'
		UNION SELECT '10'
		UNION SELECT '11'
		UNION SELECT '12'
	) M
		CROSS JOIN (
			-- Liste des années
			SELECT '2019' AS annee
			UNION SELECT '2020'
			UNION SELECT '2021'
			UNION SELECT '2022'
		) A;

Cette vue sera à faire vivre pour rajouter des années si besoin.

On va enfin créer une vue pour la facturation :

CREATE VIEW vue_facturation AS
SELECT
	YEAR( date ) AS annee,
	MONTH( mois ) AS mois,
	contrats_id,
	SUM( facturation ) AS facturation
FROM contrats_facturation
GROUP BY
	YEAR( date ),
	MONTH( date ),
	contrats_id;

On peut maintenant utiliser ces vues comme des tables "normales" dans nos prochaines requêtes, cela va grandement simplifier l'écriture ...

Je testerai un truc de ce genre :

SELECT
	VM.annee,
	VM.mois,
	VP.cout,
	VF.facturation
	SUM( FC.facturation ) AS facturation_cumulee,
	IF( C.termine, 0, VP.cout - SUM( FC.facturation ) ) AS reste_a_facturer
FROM
	vue_mois VM
		INNER JOIN contrats C
			ON DATE_FORMAT( C.debut, "%Y%m" ) <= CONCAT( VM.annee, VM.mois )
			AND DATE_FORMAT( C.fin, "%Y%m" ) >= CONCAT( VM.annee, VM.mois )
		INNER JOIN vue_previsionnel VP
			ON C.id = VP.contrats_id
		LEFT JOIN vue_facturation VF
			ON C.id = VF.contrats_id
			AND VM.annee = VF.annee
			AND VM.mois = VF.mois
		LEFT JOIN contrats_facturation FC
			ON C.id = FC.contrats_id
			AND CONCAT( VM.annee, VM.mois ) >= DATE_FORMAT( F.date, "%Y%m" )
WHERE
	C.is_devis = 0
	AND C.is_cancelled = 0
GROUP BY
	VM.annee,
	VM.mois,
	VP.cout,
	VF.facturation,
	C.termine

J'ai déjà fait quelques vues dans notre projet oui.

Je vais essayer ça, et j'essaierai aussi d'y ajouter un paramètre me permettant de filtrer les résultats sur une année dans la requête.

Concernant les vues, j'avais dans l'idée qu'on utilisait ça de façon "exceptionnelle" (j'exagère mais l'idée est là).
Ici je vois qu'on a 3 vues pour une requête, j'en déduis qu'il n'est pas gênant d'en avoir un certain nombre. Pas pour une raison technique mais juste de façon de faire.

Bref, je vais tester tout ça et je te tiens au courant de toute façon.
Ca ne sera peut-être que lundi histoire de te prévenir.

Merci beaucoup.

Au passage, hormis la formation sur OpenClassRooms que je vais commencer, si tu as des sites intéressants traitant du SQL (et plus précisément de MySQL) je suis preneur.

jouvrard a écrit:

hormis la formation sur OpenClassRooms que je vais commencer, si tu as des sites intéressants traitant du SQL (et plus précisément de MySQL) je suis preneur.

Entre le document "Conception BDD" et le cours MySQL de Openclassrooms tu as de solides bases de conception et d'administration. Après, encore et toujours la documentation MySQL, inévitable et pertinente. Enfin, pour le reste, des questions bien posées sur ton moteur de recherche Internet

jouvrard a écrit:

j'avais dans l'idée qu'on utilisait ça de façon "exceptionnelle" (j'exagère mais l'idée est là).

Ici je vois qu'on a 3 vues pour une requête

Ce n'est pas de façon exceptionnelle, mais plutôt chaque fois que nécessaire ... De plus, ces vues te seront utiles pour d'autres requêtes, notamment si tu veux faire le même travail pour un seul contrat, par exemple sur la fiche d'un contrat pouvoir afficher son graphe mensuel de facturation ou le reste à facturer ... en toute simplicité ...

Je viens de tester et il y a juste un petit truc.

La notion de "termine" ne se trouve pas dans la table "contrats" mais dans la table "contrats_facturation", c.termine n'existe donc pas.

Il faut donc ajouter un champ "termine" à la vue vue_facturation, si une des facturations d'un contrat a été notée comme terminé, et bien entendu ça ne fonctionne pas en ajoutant direction dans la requête le champs "termine":

CREATE VIEW vue_facturation AS
SELECT
    YEAR( date ) AS annee,
    MONTH( date) AS mois,
    contrats_id,
    termine,   <-- Ca marche pas 
    SUM( facturation ) AS facturation
FROM contrats_facturation
GROUP BY
    YEAR( date ),
    MONTH( date ),
    contrats_id;

Je comprends pourquoi ça ne marche pas, mais par contre pour le moment j'ai un souci pour lui dire en dros "si une des facturations du contrat est marquée comme terminée, alors "termine" = 1.
Je suis en pleine recherche.

EDIT: Il suffit que je pose la question pour trouver quelque chose qui à l'air de fonctionner. A voir si cette solution est la bonne:

SELECT
    YEAR( date ) AS annee,
    MONTH( date ) AS mois,
    contrats_id,
    MAX(termine),
    SUM( facturation ) AS facturation
FROM contrats_facturation
GROUP BY
    YEAR( date ),
    MONTH( date ),
    contrats_id

-
Edité par jouvrard 8 février 2021 à 15:26:36

jouvrard a écrit:

La notion de "termine" ne se trouve pas dans la table "contrats" mais dans la table "contrats_facturation"

C'est balaud ... Ce n'est pas la facture saisie qui est terminée, mais bien la facturation du contrat en question ... Mais bon c'est une question de conception, à chacun sa vision ...

jouvrard a écrit:

MAX(termine) AS termine

Ta solution est astucieuse ! Pour trouver si la facturation d'un contrat est terminée on cherche la valeur maxi du booléen sur toute ses factures, ça marche ... Du coup il faut lui donner un alias et dans la requête principale utiliser VF.termine au lieu de C.termine (et idem dans le GROUP BY).

Ok, c'est donc bien ce que j'ai fait donc nickel.

Pour l'histoire de la fin de la facturation, avec le booléen au niveau de la table facturation me permet de savoir sur quel facturation entrée celle-ci a été marquée comme terminée.
Tu vas me dire que j'aurais pu rapporter l'ID de la "facturation terminée" sur la table "contrats" aussi remarque...

Enfin pour le coup comme j'ai déjà un certain nombre de trucs qui se rapportent à cette table contrats_facturation, pas cerain que je modifie cette partie-là.

Voici donc la requête mise à jour:

SELECT
    VM.annee,
    VM.mois,
    VP.cout,
    VF.facturation,
    SUM( FC.facturation ) AS facturation_cumulee,
    IF( VF.termine, 0, VP.cout - SUM( FC.facturation ) ) AS reste_a_facturer
FROM
    vue_mois VM
        INNER JOIN contrats C
            ON DATE_FORMAT( C.debut, "%Y%m" ) <= CONCAT( VM.annee, VM.mois )
            AND DATE_FORMAT( C.fin, "%Y%m" ) >= CONCAT( VM.annee, VM.mois )
        INNER JOIN vue_previsionnel VP
            ON C.id = VP.contrats_id
        LEFT JOIN vue_facturation VF
            ON C.id = VF.contrats_id
            AND VM.annee = VF.annee
            AND VM.mois = VF.mois
        LEFT JOIN contrats_facturation FC
            ON C.id = FC.contrats_id
            AND CONCAT( VM.annee, VM.mois ) >= DATE_FORMAT( FC.date, "%Y%m" )
WHERE
    C.is_devis = 0
    AND C.is_cancelled = 0
GROUP BY
    VM.annee,
    VM.mois,
    VP.cout,
    VF.facturation,
    VF.termine

J'ai corrigé la ligne de la requête
"AND CONCAT( VM.annee, VM.mois ) >= DATE_FORMAT( F.date, "%Y%m" )"
vers "AND CONCAT( VM.annee, VM.mois ) >= DATE_FORMAT( FC.date, "%Y%m" )"
==> F.date est devenu FC.date, je suppose que c'était ça.

-
Edité par jouvrard 8 février 2021 à 17:05:50

jouvrard a écrit:

Pour l'histoire de la fin de la facturation, avec le booléen au niveau de la table facturation me permet de savoir sur quel facturation entrée celle-ci a été marquée comme terminée.

Pas de problème, c'est une question de point de vue ... Pour moi c'est forcément la plus récente facturation qui est "soldante" ... Je suis d'ailleurs plus à discuter du nom de la colonne que de sa position Ce serait plus parlant de la nommer "soldante" ou "derniere" ...

Allez, j'arrête de chercher la petite bête, bonne continuation !

Of, je ne me vexerai pas...

Après avoir à nouveau testé je viens de voir un truc en fait: si on n'a aucune facturation de faite, alors on a un reste à facturer de "NULL" au lieu d'avoir le cout total du contrat.
A moins que je ne passe à côté de quelque chose, mais je ne vois pas.
Normalement à partir du moment où un contrat est commencé (C.debut), alors le reste à facturer est son cout total moins ce qui a été facturé.

Je vais vider un peu les données que j'ai et faire quelques tests supplémentaires.

jouvrard a écrit:

si on n'a aucune facturation de faite, alors on a un reste à facturer de "NULL" au lieu d'avoir le cout total du contrat

Oui, car 1000 - NULL = NULL ... Il faut utiliser COALESCE pour corriger :

IF( VF.termine, 0, VP.cout - COALESCE( SUM( FC.facturation ), 0 ) ) AS reste_a_facturer

J'étais en train de retourner sur le sujet pour dire que j'avais trouvé, et en effet j'avais la même solution, t'es trop rapide c'est vexant là par contre...

Merci beaucoup, vraiment ! 👍

EDIT: Un petit souci est présent.

En fait lorsque le champ "termine"  est à 1, alors le reste à facturer est à 0... mais seulement pour le mois en cours et pas pour les suivants où on retrouve le cours normal de la facturation.

Après, s'il faut ajouter un champ "date_facturation_termine" dans la table "contrats" pour que ça soit plus simple c'est tout à fait faisable.

-
Edité par jouvrard 9 février 2021 à 10:11:56

Je suis carrément passé à côté de ce problème ... Et en fait il est plus large ... Je pense que ma requête ne marche par correctement dans tous les cas ... Elle ne faisait pas la somme des prévisionnels ...

Reprenons un peu ...

Pour chaque mois il faut la somme :

1. des prévisionnels des contrats concernés par le mois donné
2. des facturations sur le mois donné pour les contrats concernés par le mois donné
3. des facturations cumulées pour les contrats concernés par le mois donné

Pour cela on va devoir modifier la vue de facturation pour qu'elle reprenne tous les mois :

CREATE VIEW vue_facturation AS
SELECT
	VM.annee,
	VM.mois,
	CF.contrats_id,
	SUM( IFNULL( CF.facturation, 0 ) ) AS facturation
FROM
	vue_mois VM
		LEFT JOIN contrats_facturation CF
			ON CONCAT( VM.annee, VM.mois ) = DATE_FORMAT( CF.date, "%Y%m" )
GROUP BY
	VM.annee,
	VM.mois,
	CF.contrats_id;

Et on va avoir besoin d'une vue pour la facturation cumulée :

CREATE VIEW vue_facturation_cumul AS
SELECT
	VM.annee,
	VM.mois,
	CF.contrats_id,
	SUM( IFNULL( CF.facturation, 0 ) ) AS facturation,
	MAX( IFNULL( CF.termine, 0 ) ) AS termine
FROM
	vue_mois VM
		LEFT JOIN contrats_facturation CF
			ON CONCAT( VM.annee, VM.mois ) >= DATE_FORMAT( CF.date, "%Y%m" )
GROUP BY
	VM.annee,
	VM.mois,
	CF.contrats_id;

Maintenant, chaque vue ne retourne qu'une seule ligne par contrat et par mois, on peut tout regrouper :

SELECT
	VM.annee,
	VM.mois,
	SUM( IFNULL( VP.cout, 0 ) ) AS cout,
	SUM( IFNULL( VF.facturation, 0 ) ) AS fact_mois,
	SUM( IFNULL( VFC.facturation, 0 ) ) AS fact_cumul,
	SUM( IFNULL( VP.cout, 0 ) ) - SUM( IFNULL( IF( VFC.termine, VP.cout, VFC.facturation ), 0 ) ) AS reste
FROM
	vue_mois VM
		INNER JOIN contrats C
			ON DATE_FORMAT( C.debut, "%Y%m" ) <= CONCAT( VM.annee, VM.mois )
			AND DATE_FORMAT( C.fin, "%Y%m" ) >= CONCAT( VM.annee, VM.mois )
		INNER JOIN vue_previsionnel VP
			ON C.id = VP.contrats_id
		LEFT JOIN vue_facturation VF
			ON C.id = VF.contrats_id
			AND VM.annee = VF.annee
			AND VM.mois = VF.mois
		LEFT JOIN vue_facturation_cumul VFC
			ON C.id = VFC.contrats_id
			AND VM.annee = VFC.annee
			AND VM.mois = VFC.mois
GROUP BY
	VM.annee,
	VM.mois

Je viens de passer les modifications.

Pour explication j'ai créé 3 contrats: 2 débutants en février pour un total de 2000€ et 1 débutant en mars avec un cout de 2500€.

EDIT> Je ne sais pas si tu as vu le message que j'ai laissé avant ici mais il s'agissait d'une erreur de ma part, les totaux me paraissent totalement bons !

-
Edité par jouvrard 9 février 2021 à 15:07:30

Wouhou !

Bon, truc de fou: je lance la requête sur phpMyAdmin pas de souci, je la lance à partir de mon code et j'ai un résultat vide...
Je viens de tester avec une requête toute bête pour voir si l'erreur venait d'ailleurs mais dans ce cas-là j'ai les résultats demandés qui arrivent.

Au cas où, mon code:

$sql = 'SELECT
                    VM.annee,
                    VM.mois,
                    SUM( IFNULL( VP.cout, 0 ) ) AS cout,
                    SUM( IFNULL( VF.facturation, 0 ) ) AS fact_mois,
                    SUM( IFNULL( VFC.facturation, 0 ) ) AS fact_cumul,
                    SUM( IFNULL( VP.cout, 0 ) ) - SUM( IFNULL( IF( VFC.termine, VP.cout, VFC.facturation ), 0 ) ) AS reste
                FROM
                    vue_mois VM
                        INNER JOIN contrats C
                            ON DATE_FORMAT( C.debut, "%Y%m" ) <= CONCAT( VM.annee, VM.mois )
                            AND DATE_FORMAT( C.fin, "%Y%m" ) >= CONCAT( VM.annee, VM.mois )
                        INNER JOIN vue_previsionnel VP
                            ON C.id = VP.contrats_id
                        LEFT JOIN vue_facturation VF
                            ON C.id = VF.contrats_id
                            AND VM.annee = VF.annee
                            AND VM.mois = VF.mois
                        LEFT JOIN vue_facturation_cumul VFC
                            ON C.id = VFC.contrats_id
                            AND VM.annee = VFC.annee
                            AND VM.mois = VFC.mois
                GROUP BY
                    VM.annee,
                    VM.mois';
        try {
            $req = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
            $req->execute();
            $res = $req->fetchAll(PDO::FETCH_ASSOC);
            return $res;
        } catch (Exception $e) {
            return $this->sqlError($req->errorInfo());
        }

Aucune erreur, rien...

J'ai installé la version d'essai de PHPStorm pour voir et ce que je remarque c'est que sur l'affichage des vues, il ne parvient pas à voir ce qu'il y a dans "vue_facturation" et "vue_facturation_cumul", comme si c'était vide, sans même des champs à l'intérieur.
Je ne sais pas si ces 2 comportements sont liés.

Je viens d'essayer en "intégrant" les requêtes des 2 vues éventuellement problématiques directement dans la requête principale, sans résultat non plus.
Dans mon code, résultat vide, sur phpMyAdmin, tout est ok...

SELECT
                    VM.annee,
                    VM.mois,
                    SUM( IFNULL( VP.cout, 0 ) ) AS cout,
                    SUM( IFNULL( VF.facturation, 0 ) ) AS fact_mois,
                    SUM( IFNULL( VFC.facturation, 0 ) ) AS fact_cumul,
                    SUM( IFNULL( VP.cout, 0 ) ) - SUM( IFNULL( IF( VFC.termine, VP.cout, VFC.facturation ), 0 ) ) AS reste
                FROM
                    vue_mois VM
                        INNER JOIN contrats C
                            ON DATE_FORMAT( C.debut, "%Y%m" ) <= CONCAT( VM.annee, VM.mois )
                            AND DATE_FORMAT( C.fin, "%Y%m" ) >= CONCAT( VM.annee, VM.mois )
                        INNER JOIN vue_previsionnel VP
                            ON C.id = VP.contrats_id
                        LEFT JOIN
                            (SELECT
                                VM.annee,
                                VM.mois,
                                CF.contrats_id,
                                SUM( IFNULL( CF.facturation, 0 ) ) AS facturation
                            FROM
                                vue_mois VM
                                    LEFT JOIN contrats_facturation CF
                                        ON CONCAT( VM.annee, VM.mois ) = DATE_FORMAT( CF.date, "%Y%m" )
                            GROUP BY
                                VM.annee,
                                VM.mois,
                                CF.contrats_id) VF
                            ON C.id = VF.contrats_id
                            AND VM.annee = VF.annee
                            AND VM.mois = VF.mois
                        LEFT JOIN (SELECT
                                    VM.annee,
                                    VM.mois,
                                    CF.contrats_id,
                                    SUM( IFNULL( CF.facturation, 0 ) ) AS facturation,
                                    MAX( IFNULL( CF.termine, 0 ) ) AS termine
                                FROM
                                    vue_mois VM
                                        LEFT JOIN contrats_facturation CF
                                            ON CONCAT( VM.annee, VM.mois ) >= DATE_FORMAT( CF.date, "%Y%m" )
                                GROUP BY
                                    VM.annee,
                                    VM.mois,
                                    CF.contrats_id) VFC
                            ON C.id = VFC.contrats_id
                            AND VM.annee = VFC.annee
                            AND VM.mois = VFC.mois
                GROUP BY
                    VM.annee,
                    VM.mois

Je continue toujours mes tests en lançant à partir de mon code juste la requête de la vue "vue_facturation", et... résultat vide! (et toujours ok sur phpMyAdmin):

SELECT
    VM.annee,
    VM.mois,
    CF.contrats_id,
    SUM( IFNULL( CF.facturation, 0 ) ) AS facturation
FROM
    vue_mois VM
        LEFT JOIN contrats_facturation CF
            ON CONCAT( VM.annee, VM.mois ) = DATE_FORMAT( CF.date, "%Y%m" )
GROUP BY
    VM.annee,
    VM.mois,
    CF.contrats_id

-
Edité par jouvrard 10 février 2021 à 10:35:59

jouvrard a écrit:

à partir de mon code et j'ai un résultat vide [...] Aucune erreur, rien...

Par précaution, vérifie bien que tu as activé l'affichage des erreurs, PHP et PDO (cf FAQ PHP si besoin).

jouvrard a écrit:

Dans mon code, résultat vide, sur phpMyAdmin, tout est ok

Es-tu sûr que ton code se connecte à la même base de données que celle consultée avec PHPMyAdmin ?

-
Edité par Benzouye 10 février 2021 à 10:44:40

J'étais persuadé que j'avais déjà activé ça mais après vérification ce n'était pas le cas...
J'ai donc ajouté ceci:

$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

Et je reçois l'erreur suivante:

Warning: PDOStatement::execute(): SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation

Problème de charset visiblement quoi.

Dans ma connexion à la BDD j'avais ceci:

$this->pdo = new PDO("mysql:dbname=$dbname;host=$host;charset=utf8mb4", $username, $password);

En mettant juste "charset=utf8" je retrouve des résultats.
Je vais voir si ça ne change pas quelque chose ailleurs par contre, sait-on jamais...

-
Edité par jouvrard 10 février 2021 à 11:02:50

jouvrard a écrit:

Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation

C'est bien la première fois que je vois cela ...

jouvrard a écrit:

En mettant juste "charset=utf8" je retrouve des résultats

Je pense que le plus propre c'est de mettre en cohérence les collations dans ta base de données pour n'avoir que du unicode ou que du general (tables et colonnes), ce serait dommage de se priver d'utf8mb4 ...

Bon, en fait la vue "vue_mois" étant en utf8mdb_unicode_ci alors que tout le reste est en general_ci. Pourquoi, je ne sais pas.
Bref, changé ça et tout roule.

Et la requête que j'ai utilisés pour chercher tous les unicode_ci au cas où d'autres tomberaient là dessus:

SELECT table_schema, table_name, column_name, character_set_name, collation_name

FROM information_schema.columns

WHERE collation_name = 'utf8mb4_unicode_ci'

ORDER BY table_schema, table_name,ordinal_position