diff -r dc6c3ac62efa -r fdf808d7d374 hdalab/filter.php
--- a/hdalab/filter.php	Thu Feb 02 12:03:07 2012 +0100
+++ b/hdalab/filter.php	Mon Feb 06 18:50:19 2012 +0100
@@ -4,6 +4,7 @@
 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);
@@ -11,10 +12,10 @@
 $tag_count = (isset($_REQUEST['tagcount']) ? pg_escape_string($_REQUEST['tagcount']) : 30);
 $cont_count = 0;
 
-if ($label or $periode or gettype($contentlist) != "NULL") {
-    $globalsql = "SELECT id, title, description, url FROM hdabo_datasheet E";
+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();
+    $globalfilters = array("validated=TRUE");
     if ($periode) {
         $years = split(',',$periode);
         $start_year = $years[0];
@@ -31,6 +32,19 @@
         $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) {
@@ -64,12 +78,6 @@
         $contenus[$ligne["id"]] = $ligne;
     }
     pg_free_result($rq);
-    $rq = pg_query("SELECT label, isocode FROM hdaviz_cc");
-    $countries = array();
-    while($ligne = pg_fetch_row($rq)) {
-        $countries[$ligne[0]] = array("isocode" => $ligne[1], "score" => 0);
-    }
-    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)) {
@@ -79,22 +87,27 @@
     $tags = array();
     $contentids = join(",",array_keys($contenus));
     if ($contentids) {
-        $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 ($contentids) AND L.tag_id = M.id AND L.order <= $max_tag_order ORDER BY L.order ASC");
+        $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 (!isset($tags[$ligne[1]])) {
-                $tags[$ligne[1]] = array("id" => $ligne[1], "label" => $ligne[2], "score" => 0);
+            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;
+                }
             }
-            $tags[$ligne[1]]["score"] += $tagscore;
             if ($match_tag) {
                 $contenus[$ligne[0]]["score"] += $tagscore;
-                $tags[$ligne[1]]["match"] = True;
             }
-            if (isset($countries[$ligne[2]])) {
+/*            if (isset($countries[$ligne[2]])) {
                 $countries[$ligne[2]]["score"] += $tagscore;
             }
+ */
             if (isset($disciplines[$ligne[2]])) {
                 $disciplines[$ligne[2]]["score"] += $tagscore;
             }
@@ -112,26 +125,33 @@
     }
     usort($tags,"triscore");
     usort($disciplines,"triscore");
-    $countries = array_values($countries);
+/*    $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, SUM(".(2*$max_tag_order)." - V.order)/(U.end_year + 1 - U.start_year) 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");
+        $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 FROM hdabo_datasheet ORDER BY RANDOM() LIMIT $content_count");
+    $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");
+    $rq = pg_query("SELECT COUNT(*) FROM hdabo_datasheet WHERE validated=TRUE");
     $ligne = pg_fetch_row($rq);
     $cont_count = $ligne[0];
     pg_free_result($rq);
@@ -141,16 +161,16 @@
     }
     pg_free_result($rq);
     $contenus = array_values($contenus);
-    $rq = pg_query("SELECT hdabo_tag.id, label, SUM(".(2*$max_tag_order)." - hdabo_taggedsheet.order) score FROM hdabo_tag, hdabo_taggedsheet WHERE hdabo_tag.id = hdabo_taggedsheet.tag_id AND hdabo_taggedsheet.order <= $max_tag_order GROUP BY hdabo_tag.id, label ORDER BY score DESC LIMIT $tag_count");
+    
+    $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 C.isocode, SUM(".(2*$max_tag_order)." - B.order) score FROM hdabo_tag A, hdabo_taggedsheet B, hdaviz_cc C WHERE A.id = B.tag_id AND B.order <= $max_tag_order AND A.label = C.label GROUP BY C.isocode");
-    $countries = pg_fetch_all($rq);
-    pg_free_result($rq);
-    $rq = pg_query("SELECT A.label, SUM(".(2*$max_tag_order)." - B.order) 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 GROUP BY A.label ORDER BY score DESC LIMIT 10");
+    
+    $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, SUM(".(2*$max_tag_order)." - hdabo_taggedsheet.order)/(U.end_year + 1 - U.start_year) score FROM hdaviz_years U, hdabo_taggedsheet WHERE U.tag_id = hdabo_taggedsheet.tag_id AND hdabo_taggedsheet.order <= $max_tag_order GROUP BY U.start_year, U.end_year");
+    
+    $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) {
@@ -158,6 +178,12 @@
         }
     }
     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();