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 $label = (isset($_REQUEST['label']) ? pg_escape_string($_REQUEST['label']) : NULL);

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

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

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

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

    12 $cont_count = 0;

    13 

    14 if ($label || $periode || $contentlist) {

    15     $globalsql = "SELECT id, title, description, url FROM hdabo_datasheet E";

    16     $globalids = array();

    17     $globalfilters = array();

    18     if ($periode) {

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

    20         $start_year = $years[0];

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

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

    23         $minstart = $start_year - $delta;

    24         $maxend = $end_year + $delta;

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

    26         $ids = array();

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

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

    29         }

    30         pg_free_result($rq);

    31         $globalids = $ids;

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

    33     }

    34     if ($label) {

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

    36         foreach ($lblist as $txtlbl) {

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

    38             $ids = array();

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

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

    41             }

    42             pg_free_result($rq);

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

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

    45         }

    46     }

    47     if ($contentlist) {

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

    49     }

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

    51     if (!$label && !$periode && !$contentlist) {

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

    53     }

    54     $rq = pg_query($globalsql);

    55     $contenus = array();

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

    57         $cont_count++;

    58         $ligne["score"] = 0;

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

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

    61     }

    62     pg_free_result($rq);

    63     $rq = pg_query("SELECT label, isocode FROM hdaviz_cc");

    64     $countries = array();

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

    66         $countries[$ligne[0]] = array("isocode" => $ligne[1], "score" => 0);

    67     }

    68     pg_free_result($rq);

    69     $tags = array();

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

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

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

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

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

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

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

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

    78         }

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

    80         if ($match_tag) {

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

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

    83         }

    84         if (isset($countries[$ligne[2]])) {

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

    86         }

    87     }

    88     pg_free_result($rq);

    89     function triscore($a, $b) {

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

    91     }

    92     if (!$contentlist) {

    93         usort($contenus,"triscore");

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

    95     } else {

    96         $contenus = array_values($contenus);

    97     }

    98     usort($tags,"triscore");

    99     $countries = array_values($countries);

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

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

   102     $years = array();

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

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

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

   106         }

   107     }

   108     pg_free_result($rq);

   109 } else {

   110     $rq = pg_query("SELECT id, title, description, url FROM hdabo_datasheet ORDER BY RANDOM() LIMIT $content_count");

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

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

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

   114     }

   115     $rq = pg_query("SELECT COUNT(*) FROM hdabo_datasheet");

   116     $ligne = pg_fetch_row($rq);

   117     $cont_count = $ligne[0];

   118     pg_free_result($rq);

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

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

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

   122     }

   123     pg_free_result($rq);

   124     $contenus = array_values($contenus);

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

   126     $tags = pg_fetch_all($rq);

   127     pg_free_result($rq);

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

   129     $countries = pg_fetch_all($rq);

   130     pg_free_result($rq);

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

   132     $years = array();

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

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

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

   136         }

   137     }

   138     pg_free_result($rq);

   139 }

   140 ksort($years);

   141 $yearchange = array();

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

   143     if ($year < 2011) {

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

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

   146         }

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

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

   149         }

   150     }

   151 }

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

   153 echo json_encode($output);

   154 ?>