Discussion :

[Riri80]

Bonjour,

- je possède dans ma base de données contenant les tables suivantes :
TABLE USERS : id, nom, prenom
TABLE ENTITE : id, nom
TABLE AFFECTATION : id, id_user, id_entite, date_debut

- la date de début d'affectation est au format date (xxxx-xx-xx).
- je peux avoir plusieurs affectation pour une personne donnée :
ex : id_user = 1 -> affecté à l'entité 1 à partir du 01/01/2011 et à l'entité 2 à partir du 15/05/2012.

Mon objectif est de créer un tableau complet du nombre de personnes par entité :
ex :
Array(
[Entité 1] => Array(

[Mois 1] =>Array(

[Jour 1] => Nombre de personnes affectée à l'entité 1 le 01/01/2011

...

[Jour 31] => Nombre de personnes affectée à l'entité 1 le 31/01/2011

)

...

[Mois 12] =>Array(

[Jour 1] => Nombre de personnes affectée à l'entité 1 le 01/12/2011

...

[Jour 31] => Nombre de personnes affectée à l'entité 1 le 31/12/2011

...
[Entité n] => Array(

[Mois 1] =>Array(

[Jour 1] => Nombre de personnes affectée à l'entité n le 01/01/2011

...

[Jour 31] => Nombre de personnes affectée à l'entité n le 31/01/2011

...

[Mois 12] =>Array(

[Jour 1] => Nombre de personnes affectée à l'entité n le 01/12/2011

...

[Jour 31] => Nombre de personnes affectée à l'entité n le 31/12/2011

)

)

)

Pour cela j'ai créé quelques fonctions PHP qui y arrivent mais le cheminement ne parait laborieux et je me demande si je ne peux pas filtrer un maximum par mysql pour faciliter la chose ?
En PHP, je suis les étapes suivantes :
- création d'un tableau [USER] => [JOUR DE L'ANNEE COURANTE] => ID ENTITE
- création d'un tableau [ENTITE] => [MOIS] => Nombre de jours cumulés où un utilisateur est affecté à cette entité

J'ai encore des problèmes quand les données concernent plusieurs année mais j'ai bon espoir de les corriger.
Auriez-vous des conseils sur ce que j'essaie de faire ?
Seriez-vous parti sur une autre approche ?

Merci d'avance.

[gene69]

euuh en fait tu n'es obligé de rien faire de particulier.

1/ tu selectionnes en vrac dans ta base de donnée
2/ tu découpes ta date (en 3 entiers de préférences ) en $jour, $mois ( $annee )
2bis) tu récuperes l'entité
3) tu ranges chaque lignes de la base dans une listes

Code speudophp :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
while ( $row = xxx_fetch_row() ){ 
$entite=
$mois=
$jour=
$liste[$entite][$mois][$jour]=$row;
}

reste la requete.

edit: j'avais pas bien compris la question. euuh c'est compliqué, j'y réfléchi et voilà. D'abord, comment tu représentes les "fin d'affectations" ?

[gene69]

le plus simple est de dire que le nombre de personne affecté le 1er janvier est 0 partout (ou une constante magique que tu configures une fois par an).

si ton systeme fonctionne comme là ou j'avais travaillé, on est affecté à un poste/entité jusqu'à l'affectation. Un licenciement c'est une affectation sur l'entité des personnes licenciées.

comme tu n'as pas de date de fin d'affectation, euuh c'est ennyant on est obligé d'aller à l'origine de chaque entité.

On va retranscrire jour par jour l'effectif des personnes, sachant que si pas d'information alors la personne ne change pas d'affectation.

"le jour d'avant, toutes les entitées sont vides".

on selectionne toutes les lignes de la requete

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT id, id_user, id_entite, date_debut
FROM affectation
ORDER BY date_debut ASC

si ta table est bien foutu tu peux aussi dire id asc, parce que ben par construction ta clé primaire est triée par jour... sauf si tu fais des mouvement rétroactives ou en avance.

ensuite l'algo donne:

