42 pg_free_result($rq); |
42 pg_free_result($rq); |
43 $globalids = array_merge($globalids,$ids); |
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)"; |
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 } |
45 } |
46 } |
46 } |
47 if ($contentlist) { |
47 if (gettype($contentlist) != "NULL") { |
48 $globalfilters[] = "id IN ($contentlist)"; |
48 if ($contentlist) { |
|
49 $globalfilters[] = "id IN ($contentlist)"; |
|
50 } else { |
|
51 $globalfilters[] = "id IS NULL"; |
|
52 } |
49 } |
53 } |
50 $globalsql = $globalsql.(count($globalfilters) ? " WHERE ".join($globalfilters," AND ") : "" ); |
54 $globalsql = $globalsql.(count($globalfilters) ? " WHERE ".join($globalfilters," AND ") : "" ); |
51 if (!$label && !$periode && !$contentlist) { |
55 if (!$label && !$periode && gettype($contentlist)=="NULL") { |
52 $globalsql = $globalsql." ORDER BY RANDOM()"; |
56 $globalsql = $globalsql." ORDER BY RANDOM()"; |
53 } |
57 } |
54 $rq = pg_query($globalsql); |
58 $rq = pg_query($globalsql); |
55 $contenus = array(); |
59 $contenus = array(); |
56 while($ligne = pg_fetch_assoc($rq)) { |
60 while($ligne = pg_fetch_assoc($rq)) { |
64 $countries = array(); |
68 $countries = array(); |
65 while($ligne = pg_fetch_row($rq)) { |
69 while($ligne = pg_fetch_row($rq)) { |
66 $countries[$ligne[0]] = array("isocode" => $ligne[1], "score" => 0); |
70 $countries[$ligne[0]] = array("isocode" => $ligne[1], "score" => 0); |
67 } |
71 } |
68 pg_free_result($rq); |
72 pg_free_result($rq); |
|
73 $rq = pg_query("SELECT DISTINCT label FROM hdabo_tag WHERE category_id=5"); |
|
74 $disciplines = array(); |
|
75 while($ligne = pg_fetch_row($rq)) { |
|
76 $disciplines[$ligne[0]] = array("label" => $ligne[0], "score" => 0); |
|
77 } |
|
78 pg_free_result($rq); |
69 $tags = array(); |
79 $tags = array(); |
70 $contentids = join(",",array_keys($contenus)); |
80 $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"); |
81 if ($contentids) { |
72 while($ligne = pg_fetch_row($rq)) { |
82 $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"); |
73 $match_tag = in_array($ligne[1], $globalids); |
83 while($ligne = pg_fetch_row($rq)) { |
74 $contenus[$ligne[0]]["tags"][] = array("id" => $ligne[1], "label" => $ligne[2], "order" => $ligne[3], "match" => $match_tag); |
84 $match_tag = in_array($ligne[1], $globalids); |
75 $tagscore = 2*$max_tag_order-$ligne[3]; |
85 $contenus[$ligne[0]]["tags"][] = array("id" => $ligne[1], "label" => $ligne[2], "order" => $ligne[3], "match" => $match_tag); |
76 if (!isset($tags[$ligne[1]])) { |
86 $tagscore = 2*$max_tag_order-$ligne[3]; |
77 $tags[$ligne[1]] = array("id" => $ligne[1], "label" => $ligne[2], "score" => 0); |
87 if (!isset($tags[$ligne[1]])) { |
|
88 $tags[$ligne[1]] = array("id" => $ligne[1], "label" => $ligne[2], "score" => 0); |
|
89 } |
|
90 $tags[$ligne[1]]["score"] += $tagscore; |
|
91 if ($match_tag) { |
|
92 $contenus[$ligne[0]]["score"] += $tagscore; |
|
93 $tags[$ligne[1]]["match"] = True; |
|
94 } |
|
95 if (isset($countries[$ligne[2]])) { |
|
96 $countries[$ligne[2]]["score"] += $tagscore; |
|
97 } |
|
98 if (isset($disciplines[$ligne[2]])) { |
|
99 $disciplines[$ligne[2]]["score"] += $tagscore; |
|
100 } |
78 } |
101 } |
79 $tags[$ligne[1]]["score"] += $tagscore; |
102 pg_free_result($rq); |
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 } |
103 } |
88 pg_free_result($rq); |
|
89 function triscore($a, $b) { |
104 function triscore($a, $b) { |
90 return $b["score"] - $a["score"]; |
105 return $b["score"] - $a["score"]; |
91 } |
106 } |
92 if (!$contentlist) { |
107 if (!$contentlist) { |
93 usort($contenus,"triscore"); |
108 usort($contenus,"triscore"); |
94 $contenus = array_slice($contenus, 0, $content_count); |
109 $contenus = array_slice($contenus, 0, $content_count); |
95 } else { |
110 } else { |
96 $contenus = array_values($contenus); |
111 $contenus = array_values($contenus); |
97 } |
112 } |
98 usort($tags,"triscore"); |
113 usort($tags,"triscore"); |
|
114 usort($disciplines,"triscore"); |
99 $countries = array_values($countries); |
115 $countries = array_values($countries); |
100 $tags = array_slice($tags, 0, $tag_count); |
116 $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"); |
117 $disciplines = array_slice($disciplines, 0, 10); |
102 $years = array(); |
118 $years = array(); |
103 while($ligne = pg_fetch_row($rq)) { |
119 if ($contentids) { |
104 foreach(range($ligne[0], $ligne[1]) as $year) { |
120 $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"); |
105 $years[$year] = $ligne[2] + ( isset($years[$year]) ? $years[$year] : 0 ); |
121 while($ligne = pg_fetch_row($rq)) { |
|
122 foreach(range($ligne[0], $ligne[1]) as $year) { |
|
123 $years[$year] = $ligne[2] + ( isset($years[$year]) ? $years[$year] : 0 ); |
|
124 } |
106 } |
125 } |
|
126 pg_free_result($rq); |
107 } |
127 } |
108 pg_free_result($rq); |
|
109 } else { |
128 } else { |
110 $rq = pg_query("SELECT id, title, description, url FROM hdabo_datasheet ORDER BY RANDOM() LIMIT $content_count"); |
129 $rq = pg_query("SELECT id, title, description, url FROM hdabo_datasheet ORDER BY RANDOM() LIMIT $content_count"); |
111 while($ligne = pg_fetch_assoc($rq)) { |
130 while($ligne = pg_fetch_assoc($rq)) { |
112 $ligne["tags"] = array(); |
131 $ligne["tags"] = array(); |
113 $contenus[$ligne["id"]] = $ligne; |
132 $contenus[$ligne["id"]] = $ligne; |
126 $tags = pg_fetch_all($rq); |
145 $tags = pg_fetch_all($rq); |
127 pg_free_result($rq); |
146 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"); |
147 $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); |
148 $countries = pg_fetch_all($rq); |
130 pg_free_result($rq); |
149 pg_free_result($rq); |
|
150 $rq = pg_query("SELECT A.label, SUM(".(2*$max_tag_order)." - B.order) 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 GROUP BY A.label ORDER BY score DESC LIMIT 10"); |
|
151 $disciplines = pg_fetch_all($rq); |
|
152 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"); |
153 $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(); |
154 $years = array(); |
133 while($ligne = pg_fetch_row($rq)) { |
155 while($ligne = pg_fetch_row($rq)) { |
134 foreach(range($ligne[0], $ligne[1]) as $year) { |
156 foreach(range($ligne[0], $ligne[1]) as $year) { |
135 $years[$year] = $ligne[2] + ( isset($years[$year]) ? $years[$year] : 0 ); |
157 $years[$year] = $ligne[2] + ( isset($years[$year]) ? $years[$year] : 0 ); |