<?php

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

require('connect.inc.php');

$periode = (isset($_REQUEST['period']) ? pg_escape_string($_REQUEST['period']) : 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 || $periode || $contentlist) {

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

    $globalids = array();

    $globalfilters = array();

    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 ($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 ($contentlist) {

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

    }

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

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

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

    $tags = array();

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

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

    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);

        }

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

        if ($match_tag) {

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

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

        }

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

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

    $countries = array_values($countries);

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

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

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

} else {

    $rq = pg_query("SELECT id, title, description, url FROM hdabo_datasheet 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");

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

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

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

}

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);

echo json_encode($output);

?>