Code speudo :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
$jourdavant=null
Pour chaque nouvelle affectation
{
 $jourcourant = date_debut de la affectation courante
 
 // si on a fini de traiter le jour d'avant.
 si ( $jourcourant!==$jourdavant )
 {
  $liste[$entite][$annee][$mois][$jour]['nombre'] = count($personnel[$identite]);
  }
 // on initialise pour le jour courant
   $jour= jour(date_debut) ;
   $mois = mois(date_debut);
   $annee = annee(date_debut);
 
 
 
 //on retire l'utilisateur de l'entité précédante.
 si (isset( $affectation[$iduser] ) ) 
 {
  $prevIdentite = $affectation[$idpersonne];
  $tmp = $personnel[ $prevIdentite ] 
 
 // astuce typique php, facile a écrire mais pas a expliquer.
  $tmp =array_flip($tmp)
  unset($tmp[$iduser]);
  $tmp =array_flip($tmp)
 
  $personnel[$prevIdEntite] = tmp;
 }
 
 // on met a jour l'affectation courate de la personne.
 $affectation[$iduser]=$identite;
 
 //on met a jour l'effectif de la nouvelle entite
 $personnel[$identite][]=$iduser;
 
 $jourdavant=$jourcourant;
 
}//fin boucle
 $liste[$entite][$annee][$mois][$jour]['nombre'] = count($personnel[$identite]);

ya des ptit détails à améliorer mais si quelqu'un a plus performant je suis currieux de voir.

edit: correction ptit bug.

[gene69]

et ensuite il est possible de foisonner dans les jours manquant en php par un nouveau parcours ...

oups y faut corriger un pb dans l'algo.

[Riri80]

Merci beaucoup pour ces éclaircissements !
Je vais essayer de m'en sortir avec ça.

[redoran]

salut ; si je me refere a votre message , on constate que l'affectation et selon un intervalle de temps et non pour une journée sauf si le cas.
dans le premier cas , les résultats doivent être selon un intervalle de temps .
bon ; là je crois qu'il faut passé par les outils du décisionnel parce qu'il s'agit des cubes , j'ai eu la même problématique.
je crois que mysql ne support pas les requêtes complexe du décisionnel.

[CinePhil]

Pour commencer, puisque tu veux les effectifs de tous les jours de tous les mois de toutes les années, il faut que tu modélises et implémentes en BDD un calendrier car tu n'as, pour le moment, que des dates de début d'affectation et il peut y avoir des trous.

Ensuite, comme tu n'as que des dates de début d'affectation, il faudrait commencer, en SQL, par définir les périodes d'affectation :
- de date de début d'une affectation d'une personne jusqu'à la prochaine - 1 jour ;
- ou de date de début d'une affectation d'une personne jusqu'à aujourd'hui s'il n'y a pas de prochaine.

Cette requête devrait permettre de trouver toutes les périodes d'affectation des users aux entités :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT a1.id_user, a1.id_entite,
	a1.date_debut,
	CASE
		WHEN a2.date_debut IS NULL THEN CURRENT_DATE
		ELSE DATE_SUB(MIN(a2.date_debut), INTERVAL 1 DAY)
	END AS date_fin
FROM affectation a1
LEFT OUTER JOIN affectation a2
	ON a2.id_user = a1.id_user
	AND a2.id_entite = a1.id_entite
	AND a2.date_debut > a1.date_debut
GROUP BY a1.id_user, a1.id_entite, a1.date_debut

Enfin, en faisant une jointure avec le calendrier et la table des entités afin d'avoir toutes les entités et tous les jours, cette requête devrait donner le nombre de users par entité et par jour :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT e.id, c.jour,
	COUNT(tmp.id_user) AS nb_users
FROM
(
	SELECT a1.id_user, a1.id_entite,
		a1.date_debut,
		CASE
			WHEN a2.date_debut IS NULL THEN CURRENT_DATE
			ELSE DATE_SUB(MIN(a2.date_debut), INTERVAL 1 DAY)
		END AS date_fin
	FROM affectation a1
	LEFT OUTER JOIN affectation a2
		ON a2.id_user = a1.id_user
		AND a2.id_entite = a1.id_entite
		AND a2.date_debut > a1.date_debut
	GROUP BY a1.id_user, a1.id_entite, a1.date_debut
) tmp
RIGHT OUTER JOIN calendrier c
	ON c.jour BETWEEN tmp.date_debut AND tmp.date_fin
RIGHT OUTER JOIN entite e ON e.id = tmp.id_entite
GROUP BY tmp.id_entite, c.jour
ORDER BY tmp.id_entite, c.jour

Il ne restera plus qu'à gérer les ruptures de mois et d'entités en PHP pour la présentation des données.

À tester.