Subversion Repositories Applications.papyrus

<?php

/*

 * Moteur de recherche SPHINX

 * @author        Raphaël Droz <raphael@tela-botanica.org

 * @copyright     Tela-Botanica 2013

 */

/*

  sudo urpmi lib64sphinxclient-devel

  sudo pecl install sphinx

  see also: http://www.ibm.com/developerworks/library/os-sphinx/

  see also: http://sphinxsearch.com/docs/manual-2.0.7.html#extended-syntax

  TODO: http://sphinxsearch.com/blog/2010/08/17/how-sphinx-relevance-ranking-works/

  TODO: attention, projet,bazaar,spip et papyrus sont latin9, ainsi en est-il de l'input attendu du <form>

  		mais pour coste... c'est full utf-8

*/

/* returned struct:

'poids' => 0,

'url' => '',

'titre' => '',

'hreflang' => '',

'accesskey' => '',

'title' => '', // balise 'title'

'date_creation' => '',

'description' => ''

*/

define('SPHINX_DSN', '127.0.0.1:9306');

define('_MRS_SPHINX_BASEHOST', $_SERVER['HTTP_HOST']);

// pour strftime()

date_default_timezone_set('Europe/Paris');

setlocale(LC_TIME, 'fr_FR');

class MoteurRecherche_SPIP {

	public function get($ids, $q = NULL) {

		if(!$ids) return array();

		$db = DB::connect($GLOBALS['_MOTEUR_RECHERCHE_']['spip'][0]['bdd_dsn']);

		$req = $db->query(sprintf(<<<EOF

SELECT id_article AS id, titre, texte, date AS date_creation, lang as hreflang

FROM spip_articles

WHERE statut = "%s"

AND id_article IN (%s)

EOF

								  ,

								  "publie",

								  implode(',', $ids)));

		(DB::isError($req)) ? die($req->getMessage()) : '';

		$content = array();

		while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {

			$rec['url_simple'] = sprintf("%s/article%d.html",

										 trim($GLOBALS['_MOTEUR_RECHERCHE_']['spip'][0]['url'], '/'),

										 $rec['id']);

			$rec['url'] = sprintf("%s?var_recherche=%s",

								  $rec['url_simple'],

								  More_Recherche::traiterMotif($q, 'url'));

			$rec['description'] = More_Recherche::couperTexte($rec['texte'], MORE_RESULTAT_TAILLE_DESCRIPTION);

			unset($rec['texte']);

			$content[$rec['id']] = $rec;

		}

		return $content;

	}

}

class MoteurRecherche_BAZAR {

	public function get($ids, $q = NULL) {

		if(!$ids) return array();

		$db = DB::connect($GLOBALS['_MOTEUR_RECHERCHE_']['bazar'][0]['bdd_dsn']);

		$req = $db->query(sprintf(<<<EOF

SELECT bf_id_fiche AS id,

	   bf_description AS texte,

	   bf_titre AS titre,

	   bf_date_debut_evenement AS date_creation

FROM bazar_fiche

WHERE bf_id_fiche IN (%s)

EOF

								  ,

								  implode(',', $ids)));

		(DB::isError($req)) ? die($req->getMessage()) : '';

		$content = array();

		while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {

			$rec['url_simple'] = $rec['url'] = sprintf(trim($GLOBALS['_MOTEUR_RECHERCHE_']['bazar'][0]['url'], '/'), $rec['id']);

			$rec['description'] = More_Recherche::couperTexte($rec['texte'], MORE_RESULTAT_TAILLE_DESCRIPTION);

			unset($rec['texte']);

			$content[$rec['id']] = $rec;

		}

		return $content;

	}

}

class MoteurRecherche_PROJET {

	public function get($ids, $q = NULL) {

		if(!$ids) return array();

		$db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];

		$req = $db->query(sprintf(<<<EOF

SELECT p_id AS id, p_titre AS titre, p_description, p_date_creation AS date_creation

FROM projet WHERE p_id IN (%s)

EOF

								  ,

								  implode(',', $ids)));

		(DB::isError($req)) ? die($req->getMessage()) : '';

		$content = array();

		while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {

			$rec['url_simple'] = $rec['url'] = sprintf("%s?id_projet=%d",

													   trim($GLOBALS['_MOTEUR_RECHERCHE_']['projet'][0]['url'], '/'),

													   $rec['id']);

			$rec['description'] = substr(strip_tags($rec['p_description']), 0, 400 + 2 * MORE_RESULTAT_TAILLE_DESCRIPTION);

			unset($rec['p_description']);

			$content[$rec['id']] = $rec;

		}

		return $content;

	}

}

class MoteurRecherche_PAPYRUS {

	public function get($ids, $q = NULL) {

		if(!$ids) return array();

        $db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];

