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

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

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

    11 $cont_count = 0;

    12 

    13 if ($label || $periode) {

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

    15     $globalids = array();

    16     $globalfilters = array();

    17     if ($periode) {

    18         $years = split('\|',$periode);

    19         $start_year = $years[0];

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

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

    22         $minstart = $start_year - $delta;

    23         $maxend = $end_year + $delta;

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

    25         $ids = array();

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

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

    28         }

    29         pg_free_result($rq);

    30         $globalids = $ids;

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

    32     }

    33     if ($label) {

    34         $lblist = split('\|',$label);

    35         foreach ($lblist as $txtlbl) {

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

    37             $ids = array();

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

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

    40             }

    41             pg_free_result($rq);

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

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

    44         }

    45     }

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

    47     if (!$label && !$periode) {

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

    49     }

    50     $rq = pg_query($globalsql);

    51     $contenus = array();

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

    53         $cont_count++;

    54         $ligne["score"] = 0;

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

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

    57     }

    58     pg_free_result($rq);

    59     $tags = array();

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

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

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

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

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

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

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

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

    68         }

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

    70         if ($match_tag) {

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

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

    73         }

    74     }

    75     pg_free_result($rq);

    76     function triscore($a, $b) {

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

    78     }

    79     usort($contenus,"triscore");

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

    81     usort($tags,"triscore");

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

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

    84     $years = array();

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

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

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

    88         }

    89     }

    90     pg_free_result($rq);

    91 } else {

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

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

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

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

    96     }

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

    98     $ligne = pg_fetch_row($rq);

    99     $cont_count = $ligne[0];

   100     pg_free_result($rq);

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

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

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

   104     }

   105     pg_free_result($rq);

   106     $contenus = array_values($contenus);

   107     $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 ORDER BY score DESC LIMIT $tag_count");

   108     $tags = pg_fetch_all($rq);

   109     pg_free_result($rq);

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

   111     $years = array();

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

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

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

   115         }

   116     }

   117     pg_free_result($rq);

   118 }

   119 ksort($years);

   120 $yearchange = array();

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

   122     if ($year < 2011) {

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

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

   125         }

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

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

   128         }

   129     }

   130 }

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

   132 echo json_encode($output);

   133 ?>