Creation d'un tableau avancé à partir de dates en mysql

Version imprimable

21/09/2012, 13h30
Riri80

Creation d'un tableau avancé à partir de dates en mysql

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.
21/09/2012, 14h47
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:

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" ?

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:

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:

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.

21/09/2012, 15h40
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.
21/09/2012, 16h42
Riri80

suite

Merci beaucoup pour ces éclaircissements !
Je vais essayer de m'en sortir avec ça.
22/09/2012, 21h59
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. :cry:

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:

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:

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.