3 |
3 |
4 require('connect.inc.php'); |
4 require('connect.inc.php'); |
5 |
5 |
6 $periode = (isset($_REQUEST['period']) ? pg_escape_string($_REQUEST['period']) : NULL); |
6 $periode = (isset($_REQUEST['period']) ? pg_escape_string($_REQUEST['period']) : NULL); |
7 $label = (isset($_REQUEST['label']) ? pg_escape_string($_REQUEST['label']) : NULL); |
7 $label = (isset($_REQUEST['label']) ? pg_escape_string($_REQUEST['label']) : NULL); |
|
8 $contentlist = (isset($_REQUEST['contentlist']) ? pg_escape_string($_REQUEST['contentlist']) : NULL); |
8 $max_tag_order = (isset($_REQUEST['mto']) ? pg_escape_string($_REQUEST['mto']) : 12); |
9 $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 $content_count = (isset($_REQUEST['contentcount']) ? pg_escape_string($_REQUEST['contentcount']) : 12); |
10 $tag_count = (isset($_REQUEST['tagcount']) ? pg_escape_string($_REQUEST['tagcount']) : 30); |
11 $tag_count = (isset($_REQUEST['tagcount']) ? pg_escape_string($_REQUEST['tagcount']) : 30); |
11 $cont_count = 0; |
12 $cont_count = 0; |
12 |
13 |
13 if ($label || $periode) { |
14 if ($label || $periode || $contentlist) { |
14 $globalsql = "SELECT id, title, description, url FROM hdabo_datasheet E"; |
15 $globalsql = "SELECT id, title, description, url FROM hdabo_datasheet E"; |
15 $globalids = array(); |
16 $globalids = array(); |
16 $globalfilters = array(); |
17 $globalfilters = array(); |
17 if ($periode) { |
18 if ($periode) { |
18 $years = split('\|',$periode); |
19 $years = split(',',$periode); |
19 $start_year = $years[0]; |
20 $start_year = $years[0]; |
20 $end_year = (count($years) > 1 ? $years[1] : $start_year); |
21 $end_year = (count($years) > 1 ? $years[1] : $start_year); |
21 $delta = max( 1, floor( ($end_year - $start_year ) / 2 ) ); |
22 $delta = max( 1, floor( ($end_year - $start_year ) / 2 ) ); |
22 $minstart = $start_year - $delta; |
23 $minstart = $start_year - $delta; |
23 $maxend = $end_year + $delta; |
24 $maxend = $end_year + $delta; |
29 pg_free_result($rq); |
30 pg_free_result($rq); |
30 $globalids = $ids; |
31 $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 $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 } |
33 if ($label) { |
34 if ($label) { |
34 $lblist = split('\|',$label); |
35 $lblist = split(',',$label); |
35 foreach ($lblist as $txtlbl) { |
36 foreach ($lblist as $txtlbl) { |
36 $rq = pg_query("SELECT id FROM hdabo_tag WHERE label ILIKE '$txtlbl'"); |
37 $rq = pg_query("SELECT id FROM hdabo_tag WHERE label ILIKE '$txtlbl'"); |
37 $ids = array(); |
38 $ids = array(); |
38 while($ligne = pg_fetch_row($rq)) { |
39 while($ligne = pg_fetch_row($rq)) { |
39 $ids[] = $ligne[0]; |
40 $ids[] = $ligne[0]; |
41 pg_free_result($rq); |
42 pg_free_result($rq); |
42 $globalids = array_merge($globalids,$ids); |
43 $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 $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 } |
45 } |
46 } |
|
47 if ($contentlist) { |
|
48 $globalfilters[] = "id IN ($contentlist)"; |
|
49 } |
46 $globalsql = $globalsql.(count($globalfilters) ? " WHERE ".join($globalfilters," AND ") : "" ); |
50 $globalsql = $globalsql.(count($globalfilters) ? " WHERE ".join($globalfilters," AND ") : "" ); |
47 if (!$label && !$periode) { |
51 if (!$label && !$periode && !$contentlist) { |
48 $globalsql = $globalsql." ORDER BY RANDOM()"; |
52 $globalsql = $globalsql." ORDER BY RANDOM()"; |
49 } |
53 } |
50 $rq = pg_query($globalsql); |
54 $rq = pg_query($globalsql); |
51 $contenus = array(); |
55 $contenus = array(); |
52 while($ligne = pg_fetch_assoc($rq)) { |
56 while($ligne = pg_fetch_assoc($rq)) { |
53 $cont_count++; |
57 $cont_count++; |
54 $ligne["score"] = 0; |
58 $ligne["score"] = 0; |
55 $ligne["tags"] = array(); |
59 $ligne["tags"] = array(); |
56 $contenus[$ligne["id"]] = $ligne; |
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); |
57 } |
67 } |
58 pg_free_result($rq); |
68 pg_free_result($rq); |
59 $tags = array(); |
69 $tags = array(); |
60 $contentids = join(",",array_keys($contenus)); |
70 $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"); |
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"); |
69 $tags[$ligne[1]]["score"] += $tagscore; |
79 $tags[$ligne[1]]["score"] += $tagscore; |
70 if ($match_tag) { |
80 if ($match_tag) { |
71 $contenus[$ligne[0]]["score"] += $tagscore; |
81 $contenus[$ligne[0]]["score"] += $tagscore; |
72 $tags[$ligne[1]]["match"] = True; |
82 $tags[$ligne[1]]["match"] = True; |
73 } |
83 } |
|
84 if (isset($countries[$ligne[2]])) { |
|
85 $countries[$ligne[2]]["score"] += $tagscore; |
|
86 } |
74 } |
87 } |
75 pg_free_result($rq); |
88 pg_free_result($rq); |
76 function triscore($a, $b) { |
89 function triscore($a, $b) { |
77 return $b["score"] - $a["score"]; |
90 return $b["score"] - $a["score"]; |
78 } |
91 } |
79 usort($contenus,"triscore"); |
92 if (!$contentlist) { |
80 $contenus = array_slice($contenus, 0, $content_count); |
93 usort($contenus,"triscore"); |
|
94 $contenus = array_slice($contenus, 0, $content_count); |
|
95 } else { |
|
96 $contenus = array_values($contenus); |
|
97 } |
81 usort($tags,"triscore"); |
98 usort($tags,"triscore"); |
|
99 $countries = array_values($countries); |
82 $tags = array_slice($tags, 0, $tag_count); |
100 $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"); |
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"); |
84 $years = array(); |
102 $years = array(); |
85 while($ligne = pg_fetch_row($rq)) { |
103 while($ligne = pg_fetch_row($rq)) { |
86 foreach(range($ligne[0], $ligne[1]) as $year) { |
104 foreach(range($ligne[0], $ligne[1]) as $year) { |
102 while($ligne = pg_fetch_row($rq)) { |
120 while($ligne = pg_fetch_row($rq)) { |
103 $contenus[$ligne[0]]["tags"][] = array("id" => $ligne[1], "label" => $ligne[2], "order" => $ligne[3]); |
121 $contenus[$ligne[0]]["tags"][] = array("id" => $ligne[1], "label" => $ligne[2], "order" => $ligne[3]); |
104 } |
122 } |
105 pg_free_result($rq); |
123 pg_free_result($rq); |
106 $contenus = array_values($contenus); |
124 $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"); |
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"); |
108 $tags = pg_fetch_all($rq); |
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); |
109 pg_free_result($rq); |
130 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"); |
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"); |
111 $years = array(); |
132 $years = array(); |
112 while($ligne = pg_fetch_row($rq)) { |
133 while($ligne = pg_fetch_row($rq)) { |
113 foreach(range($ligne[0], $ligne[1]) as $year) { |
134 foreach(range($ligne[0], $ligne[1]) as $year) { |
126 if (!isset($years[$year+1]) && ($year != -1)) { |
147 if (!isset($years[$year+1]) && ($year != -1)) { |
127 $yearchange[] = array('year' => $year + 1, 'score' => 0); |
148 $yearchange[] = array('year' => $year + 1, 'score' => 0); |
128 } |
149 } |
129 } |
150 } |
130 } |
151 } |
131 $output = array("count" => $cont_count, "contents" => $contenus, "tags" => $tags, "sparkline" => $yearchange); |
152 $output = array("count" => $cont_count, "contents" => $contenus, "tags" => $tags, "sparkline" => $yearchange, "countries" => $countries); |
132 echo json_encode($output); |
153 echo json_encode($output); |
133 ?> |
154 ?> |