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 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
|
public function resultat($month1,$month2){
$tRequete1=$this->findManySimple("Select (MonthName(datedubilan)) as periode,Year(datedubilan)as annee,count(distinct(mensuelafd.idanimateur))as animateur,sum(mensuelafd.precedentsuivie) as precedentsuivie,sum(mensuelafd.entredumois) as entredumois
FROM mensuelafd
where (mensuelafd.datedubilan >= {D '$month1'} AND mensuelafd.datedubilan <= {D '$month2'}) GROUP BY Month(datedubilan),Year(datedubilan)Order by Month(datedubilan),Year(datedubilan)
");
plugin_debug::addSpy('requete1',$tRequete1);
$tRequete2=$this->findManySimple("SELECT (MonthName(datesortie)) as periode,Year(datesortie)as annee, count(distinct(evafamille.idevafamille)) as nbrefamille,(sum(evafamille.duresuivie)/ count(distinct(evafamille.idevafamille)))as dure,(sum(evafamille.progressurdix)/ count(distinct(evafamille.idevafamille))) as dix,sum(evafamille.vadvalide) as valide,sum(evafamille.vadmarirencontrer) as mari,sum(evafamille.vadferme) as ferme,sum(evafamille.nombredestructuresversfamille) as structdif,sum(evafamille.totalreferencements) as ref,sum(evafamille.groupe1) as niv1, sum(evafamille.groupe1) as niv2, sum(evafamille.groupe3) as niv3, sum(evafamille.groupe4) as niv4, sum(evafamille.groupe5) as niv5, sum(evafamille.groupe6) as niv6
FROM evafamille
where (evafamille.datesortie >= {D '$month1'} AND evafamille.datesortie <= {D '$month2'}) GROUP BY Month( datesortie),Year(datesortie)Order by Month( datesortie),Year(datesortie)");
plugin_debug::addSpy('requete2',$tRequete2);
$tResult=array();
foreach($tRequete1 as $oRequete){
$tResult[$oRequete->periode.'-'.$oRequete->annee]=$oRequete;
}
$tColumnRequete2=array('periode','annee','nbrefamille','dure','dix','valide','mari','ferme','structdif','ref','niv1','niv2','niv3','niv4','niv5','niv6');//toutes les colonnes de la requete 2
foreach($tRequete2 as $oRequete){
//check requete1
if(!isset($tResult[$oRequete->periode.'-'.$oRequete->annee])){
//ici on defini les valeurs par defaut de la requete 1 (si pas de ligne)
$oRequete->animateur=null;
//colonnes suivantes
$tResult[$oRequete->periode.'-'.$oRequete->annee]=$oRequete;
}
foreach($tColumnRequete2 as $sColumn){
$tResult[$oRequete->periode.'-'.$oRequete->annee]->$sColumn=$oRequete->$sColumn;
}
}
//check requete 2
foreach($tRequete1 as $oRequete){
//check requete 2
if(!isset($tResult[$oRequete->periode.'-'.$oRequete->annee]->nbrefamille)){
//ici on defini les valeurs par defaut de la requete 2 (si pas de ligne)
$oRequete->nbrefamille=null;
//colonnes suivantes
$tResult[$oRequete->periode.'-'.$oRequete->annee]=$oRequete;
}
}
plugin_debug::addSpy('result',$tResult);
return $tResult;
} |
Partager