--- a/hdalab/filter.php Fri Feb 24 18:38:00 2012 +0100
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,202 +0,0 @@
-<?php
-header('Content-type: application/json');
-
-require('connect.inc.php');
-
-$periode = (isset($_REQUEST['period']) ? pg_escape_string($_REQUEST['period']) : NULL);
-$pays = (isset($_REQUEST['country']) ? pg_escape_string($_REQUEST['country']) : NULL);
-$label = (isset($_REQUEST['label']) ? pg_escape_string($_REQUEST['label']) : NULL);
-$contentlist = (isset($_REQUEST['contentlist']) ? pg_escape_string($_REQUEST['contentlist']) : NULL);
-$max_tag_order = (isset($_REQUEST['mto']) ? pg_escape_string($_REQUEST['mto']) : 12);
-$content_count = (isset($_REQUEST['contentcount']) ? pg_escape_string($_REQUEST['contentcount']) : 12);
-$tag_count = (isset($_REQUEST['tagcount']) ? pg_escape_string($_REQUEST['tagcount']) : 30);
-$cont_count = 0;
-
-if ($label or $pays or $periode or gettype($contentlist) != "NULL") {
- $globalsql = "SELECT id, title, description, url, latitude, longitude FROM hdabo_datasheet E LEFT JOIN hdaviz_datasheet_insee K ON E.id = K.datasheet_id LEFT JOIN hdaviz_insee_coords Q ON K.insee_code = Q.code_insee";
- $globalids = array();
- $globalfilters = array("validated=TRUE");
- if ($periode) {
- $years = split(',',$periode);
- $start_year = $years[0];
- $end_year = (count($years) > 1 ? $years[1] : $start_year);
- $delta = max( 1, floor( ($end_year - $start_year ) / 2 ) );
- $minstart = $start_year - $delta;
- $maxend = $end_year + $delta;
- $rq = pg_query("SELECT tag_id FROM hdaviz_years WHERE end_year >= $start_year AND start_year <= $end_year AND end_year <= $maxend AND start_year >= $minstart");
- $ids = array();
- while($ligne = pg_fetch_row($rq)) {
- $ids[] = $ligne[0];
- }
- pg_free_result($rq);
- $globalids = $ids;
- $globalfilters[] = "EXISTS (SELECT * FROM hdabo_taggedsheet F WHERE F.tag_id IN (" . join(",", $ids) . ") AND F.order <= $max_tag_order AND F.datasheet_id = E.id)";
- }
- if ($pays) {
- $listepays = split(',',$pays);
- foreach ($listepays as $unpays) {
- $rq = pg_query("SELECT id FROM hdaviz_inclusiongeo WHERE isocode='$unpays'");
- $ids = array();
- while($ligne = pg_fetch_row($rq)) {
- $ids[] = $ligne[0];
- }
- pg_free_result($rq);
- $globalids = array_merge($globalids,$ids);
- $globalfilters[] = "EXISTS (SELECT * FROM hdabo_taggedsheet G WHERE G.tag_id IN (" . join(",", $ids) . ") AND G.order <= $max_tag_order AND G.datasheet_id = E.id)";
- }
- }
- if ($label) {
- $lblist = split(',',$label);
- foreach ($lblist as $txtlbl) {
- $rq = pg_query("SELECT id FROM hdabo_tag WHERE label ILIKE '$txtlbl'");
- $ids = array();
- while($ligne = pg_fetch_row($rq)) {
- $ids[] = $ligne[0];
- }
- pg_free_result($rq);
- $globalids = array_merge($globalids,$ids);
- $globalfilters[] = "EXISTS (SELECT * FROM hdabo_taggedsheet G WHERE G.tag_id IN (" . join(",", $ids) . ") AND G.order <= $max_tag_order AND G.datasheet_id = E.id)";
- }
- }
- if (gettype($contentlist) != "NULL") {
- if ($contentlist) {
- $globalfilters[] = "id IN ($contentlist)";
- } else {
- $globalfilters[] = "id IS NULL";
- }
- }
- $globalsql = $globalsql.(count($globalfilters) ? " WHERE ".join($globalfilters," AND ") : "" );
- if (!$label && !$periode && gettype($contentlist)=="NULL") {
- $globalsql = $globalsql." ORDER BY RANDOM()";
- }
- $rq = pg_query($globalsql);
- $contenus = array();
- while($ligne = pg_fetch_assoc($rq)) {
- $cont_count++;
- $ligne["score"] = 0;
- $ligne["tags"] = array();
- $contenus[$ligne["id"]] = $ligne;
- }
- pg_free_result($rq);
- $rq = pg_query("SELECT DISTINCT label FROM hdabo_tag WHERE category_id=5");
- $disciplines = array();
- while($ligne = pg_fetch_row($rq)) {
- $disciplines[$ligne[0]] = array("label" => $ligne[0], "score" => 0);
- }
- pg_free_result($rq);
- $tags = array();
- $contentids = join(",",array_keys($contenus));
- if ($contentids) {
- $rq = pg_query("SELECT L.datasheet_id, L.tag_id, M.label, L.order, M.category_id FROM hdabo_taggedsheet L, hdabo_tag M WHERE L.datasheet_id IN ($contentids) AND L.tag_id = M.id AND L.order <= $max_tag_order ORDER BY L.order ASC");
- while($ligne = pg_fetch_row($rq)) {
- $match_tag = in_array($ligne[1], $globalids);
- $contenus[$ligne[0]]["tags"][] = array("id" => $ligne[1], "label" => $ligne[2], "order" => $ligne[3], "match" => $match_tag);
- $tagscore = 2*$max_tag_order-$ligne[3];
- if (!in_array($ligne[4], array(1,2,5))) {
- if (!isset($tags[$ligne[1]])) {
- $tags[$ligne[1]] = array("id" => $ligne[1], "label" => $ligne[2], "score" => 0);
- }
- $tags[$ligne[1]]["score"] += $tagscore;
- if ($match_tag) {
- $tags[$ligne[1]]["match"] = True;
- }
- }
- if ($match_tag) {
- $contenus[$ligne[0]]["score"] += $tagscore;
- }
-/* if (isset($countries[$ligne[2]])) {
- $countries[$ligne[2]]["score"] += $tagscore;
- }
- */
- if (isset($disciplines[$ligne[2]])) {
- $disciplines[$ligne[2]]["score"] += $tagscore;
- }
- }
- pg_free_result($rq);
- }
- function triscore($a, $b) {
- return $b["score"] - $a["score"];
- }
- if (!$contentlist) {
- usort($contenus,"triscore");
- $contenus = array_slice($contenus, 0, $content_count);
- } else {
- $contenus = array_values($contenus);
- }
- usort($tags,"triscore");
- usort($disciplines,"triscore");
-/* $countries = array_values($countries);
- */
- $tags = array_slice($tags, 0, $tag_count);
- $disciplines = array_slice($disciplines, 0, 10);
- $years = array();
- $countries = array();
- if ($contentids) {
- $rq = pg_query("SELECT U.start_year, U.end_year, COUNT(*) score FROM hdaviz_years U, hdabo_taggedsheet V WHERE U.tag_id = V.tag_id AND V.order <= $max_tag_order AND V.datasheet_id IN ($contentids) GROUP BY U.start_year, U.end_year");
- while($ligne = pg_fetch_row($rq)) {
- foreach(range($ligne[0], $ligne[1]) as $year) {
- $years[$year] = $ligne[2] + ( isset($years[$year]) ? $years[$year] : 0 );
- }
- }
- pg_free_result($rq);
- $rq = pg_query("SELECT U.isocode, COUNT(*) FROM hdaviz_inclusiongeo U, hdabo_taggedsheet V WHERE U.id = V.tag_id AND V.order <= $max_tag_order AND V.datasheet_id IN ($contentids) GROUP BY U.isocode");
- while($ligne = pg_fetch_row($rq)) {
- $countries[$ligne[0]] = $ligne[1];
- }
- pg_free_result($rq);
- }
-} else {
- $rq = pg_query("SELECT id, title, description, url, latitude, longitude FROM hdabo_datasheet E LEFT JOIN hdaviz_datasheet_insee K ON E.id = K.datasheet_id LEFT JOIN hdaviz_insee_coords Q ON K.insee_code = Q.code_insee WHERE validated=TRUE ORDER BY RANDOM() LIMIT $content_count");
- while($ligne = pg_fetch_assoc($rq)) {
- $ligne["tags"] = array();
- $contenus[$ligne["id"]] = $ligne;
- }
- $rq = pg_query("SELECT COUNT(*) FROM hdabo_datasheet WHERE validated=TRUE");
- $ligne = pg_fetch_row($rq);
- $cont_count = $ligne[0];
- pg_free_result($rq);
- $rq = pg_query("SELECT L.datasheet_id, L.tag_id, M.label, L.order FROM hdabo_taggedsheet L, hdabo_tag M WHERE L.datasheet_id IN (".join(",",array_keys($contenus)).") AND L.tag_id = M.id AND L.order <= $max_tag_order ORDER BY L.order ASC");
- while($ligne = pg_fetch_row($rq)) {
- $contenus[$ligne[0]]["tags"][] = array("id" => $ligne[1], "label" => $ligne[2], "order" => $ligne[3]);
- }
- pg_free_result($rq);
- $contenus = array_values($contenus);
-
- $rq = pg_query("SELECT label, SUM(".(2*$max_tag_order)." - V.order) score FROM hdabo_tag U, hdabo_taggedsheet V WHERE U.id = V.tag_id AND V.order <= $max_tag_order AND category_id NOT IN (1,2,5) AND EXISTS (SELECT * FROM hdabo_datasheet W WHERE V.datasheet_id = W.id AND W.validated=TRUE) GROUP BY label ORDER BY score DESC LIMIT $tag_count");
- $tags = pg_fetch_all($rq);
- pg_free_result($rq);
-
- $rq = pg_query("SELECT A.label, COUNT(*) score FROM hdabo_tag A, hdabo_taggedsheet B WHERE A.id = B.tag_id AND B.order <= $max_tag_order AND A.category_id=5 AND EXISTS (SELECT * FROM hdabo_datasheet W WHERE B.datasheet_id = W.id AND W.validated=TRUE) GROUP BY A.label ORDER BY score DESC LIMIT 10");
- $disciplines = pg_fetch_all($rq);
- pg_free_result($rq);
-
- $rq = pg_query("SELECT U.start_year, U.end_year, COUNT(*) FROM hdaviz_years U, hdabo_taggedsheet V WHERE U.tag_id = V.tag_id AND V.order <= $max_tag_order AND EXISTS (SELECT * FROM hdabo_datasheet W WHERE V.datasheet_id = W.id AND W.validated=TRUE) GROUP BY U.start_year, U.end_year");
- $years = array();
- while($ligne = pg_fetch_row($rq)) {
- foreach(range($ligne[0], $ligne[1]) as $year) {
- $years[$year] = $ligne[2] + ( isset($years[$year]) ? $years[$year] : 0 );
- }
- }
- pg_free_result($rq);
- $countries = array();
- $rq = pg_query("SELECT U.isocode, COUNT(*) FROM hdaviz_inclusiongeo U, hdabo_taggedsheet V WHERE U.id = V.tag_id AND V.order <= $max_tag_order AND EXISTS (SELECT * FROM hdabo_datasheet W WHERE V.datasheet_id = W.id AND W.validated=TRUE) GROUP BY U.isocode");
- while($ligne = pg_fetch_row($rq)) {
- $countries[$ligne[0]] = $ligne[1];
- }
- pg_free_result($rq);
-}
-ksort($years);
-$yearchange = array();
-foreach($years as $year => $score) {
- if ($year < 2011) {
- if (!isset($years[$year-1]) || $years[$year-1] != $score) {
- $yearchange[] = array('year' => $year, 'score' => $score);
- }
- if (!isset($years[$year+1]) && ($year != -1)) {
- $yearchange[] = array('year' => $year + 1, 'score' => 0);
- }
- }
-}
-$output = array("count" => $cont_count, "contents" => $contenus, "tags" => $tags, "sparkline" => $yearchange, "countries" => $countries, "disciplines" => $disciplines);
-echo json_encode($output);
-?>
\ No newline at end of file