		$req = $db->query(sprintf(<<<EOF

SELECT mc.gmc_ce_menu AS id,

	   IF(gm_nom != '', gm_nom, IF(gm_titre != '', gm_titre, gm_titre_alternatif)) AS titre,

	   gmc_contenu AS texte,

	   gm_description_libre, gm_description_resume,

	   gm_mots_cles,gm_source, gm_auteur, gm_contributeur, gm_editeur, gm_categorie, gm_date_creation AS date_creation

FROM gen_menu m

LEFT JOIN gen_menu_contenu mc ON mc.gmc_ce_menu = m.gm_id_menu AND mc.gmc_bool_dernier = 1

WHERE mc.gmc_ce_menu IN (%s)

EOF

								  ,

								  implode(',', $ids)));

		(DB::isError($req)) ? die($req->getMessage()) : '';

		$content = array();

		while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {

			// Création de l'url

			// TODO : utiliser comme pour spip un fichier de config spécifique pour virer PAP_URL d'ici

			$une_url = new Pap_URL(PAP_URL);

			$une_url->setId($rec['id']);

			$rec['url_simple'] = $une_url->getURL();

			$une_url->addQueryString('var_recherche', More_Recherche::traiterMotif($q, 'url'), true);

			$rec['url'] = $une_url->getURL();

			$rec['description'] =  htmlentities($rec['gm_description_resume']);

			unset($rec['gm_description_resume']);

			$content[$rec['id']] = $rec;

		}

		return $content;

	}

}

class MoteurRecherche_COSTE {

	public function get($ids, $q = NULL) {

		if(!$ids) return array();

		// DB access is dumb, let's use this one and pray

		$db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];

		$req = $db->query(sprintf(<<<EOF

SELECT c.flore_bdtfx_nn AS id, c.nom_sci AS titre, dsc.body AS description

FROM tb_eflore.coste_v2_00 c

LEFT JOIN tela_prod_wikini.florecoste_pages dsc ON c.page_wiki_dsc = dsc.tag AND dsc.latest = 'Y'

WHERE c.flore_bdtfx_nn IN (%s)

EOF

								  ,

								  implode(',', $ids)));

		(DB::isError($req)) ? die($req->getMessage()) : '';

		$content = array();

		while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {

			$rec['url_simple'] = $rec['url'] = sprintf("http://%s/bdtfx-nn-%d", _MRS_SPHINX_BASEHOST, $rec['id']);

			// TODO: interpret wikini

			$rec['description'] = substr($rec['description'], 0, 400 + 2 * MORE_RESULTAT_TAILLE_DESCRIPTION);

			$content[$rec['id']] = $rec;

		}

		return $content;

	}

}

class MoteurRecherche_NVJFL {

	public function get($ids, $q = NULL) {

		if(!$ids) return array();

		// DB access is dumb, let's use this one and pray

		$db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];

		$req = $db->query(sprintf(<<<EOF

SELECT b.num_nom AS id, CONCAT(nom_sci, ' (nn: ', b.num_nom, ', nt: ', num_taxonomique, ')') AS titre, GROUP_CONCAT(n.nom_vernaculaire) AS description

FROM tb_eflore.bdtfx_v1_01 b

LEFT JOIN tb_eflore.nvjfl_v2007 n ON n.num_taxon = b.num_taxonomique

WHERE b.num_nom IN (%s)

GROUP BY n.num_taxon

EOF

								  ,

								  implode(',', $ids)));

		(DB::isError($req)) ? die($req->getMessage()) : '';

		$content = array();

		while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {

			$rec['url_simple'] = $rec['url'] = sprintf("http://%s/bdtfx-nn-%d", _MRS_SPHINX_BASEHOST, $rec['id']);

			$rec['description'] = substr($rec['description'], 0, 400 + 2 * MORE_RESULTAT_TAILLE_DESCRIPTION);

			$content[$rec['id']] = $rec;

		}

		return $content;

	}

}

// http://stackoverflow.com/questions/348410/sort-an-array-based-on-another-array

function _sortArrayByArray($array, $orderArray) {

	$ordered = array();

	foreach($orderArray as $key) {

		if(array_key_exists($key, $array)) {

			$ordered[$key] = $array[$key];

			unset($array[$key]);

		}

	}

	return $ordered + $array;

}

// adaption aux templates existants: [score] => [weight]

function _weight2score(&$item, $key, $max) {

	$item['score'] = intval($item['weight'] / $max * 100);

	$item['date_creation'] = isset($item['date_creation']) ? strftime("%d %B %Y", strtotime($item['date_creation'])) : '';

	unset($item['weight']);

}

function _actuNewerFirst($a,$b) {

	return isset($a['date_creation']) && isset($b['date_creation']) ? strcmp($b['date_creation'], $a['date_creation']) : 0;

}

