16,12 → 16,22 |
|
private $table_liaison; |
private $table_mots_cles; |
private $champ_id_element_lie; |
|
public function GestionMotsClesChemin($config, $mode = 'obs') { |
$this->config = $config; |
//TODO: switch suivant mode |
$this->table_liaison = 'cel_obs_tags_path_liaison'; |
$this->table_mots_cles = 'cel_obs_tags_path'; |
$this->mode = $mode; |
|
if($mode == 'obs') { |
$this->table_liaison = 'cel_obs_tags_path_liaison'; |
$this->table_mots_cles = 'cel_obs_tags_path'; |
$this->champ_id_element_lie = 'id_obs'; |
} else { |
$this->table_liaison = 'cel_images_tags_path_liaison'; |
$this->table_mots_cles = 'cel_images_tags_path'; |
$this->champ_id_element_lie = 'id_image'; |
} |
} |
|
public function viderTables() { |
34,43 → 44,125 |
return $vidage; |
} |
|
public function convertirNestedSetVersPathEnum() { |
public function construireRequeteOr($mots_cles) { |
return $this->construireRequete($mots_cles, 'OR'); |
} |
|
public function construireRequeteCheminOr($mots_cles) { |
return $this->construireRequeteChemin($mots_cles, 'OR'); |
} |
|
public function construireRequeteAnd($mots_cles) { |
return $this->construireRequete($mots_cles, 'AND'); |
} |
|
public function construireRequeteCheminAnd($mots_cles) { |
return $this->construireRequeteChemin($mots_cles, 'AND'); |
} |
|
public function construireRequete($mots_cles, $operateur = 'AND') { |
$requete = "SELECT * FROM cel_images_tags_path_liaison "; |
$criteres = array(); |
$premier = array_pop($mots_cles); |
$requete .= 'WHERE (tag = '.Cel::db()->proteger($premier).' '; |
|
foreach ($mots_cles as $mot_cle) { |
$requete .= " ".$operateur." id_image IN (SELECT id_image ". |
"FROM cel_images_tags_path_liaison ". |
"WHERE tag = ".Cel::db()->proteger($mot_cle)." ". |
") "; |
} |
$requete .= ")"; |
$images = Cel::db()->requeter($requete); |
return $images; |
} |
|
private function construireRequeteChemin($mots_cles, $operateur = 'AND') { |
$requete = "SELECT * FROM cel_images_tags_path_liaison "; |
$criteres = array(); |
$premier = array_pop($mots_cles); |
$requete .= 'WHERE (chemin LIKE '.Cel::db()->proteger('%/'.$premier.'/%').' '; |
|
$time_start = microtime(); |
foreach ($mots_cles as $mot_cle) { |
$requete .= " ".$operateur." id_image IN (SELECT id_image ". |
"FROM cel_images_tags_path_liaison ". |
"WHERE chemin LIKE ".Cel::db()->proteger('%/'.$mot_cle.'/%')." ". |
") "; |
} |
$requete .= ")"; |
$images = Cel::db()->requeter($requete); |
return $images; |
} |
|
private function obtenirMotsClesOriginaux($mode) { |
|
$requete_arbre = "SELECT * FROM cel_mots_cles_obs_srv ORDER BY id_utilisateur, niveau"; |
if($mode == 'obs') { |
$requete_arbre = "SELECT id_mot_cle_obs as id_mot_cle, ce_mot_cle_obs_parent as parent, id_utilisateur, mot_cle ". |
"FROM cel_mots_cles_obs_srv ORDER BY id_utilisateur, niveau"; |
} else { |
$requete_arbre = "SELECT id_mot_cle_image as id_mot_cle, ce_mot_cle_image_parent as parent, id_utilisateur, mot_cle ". |
"FROM cel_mots_cles_images_srv ORDER BY id_utilisateur, niveau"; |
} |
|
$mots_cles = Cel::db()->requeter($requete_arbre); |
|
$requete_liaisons = "SELECT comc.id_observation, comc.id_mot_cle_obs, o.ce_utilisateur as id_utilisateur ". |
"FROM cel_obs_mots_cles comc ". |
"INNER JOIN cel_obs o ON o.id_observation = comc.id_observation "; |
|
$liaisons = Cel::db()->requeter($requete_liaisons); |
|
$mots_cles_hierarchiques = array(); |
$correspondances_id_mots_cles_obs = array(); |
|
$rien = 0; |
|
foreach($mots_cles as &$mot) { |
|
$chemin = '/'; |
if(isset($mots_cles_hierarchiques[$mot['ce_mot_cle_obs_parent']])) { |
$chemin = $mots_cles_hierarchiques[$mot['ce_mot_cle_obs_parent']]['chemin']; |
|
if(!empty($mot['mot_cle'])) { |
$chemin = '/'; |
$cle_parent = $mot['parent'].'-'.$mot['id_utilisateur']; |
if(isset($mots_cles_hierarchiques[$cle_parent])) { |
$chemin = $mots_cles_hierarchiques[$cle_parent]['chemin']; |
} |
$chemin .= self::simplifier($mot['mot_cle']).'/'; |
$chemin = str_replace("//", "/", $chemin); |
|
$cle = $mot['id_mot_cle'].'-'.$mot['id_utilisateur']; |
|
$mots_cles_hierarchiques[$cle] = array( |
'id_utilisateur' => $mot['id_utilisateur'], |
'chemin' => $chemin, |
'tag' => $mot['mot_cle'] |
); |
} |
$chemin .= self::simplifier($mot['mot_cle']).'/'; |
$chemin = str_replace("//", "", $chemin); |
|
$cle = $mot['id_mot_cle_obs'].'-'.$mot['id_utilisateur']; |
|
$mots_cles_hierarchiques[$cle] = array( |
'id_utilisateur' => $mot['id_utilisateur'], |
'chemin' => $chemin, |
'tag' => $mot['mot_cle'] |
); |
} |
|
$time_end = microtime(); |
$time = $time_end - $time_start; |
return $mots_cles_hierarchiques; |
} |
|
private function obtenirLiaisonsMotsClesOriginaux($mode) { |
if($mode == 'obs') { |
$requete_liaisons = "SELECT comc.id_observation as id_element_lie, comc.id_mot_cle_obs as id_mot_cle, id_utilisateur as id_utilisateur ". |
"FROM cel_obs_mots_cles_srv comc "; |
//"INNER JOIN cel_obs o ON o.id_observation = comc.id_observation "; |
} else { |
$requete_liaisons = "SELECT comc.id_image as id_element_lie, comc.id_mot_cle_image as id_mot_cle, id_utilisateur as id_utilisateur ". |
"FROM cel_images_mots_cles_srv comc "; |
//"INNER JOIN cel_images o ON o.id_image = comc.id_image "; |
} |
|
$liaisons = Cel::db()->requeter($requete_liaisons); |
return $liaisons; |
} |
|
public function convertirNestedSetVersPathEnum() { |
|
$mots_cles_hierarchiques = $this->obtenirMotsClesOriginaux($this->mode); |
$liaisons = $this->obtenirLiaisonsMotsClesOriginaux($this->mode); |
|
/*foreach($mots_cles_hierarchiques as $mot) { |
echo $mot['chemin'].'<br />'; |
}*/ |
//echo '<pre>'.print_r($liaisons,true).'</pre>'; |
//exit; |
|
$time_start = microtime(); |
|
$mots_cles_slice = array_chunk($mots_cles_hierarchiques, 800, true); |
foreach($mots_cles_slice as $mots_cles_tranche) { |
$requete_insertion_mots_cles = "INSERT INTO ".$this->table_mots_cles." (chemin, id_utilisateur, tag) ". |
88,18 → 180,20 |
cel::db()->executer($requete_insertion_mots_cles); |
} |
|
$gne = 0; |
$liaisons_slice = array_chunk($liaisons, 800, true); |
foreach($liaisons_slice as &$liaison_tranche) { |
$requete_insertion_liaisons = "INSERT INTO ".$this->table_liaison." (id_obs, chemin, id_utilisateur) ". |
$requete_insertion_liaisons = "INSERT INTO ".$this->table_liaison." (".$this->champ_id_element_lie.", chemin, tag, id_utilisateur) ". |
" VALUES "; |
$lier = false; |
|
foreach($liaison_tranche as &$liaison_obs_mot_cle) { |
$cle = $liaison_obs_mot_cle['id_mot_cle_obs'].'-'.$liaison_obs_mot_cle['id_utilisateur']; |
$cle = $liaison_obs_mot_cle['id_mot_cle'].'-'.$liaison_obs_mot_cle['id_utilisateur']; |
if(isset($mots_cles_hierarchiques[$cle])) { |
$requete_insertion_liaisons .= "(". |
cel::db()->proteger($liaison_obs_mot_cle['id_observation']).", ". |
cel::db()->proteger($liaison_obs_mot_cle['id_element_lie']).", ". |
cel::db()->proteger($mots_cles_hierarchiques[$cle]['chemin']).", ". |
cel::db()->proteger($mots_cles_hierarchiques[$cle]['tag']).", ". |
cel::db()->proteger($liaison_obs_mot_cle['id_utilisateur'])." ". |
"), "; |
$lier = true; |
112,7 → 206,8 |
} |
} |
|
echo "La génération d'un tableau de ".count($mots_cles_hierarchiques).' éléments a pris '.$time.' secondes'; |
$time = microtime() - $time_start; |
echo "La génération d'un tableau de ".count($mots_cles_hierarchiques).' éléments et de '.count($liaisons).' liaisons a pris '.$time.' secondes'; |
exit; |
} |
|
169,13 → 264,13 |
$sous_requete_chemin = "(SELECT chemin FROM ".$this->table_mots_cles." ". |
"WHERE id_tag = ".Cel::db()->proteger($id_mot_cle).") "; |
|
$requete = "INSERT INTO ".$this->table_liaison." (id_obs, chemin, id_utilisateur) ". |
$requete = "INSERT INTO ".$this->table_liaison." (".$this->champ_id_element_lie.", chemin, id_utilisateur) ". |
"VALUES (". |
Cel::db()->proteger($id_observation).", ". |
$sous_requete_chemin.",". |
Cel::db()->proteger($id_utilisateur)." ". |
") ". |
"ON DUPLICATE KEY UPDATE id_obs = id_obs; "; |
"ON DUPLICATE KEY UPDATE ".$this->champ_id_element_lie." = ".$this->champ_id_element_lie." "; |
|
$liaison = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__); |
|
190,7 → 285,7 |
Cel::db()->proteger($sous_requete_chemin).", ". |
Cel::db()->proteger($id_utilisateur)." ". |
") ". |
"ON DUPLICATE KEY UPDATE id_obs = id_obs; "; |
"ON DUPLICATE KEY UPDATE ".$this->champ_id_element_lie." = ".$this->champ_id_element_lie." "; |
|
$liaison = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__); |
|