86,8 → 86,10 |
$observations = $this->chargerObservations($liaisons); |
$total = $this->compterObservations(); |
$this->navigation->setTotal($total); |
$observations = $this->chargerImages($observations); |
$observations = $this->chargerDeterminations($observations); |
// modifie $observations |
$this->chargerImages($observations); |
// modifie $observations |
$this->chargerDeterminations($observations); |
|
//Compute |
$resultats = array(); |
103,8 → 105,10 |
$total = $this->compterObservations(); |
$this->navigation->setTotal($total); |
$observations = $this->chargerObservations($liaisons); |
$observations = $this->chargerImages($observations); |
$observations = $this->chargerDeterminations($observations); |
// modifie $observations |
$this->chargerImages($observations); |
// modifie $observations |
$this->chargerDeterminations($observations); |
|
// Mettre en forme le résultat et l'envoyer pour affichage |
$resultat->corps = array('entete' => $this->conteneur->getEntete(), 'resultats' => $observations); |
471,41 → 475,49 |
* Chargement depuis la bdd de toutes les liaisons entre images et observations |
* */ |
private function chargerLiaisons() { |
$requeteLiaisons = 'SELECT SQL_CALC_FOUND_ROWS '. |
' dob.id_observation, nom_sel, famille, ce_zone_geo, zone_geo, lieudit, '. |
' station, milieu, date_observation, dob.mots_cles_texte, date_transmission, di.id_image, '. |
' dob.ce_utilisateur, prenom, nom, courriel, '. |
' dob.prenom_utilisateur, dob.nom_utilisateur, dob.courriel_utilisateur, '. |
' nom_original, dob.commentaire AS dob_commentaire, '. |
' dob.nt, dob.nom_sel_nn '. |
$requeteLiaisons = sprintf( |
'SELECT SQL_CALC_FOUND_ROWS '. |
' dob.id_observation, nom_sel, famille, ce_zone_geo, zone_geo, lieudit, '. |
' station, milieu, date_observation, dob.mots_cles_texte, date_transmission, di.id_image, '. |
' dob.ce_utilisateur, prenom, nom, courriel, '. |
' dob.prenom_utilisateur, dob.nom_utilisateur, dob.courriel_utilisateur, '. |
' nom_original, dob.commentaire AS dob_commentaire, '. |
' dob.nt, dob.nom_sel_nn '. |
'FROM del_observation AS dob '. |
' LEFT JOIN del_utilisateur AS du '. |
' ON du.id_utilisateur = dob.ce_utilisateur '. |
' LEFT JOIN del_obs_image AS doi '. |
' ON doi.id_observation = dob.id_observation '. |
' LEFT JOIN del_image AS di '. |
' ON di.id_image = doi.id_image '. |
$this->chargerClauseWhere(). |
' LEFT JOIN del_utilisateur AS du '. |
' ON du.id_utilisateur = dob.ce_utilisateur '. |
' LEFT JOIN del_obs_image AS doi '. |
' ON doi.id_observation = dob.id_observation '. |
' LEFT JOIN del_image AS di '. |
' ON di.id_image = doi.id_image '. |
'%s'. |
' GROUP BY doi.id_observation'. |
' ORDER BY '.$this->tri.' '.$this->directionTri.' '. |
$this->gestionBdd->getLimitSql(); |
' ORDER BY %s %s %s -- %s', |
$this->chargerClauseWhere(), |
$this->tri, |
$this->directionTri, |
$this->gestionBdd->getLimitSql(), |
__FILE__ . ':' . __LINE__); |
|
return $this->bdd->recupererTous($requeteLiaisons); |
} |
|
private function chargerLiaisonsSimple() { |
$requeteObs = 'SELECT SQL_CALC_FOUND_ROWS '. |
' dob.id_observation, nom_sel, famille, ce_zone_geo, zone_geo, lieudit, '. |
' station, milieu, date_observation, dob.mots_cles_texte, date_transmission, '. |
' dob.ce_utilisateur, prenom, nom, courriel, '. |
' dob.prenom_utilisateur, dob.nom_utilisateur, dob.courriel_utilisateur, '. |
' dob.commentaire as dob_commentaire, '. |
' dob.nt, dob.nom_sel_nn '. |
$requeteObs = sprintf( |
'SELECT SQL_CALC_FOUND_ROWS '. |
' dob.id_observation, nom_sel, famille, ce_zone_geo, zone_geo, lieudit, '. |
' station, milieu, date_observation, dob.mots_cles_texte, date_transmission, '. |
' dob.ce_utilisateur, prenom, nom, courriel, '. |
' dob.prenom_utilisateur, dob.nom_utilisateur, dob.courriel_utilisateur, '. |
' dob.commentaire as dob_commentaire, '. |
' dob.nt, dob.nom_sel_nn '. |
'FROM del_observation AS dob '. |
' LEFT JOIN del_utilisateur AS du |
ON dob.ce_utilisateur = du.id_utilisateur '. |
' ORDER BY '.$this->tri.' '.$this->directionTri.' '. |
$this->gestionBdd->getLimitSql(); |
' LEFT JOIN del_utilisateur AS du ON dob.ce_utilisateur = du.id_utilisateur '. |
' ORDER BY %s %s %s -- %s', |
$this->tri, |
$this->directionTri, |
$this->gestionBdd->getLimitSql(), |
__FILE__ . ':' . __LINE__); |
$liaisons = $this->bdd->recupererTous($requeteObs); |
|
return $liaisons; |
548,15 → 560,17 |
* Sélectionner toutes les images de chaque observation |
* @param array $observations la liste des observations |
* */ |
private function chargerImages($observations) { |
private function chargerImages(&$observations) { |
$idObs = array_values(array_map(create_function('$a', 'return $a["id_observation"];'), |
$observations)); |
$r = sprintf('SELECT id_observation, doi.id_image, date_prise_de_vue AS date, hauteur , largeur ' . |
' FROM del_obs_image AS doi ' . |
' INNER JOIN del_image AS di ON doi.id_image = di.id_image ' . |
' WHERE doi.id_observation IN (%s)', |
implode(',',$idObs)); |
' WHERE doi.id_observation IN (%s) -- %s', |
implode(',',$idObs), |
__FILE__ . ':' . __LINE__); |
$images = $this->bdd->recupererTous($r); |
if(!$images) return; |
foreach ($images as $image) { |
$idObs = $image['id_observation']; |
// rapide formatage de l'image puisque le SELECT à déjà fait le gros du travail |
564,7 → 578,6 |
$image['binaire.href'] = $this->formaterLienImage($image['id_image']); |
$observations['"' . $idObs . '"']['images'][] = $image; |
} |
return $observations; |
} |
|
/** |
571,17 → 584,18 |
* Récupérer toutes les déterminations et le nombre de commentaire au total |
* @param array $observations la liste des observations à mettre à jour |
* */ |
private function chargerDeterminations($observations) { |
foreach ($observations as $id => $observation) { |
$idObs = $observation['id_observation']; |
$requetePropositions = 'SELECT * '. |
'FROM del_commentaire AS dc '. |
'WHERE dc.nom_sel IS NOT NULL '. |
" AND ce_observation = $idObs "; |
$propositions = $this->bdd->recupererTous($requetePropositions); |
$observations[$id]['commentaires'] = $this->formaterDeterminations($propositions); |
private function chargerDeterminations(&$observations) { |
$idObs = array_values(array_map(create_function('$a', 'return $a["id_observation"];'), |
$observations)); |
$r = sprintf('SELECT * FROM del_commentaire AS dc WHERE dc.nom_sel IS NOT NULL AND ce_observation IN (%s) -- %s', |
implode(',',$idObs), |
__FILE__ . ':' . __LINE__); |
$propositions = $this->bdd->recupererTous($r); |
if(!$propositions) return; |
foreach ($propositions as $proposition) { |
$idObs = $proposition['ce_observation']; |
$observations['"' . $idObs . '"']['commentaires'][] = $this->formaterDeterminations($proposition); |
} |
return $observations; |
} |
|
/** |
588,17 → 602,12 |
* Charger les votes sur les déterminations |
* @param Array $observations le tableau des observations à mettre à jour |
* */ |
private function chargerVotes($ids_proposition, $propositions) { |
$listePropositions = implode(', ', $ids_proposition); |
$requeteVotes = 'SELECT * '. |
'FROM del_commentaire_vote '. |
"WHERE ce_proposition IN ($listePropositions)"; |
|
$resultatsVotes = $this->bdd->recupererTous($requeteVotes); |
private function chargerVotes($ids_proposition, &$propositions) { |
$resultatsVotes = $this->bdd->recupererTous(sprintf('SELECT * FROM del_commentaire_vote WHERE ce_proposition IN (%s)', |
implode(',', $ids_proposition))); |
foreach ($resultatsVotes as $vote) { |
$propositions[$vote['ce_proposition']]['votes'][$vote['id_vote']] = $this->formaterVote($vote); |
} |
return $propositions; |
} |
|
/** |
605,15 → 614,10 |
* Charger le nombre de commentaires (sans détermination) associé à l'observation |
* @param Array $observations le tableau des observatins à mettre à jour |
* */ |
private function chargerNombreCommentaire($ids_proposition, $propositions) { |
$listePropositions = implode(', ', $ids_proposition); |
$requeteNbCommentaires = 'SELECT ce_commentaire_parent, ce_proposition, COUNT( id_commentaire ) AS nb '. |
'FROM del_commentaire '. |
"WHERE ce_proposition IN ($listePropositions) ". |
'GROUP BY ce_proposition '; |
|
$listeCommentaires = $this->bdd->recupererTous($requeteNbCommentaires); |
|
private function chargerNombreCommentaire($ids_proposition, &$propositions) { |
$listeCommentaires = $this->bdd->recupererTous(sprintf('SELECT ce_commentaire_parent, ce_proposition, COUNT( id_commentaire ) AS nb '. |
'FROM del_commentaire WHERE ce_proposition IN (%s) GROUP BY ce_proposition', |
implode(',', $ids_proposition))); |
foreach ($listeCommentaires as $ligneProposition) { |
// ce test sert à exclure les proposition de 1er niveau qui sont elles aussi des commentaires |
if($ligneProposition['ce_commentaire_parent'] != null && $ligneProposition['ce_commentaire_parent'] != 0) { |
622,7 → 626,6 |
$propositions[$ligneProposition['ce_proposition']]['observation']['nb_commentaires'] = $ligneProposition['nb']; |
} |
} |
return $propositions; |
} |
|
/*------------------------------------------------------------------------------- |
658,22 → 661,24 |
* Formate une liste de proposition en fonction du fichier de configuration |
*/ |
private function formaterDeterminations($listePropositions) { |
if(!$listePropositions) return array(); |
$propositions_format = array(); |
if ($listePropositions != array()) { |
foreach ($listePropositions as $id => $proposition) { |
$ids_proposition[] = $proposition['id_commentaire']; |
|
$proposition_formatee = array('nb_commentaires' => '0'); |
foreach ($this->mappingCommentaire as $nomOriginal => $nomFinal) { |
if (isset($proposition[$nomOriginal])) { |
$proposition_formatee[$nomFinal] = $proposition[$nomOriginal]; |
} |
foreach ($listePropositions as $id => $proposition) { |
$ids_proposition[] = $proposition['id_commentaire']; |
|
$proposition_formatee = array('nb_commentaires' => '0'); |
foreach ($this->mappingCommentaire as $nomOriginal => $nomFinal) { |
if (isset($proposition[$nomOriginal])) { |
$proposition_formatee[$nomFinal] = $proposition[$nomOriginal]; |
} |
$propositions_format[$proposition['id_commentaire']] = $proposition_formatee; |
} |
$propositions_format = $this->chargerVotes($ids_proposition, $propositions_format); |
$propositions_format = $this->chargerNombreCommentaire($ids_proposition, $propositions_format); |
$propositions_format[$proposition['id_commentaire']] = $proposition_formatee; |
} |
// modifie $propositions_format |
$this->chargerVotes($ids_proposition, $propositions_format); |
// modifie $propositions_format |
$this->chargerNombreCommentaire($ids_proposition, $propositions_format); |
return $propositions_format; |
} |
|