function sphinx_search($q = NULL, $page = 1) {

	if(!$q) return array();

	$db = mysql_connect(SPHINX_DSN, NULL, NULL, TRUE);

	$req = mysql_query(sprintf("SELECT group_id, main_id FROM i_projet, i_spip, i_papyrus, i_bazar, i_coste, i_nvjfl WHERE MATCH('%s') LIMIT 50", $q), $db);

	$res = array('spip' => array(), 'bazar' => array(), 'projet' => array(), 'papyrus' => array(), 'coste' => array(), 'nvjfl' => array());

	$ids_par_poids = array();

	while($rec = mysql_fetch_array($req, MYSQL_ASSOC)) {

		$res[$rec['group_id']][$rec['id']] = $rec;

		$ids_par_poids[] = $rec['main_id'];

	}

	$docs = array();

	// spip

	$spip = new MoteurRecherche_SPIP();

	foreach($spip->get(array_filter(array_keys($res['spip']), 'intval'), $q) as $v) {

		unset($res['spip'][$v['id']]['group_id'],

			  $res['spip'][$v['id']]['main_id'],

			  $res['spip'][$v['id']]['id']);

		// left: weight

		$docs['spip-' . $v['id']] = array_merge($v,	$res['spip'][$v['id']]);

	}

	// bazar

	$bazar = new MoteurRecherche_BAZAR();

	foreach($bazar->get(array_filter(array_keys($res['bazar']), 'intval'), $q) as $v) {

		unset($res['bazar'][$v['id']]['group_id'],

			  $res['bazar'][$v['id']]['main_id'],

			  $res['bazar'][$v['id']]['id']);

		// left: weight

		$docs['bazar-' . $v['id']] = array_merge($v, $res['bazar'][$v['id']]);

	}

	// projet

	$projet = new MoteurRecherche_PROJET();

	foreach($projet->get(array_filter(array_keys($res['projet']), 'intval'), $q) as $v) {

		unset($res['projet'][$v['id']]['group_id'],

			  $res['projet'][$v['id']]['main_id'],

			  $res['projet'][$v['id']]['id']);

		// left: weight

		$docs['projet-' . $v['id']] = array_merge($v, $res['projet'][$v['id']]);

	}

	// papyrus

	$papyrus = new MoteurRecherche_PAPYRUS();

	foreach($papyrus->get(array_filter(array_keys($res['papyrus']), 'intval'), $q) as $v) {

		unset($res['papyrus'][$v['id']]['group_id'],

			  $res['papyrus'][$v['id']]['main_id'],

			  $res['papyrus'][$v['id']]['id']);

		// left: weight

		$docs['papyrus-' . $v['id']] = array_merge($v, $res['papyrus'][$v['id']]);

	}

	// coste

	$coste = new MoteurRecherche_COSTE();

	foreach($coste->get(array_filter(array_keys($res['coste']), 'intval'), $q) as $v) {

		unset($res['coste'][$v['id']]['group_id'],

			  $res['coste'][$v['id']]['main_id'],

			  $res['coste'][$v['id']]['id']);

		// left: weight

		$docs['coste-' . $v['id']] = array_merge($v, $res['coste'][$v['id']]);

	}

	// nvjfl

	$nvjfl = new MoteurRecherche_NVJFL();

	foreach($nvjfl->get(array_filter(array_keys($res['nvjfl']), 'intval'), $q) as $v) {

		unset($res['nvjfl'][$v['id']]['group_id'],

			  $res['nvjfl'][$v['id']]['main_id'],

			  $res['nvjfl'][$v['id']]['id']);

		// left: weight

		$docs['nvjfl-' . $v['id']] = array_merge($v, $res['nvjfl'][$v['id']]);

	}

	// sort

	$sorted = _sortArrayByArray($docs, $ids_par_poids);

	$max = current($sorted);

	$max = $max['weight'];

	if(isset($_GET['tri']) && $_GET['tri'] == 'date')

		usort($sorted, '_actuNewerFirst');

	// transforme les clefs pour s'adapter aux templates existants

	array_walk($sorted, '_weight2score', $max);

	// var_dump($sorted);die;

	return $sorted;

}

/*

// http://www.php.net/manual/fr/sphinx.examples.php

$s = new SphinxClient;

$s->setServer("localhost", 9306);

$s->setMatchMode(SPH_MATCH_ANY);

$s->setMaxQueryTime(3);

var_dump($s->query("test"));

*/

/*

  Note: conversion côté client SQL:

  mysql -h0 -P 9306 < <(iconv -f utf8 -t latin1 <<<"SELECT * FROM i_projet, i_spip, i_papyrus, i_bazar WHERE MATCH('journée');")

*/

/*

  // test sorting

  // uncomment this:

  print_r(array_keys($sorted); die;

  // then:

  ddiff

	<(mysql -h0 -P 9306 <<<"SELECT main_id FROM i_projet, i_spip, i_papyrus, i_bazar, i_coste, i_nvjfl WHERE MATCH('test') LIMIT 50;"|awk '{print $3}'|sed 1d) \

	<(POST http://localhost/site:reseau<<<"more_motif=test&"|awk -F'=>' '{print $2}'|sed -e 's/ //g' -e '/^$/d')

  // both should be equal.

  // [ SELECT main_id, group_id FROM i_projet, i_spip, i_papyrus, i_bazar WHERE MATCH('test') LIMIT 50; ]

*/