1 <?php

     2 header('Content-type: application/json');

     3 

     4 require('connect.inc.php');

     5 

     6 $periode = (isset($_REQUEST['period']) ? pg_escape_string($_REQUEST['period']) : NULL);

     7 $pays = (isset($_REQUEST['country']) ? pg_escape_string($_REQUEST['country']) : NULL);

     8 $label = (isset($_REQUEST['label']) ? pg_escape_string($_REQUEST['label']) : NULL);

     9 $contentlist = (isset($_REQUEST['contentlist']) ? pg_escape_string($_REQUEST['contentlist']) : NULL);

    10 $max_tag_order = (isset($_REQUEST['mto']) ? pg_escape_string($_REQUEST['mto']) : 12);

    11 $content_count = (isset($_REQUEST['contentcount']) ? pg_escape_string($_REQUEST['contentcount']) : 12);

    12 $tag_count = (isset($_REQUEST['tagcount']) ? pg_escape_string($_REQUEST['tagcount']) : 30);

    13 $cont_count = 0;

    14 

    15 if ($label or $pays or $periode or gettype($contentlist) != "NULL") {

    16     $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";

    17     $globalids = array();

    18     $globalfilters = array("validated=TRUE");

    19     if ($periode) {

    20         $years = split(',',$periode);

    21         $start_year = $years[0];

    22         $end_year = (count($years) > 1 ? $years[1] : $start_year);

    23         $delta = max( 1, floor( ($end_year - $start_year ) / 2 ) );

    24         $minstart = $start_year - $delta;

    25         $maxend = $end_year + $delta;

    26         $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");

    27         $ids = array();

    28         while($ligne = pg_fetch_row($rq)) {

    29             $ids[] = $ligne[0];

    30         }

    31         pg_free_result($rq);

    32         $globalids = $ids;

    33         $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)";

    34     }

    35     if ($pays) {

    36         $listepays = split(',',$pays);

    37         foreach ($listepays as $unpays) {

    38             $rq = pg_query("SELECT id FROM hdaviz_inclusiongeo WHERE isocode='$unpays'");

    39             $ids = array();

    40             while($ligne = pg_fetch_row($rq)) {

    41                 $ids[] = $ligne[0];

    42             }

    43             pg_free_result($rq);

    44             $globalids = array_merge($globalids,$ids);

    45             $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)";

    46         }

    47     }

    48     if ($label) {

    49         $lblist = split(',',$label);

    50         foreach ($lblist as $txtlbl) {

    51             $rq = pg_query("SELECT id FROM hdabo_tag WHERE label ILIKE '$txtlbl'");

    52             $ids = array();

    53             while($ligne = pg_fetch_row($rq)) {

    54                 $ids[] = $ligne[0];

    55             }

    56             pg_free_result($rq);

    57             $globalids = array_merge($globalids,$ids);

    58             $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)";

    59         }

    60     }

    61     if (gettype($contentlist) != "NULL") {

    62         if ($contentlist) {

    63             $globalfilters[] = "id IN ($contentlist)";

    64         } else {

    65             $globalfilters[] = "id IS NULL";

    66         }

    67     }

    68     $globalsql = $globalsql.(count($globalfilters) ? " WHERE ".join($globalfilters," AND ") : "" );

    69     if (!$label && !$periode && gettype($contentlist)=="NULL") {

    70         $globalsql = $globalsql." ORDER BY RANDOM()";

    71     }

    72     $rq = pg_query($globalsql);

    73     $contenus = array();

    74     while($ligne = pg_fetch_assoc($rq)) {

    75         $cont_count++;

    76         $ligne["score"] = 0;

    77         $ligne["tags"] = array();

    78         $contenus[$ligne["id"]] = $ligne;

    79     }

    80     pg_free_result($rq);

    81     $rq = pg_query("SELECT DISTINCT label FROM hdabo_tag WHERE category_id=5");

    82     $disciplines = array();

    83     while($ligne = pg_fetch_row($rq)) {

    84         $disciplines[$ligne[0]] = array("label" => $ligne[0], "score" => 0);

    85     }

    86     pg_free_result($rq);

    87     $tags = array();

    88     $contentids = join(",",array_keys($contenus));

    89     if ($contentids) {

    90         $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");

    91         while($ligne = pg_fetch_row($rq)) {

    92             $match_tag = in_array($ligne[1], $globalids);

    93             $contenus[$ligne[0]]["tags"][] = array("id" => $ligne[1], "label" => $ligne[2], "order" => $ligne[3], "match" => $match_tag);

    94             $tagscore = 2*$max_tag_order-$ligne[3];

    95             if (!in_array($ligne[4], array(1,2,5))) {

    96                 if (!isset($tags[$ligne[1]])) {

    97                     $tags[$ligne[1]] = array("id" => $ligne[1], "label" => $ligne[2], "score" => 0);

    98                 }

    99                 $tags[$ligne[1]]["score"] += $tagscore;

   100                 if ($match_tag) {

   101                     $tags[$ligne[1]]["match"] = True;

   102                 }

   103             }

   104             if ($match_tag) {

   105                 $contenus[$ligne[0]]["score"] += $tagscore;

   106             }

   107 /*            if (isset($countries[$ligne[2]])) {

   108                 $countries[$ligne[2]]["score"] += $tagscore;

   109             }

   110  */

   111             if (isset($disciplines[$ligne[2]])) {

   112                 $disciplines[$ligne[2]]["score"] += $tagscore;

   113             }

   114         }

   115         pg_free_result($rq);

   116     }

   117     function triscore($a, $b) {

   118         return $b["score"] - $a["score"];

   119     }

   120     if (!$contentlist) {

   121         usort($contenus,"triscore");

   122         $contenus = array_slice($contenus, 0, $content_count);

   123     } else {

   124         $contenus = array_values($contenus);

   125     }

   126     usort($tags,"triscore");

   127     usort($disciplines,"triscore");

   128 /*    $countries = array_values($countries);

   129  */

   130     $tags = array_slice($tags, 0, $tag_count);

   131     $disciplines = array_slice($disciplines, 0, 10);

   132     $years = array();

   133     $countries = array();

   134     if ($contentids) {

   135         $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");

   136         while($ligne = pg_fetch_row($rq)) {

   137             foreach(range($ligne[0], $ligne[1]) as $year) {

   138                 $years[$year] = $ligne[2] + ( isset($years[$year]) ? $years[$year] : 0 );

   139             }

   140         }

   141         pg_free_result($rq);

   142         $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");

   143         while($ligne = pg_fetch_row($rq)) {

   144             $countries[$ligne[0]] = $ligne[1];

   145         }

   146         pg_free_result($rq);

   147     }

   148 } else {

   149     $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");

   150     while($ligne = pg_fetch_assoc($rq)) {

   151         $ligne["tags"] = array();

   152         $contenus[$ligne["id"]] = $ligne;

   153     }

   154     $rq = pg_query("SELECT COUNT(*) FROM hdabo_datasheet WHERE validated=TRUE");

   155     $ligne = pg_fetch_row($rq);

   156     $cont_count = $ligne[0];

   157     pg_free_result($rq);

   158     $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");

   159     while($ligne = pg_fetch_row($rq)) {

   160         $contenus[$ligne[0]]["tags"][] = array("id" => $ligne[1], "label" => $ligne[2], "order" => $ligne[3]);

   161     }

   162     pg_free_result($rq);

   163     $contenus = array_values($contenus);

   164     

   165     $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");

   166     $tags = pg_fetch_all($rq);

   167     pg_free_result($rq);

   168     

   169     $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");

   170     $disciplines = pg_fetch_all($rq);

   171     pg_free_result($rq);

   172     

   173     $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");

   174     $years = array();

   175     while($ligne = pg_fetch_row($rq)) {

   176         foreach(range($ligne[0], $ligne[1]) as $year) {

   177             $years[$year] = $ligne[2] + ( isset($years[$year]) ? $years[$year] : 0 );

   178         }

   179     }

   180     pg_free_result($rq);

   181     $countries = array();

   182     $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");

   183     while($ligne = pg_fetch_row($rq)) {

   184         $countries[$ligne[0]] = $ligne[1];

   185     }

   186     pg_free_result($rq);

   187 }

   188 ksort($years);

   189 $yearchange = array();

   190 foreach($years as $year => $score) {

   191     if ($year < 2011) {

   192         if (!isset($years[$year-1]) || $years[$year-1] != $score) {

   193             $yearchange[] = array('year' => $year, 'score' => $score);

   194         }

   195         if (!isset($years[$year+1]) && ($year != -1)) {

   196             $yearchange[] = array('year' => $year + 1, 'score' => 0);

   197         }

   198     }

   199 }

   200 $output = array("count" => $cont_count, "contents" => $contenus, "tags" => $tags, "sparkline" => $yearchange, "countries" => $countries, "disciplines" => $disciplines);

   201 echo json_encode($output);

   202 ?>