4 * Copyright 1999-2001 (c) VA Linux Systems
8 * This file is part of GForge.
10 * GForge is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License as published by
12 * the Free Software Foundation; either version 2 of the License, or
13 * (at your option) any later version.
15 * GForge is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
20 * You should have received a copy of the GNU General Public License
21 * along with GForge; if not, write to the Free Software
22 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 US
25 require ('squal_pre.php');
26 require ('common/include/cron_utils.php');
31 //FIRST TIME THIS SCRIPT IS RUN - YOU MAY NEED TO RUN THIS QUERY FIRST
33 //nightly aggregation query
35 CREATE TABLE trove_agg AS
36 SELECT tgl.trove_cat_id, g.group_id, g.group_name, g.unix_group_name,
37 g.status, g.register_time, g.short_description,
38 project_weekly_metric.percentile, project_weekly_metric.ranking
40 LEFT JOIN project_weekly_metric USING (group_id) ,
43 tgl.group_id=g.group_id
47 ORDER BY trove_cat_id ASC, ranking ASC;
49 CREATE INDEX troveagg_trovecatid ON trove_agg(trove_cat_id);
50 create index troveagg_trovecatid_ranking ON trove_agg(trove_cat_id,ranking);
52 DROP TABLE trove_treesums;
53 CREATE TABLE "trove_treesums" (
54 "trove_treesums_id" serial primary key,
55 "trove_cat_id" integer DEFAULT '0' NOT NULL,
56 "limit_1" integer DEFAULT '0' NOT NULL,
57 "subprojects" integer DEFAULT '0' NOT NULL
62 /*if (!strstr($REMOTE_ADDR,$sys_internal_network)) {
63 exit_permission_denied();
68 Rebuild the trove_agg table, which saves us
69 from doing really expensive queries in trove
70 each time of the trove map is viewed
74 db_begin(SYS_DB_TROVE);
76 db_query("DELETE FROM trove_agg;", -1, 0, SYS_DB_TROVE);
78 $sql="INSERT INTO trove_agg
79 (SELECT tgl.trove_cat_id, g.group_id, g.group_name, g.unix_group_name, g.status, g.register_time, g.short_description, project_weekly_metric.percentile, project_weekly_metric.ranking
81 LEFT JOIN project_weekly_metric USING (group_id), trove_group_link tgl
82 WHERE tgl.group_id=g.group_id
86 ORDER BY trove_cat_id ASC, ranking ASC)";
88 db_query($sql, -1, 0, SYS_DB_TROVE);
89 $err .= db_error(SYS_DB_TROVE);
91 db_commit(SYS_DB_TROVE);
95 Calculate the number of projects under each category
97 Do this by first running an aggregate query in the database,
98 then putting that into two associative arrays.
100 Start at the top of the trove tree and recursively go down
101 the tree, building a third associative array which contains
102 the count of projects under each category
104 Then iterate through that third array and insert the results into the
105 database inside of a transaction
110 $parent_list=array();
112 $q = "SELECT trove_cat.trove_cat_id,trove_cat.parent
114 WHERE trove_cat.trove_cat_id!=0
115 GROUP BY trove_cat.trove_cat_id,trove_cat.parent;" ;
117 $rows=db_numrows($res);
119 for ($i=0; $i<$rows; $i++) {
120 $parent_list[db_result($res,$i,'parent')][]=db_result($res,$i,'trove_cat_id');
123 $res=db_query("SELECT trove_cat.trove_cat_id,trove_cat.parent,count(groups.group_id) AS count
124 FROM trove_cat LEFT JOIN trove_group_link ON
125 trove_cat.trove_cat_id=trove_group_link.trove_cat_id
127 groups.group_id=trove_group_link.group_id
128 WHERE (groups.status='A' OR groups.status IS NULL)
129 AND ( groups.type_id='1' OR groups.status IS NULL)
130 AND ( groups.is_public='1' OR groups.is_public IS NULL)
131 GROUP BY trove_cat.trove_cat_id,trove_cat.parent", -1, 0, SYS_DB_TROVE);
133 $rows = db_numrows($res);
135 for ($i=0; $i<$rows; $i++) {
136 $cat_counts[db_result($res,$i,'trove_cat_id')][0]=db_result($res,$i,'parent');
137 $cat_counts[db_result($res,$i,'trove_cat_id')][1]=db_result($res,$i,'count');
142 function get_trove_sub_projects($cat_id) {
143 global $cat_counts,$sum_totals,$parent_list;
145 //number of groups that were in this trove_cat
146 $count=$cat_counts[$cat_id][1];
147 if ($count == '') { $count = 0 ; }
149 //number of children of this trove_cat
150 $rows=count( @$parent_list[$cat_id] );
152 for ($i=0; $i<$rows; $i++) {
153 $count += get_trove_sub_projects( $parent_list[$cat_id][$i] );
155 $sum_totals["$cat_id"]=$count;
159 //start the recursive function at the top of the trove tree
160 $res2=db_query("SELECT trove_cat_id FROM trove_cat WHERE parent=0", -1, 0, SYS_DB_TROVE);
162 for ($i=0; $i< db_numrows($res2); $i++) {
163 get_trove_sub_projects( db_result($res2,$i,0) );
166 db_begin(SYS_DB_TROVE);
167 db_query("DELETE FROM trove_treesums", -1, 0, SYS_DB_TROVE);
168 $err .= db_error(SYS_DB_TROVE);
171 while (list($k,$v) = each($sum_totals)) {
172 $res = db_query("INSERT INTO trove_treesums (trove_cat_id,subprojects)
173 VALUES ($k,$v)", -1, 0, SYS_DB_TROVE);
174 if (!$res || db_affected_rows($res)!=1) {
175 $err .= db_error(SYS_DB_TROVE);
177 // $err .= "<tr><td>$k</td><td>$v</td></tr>\n";
180 //$err .= "</TABLE>";
182 db_commit(SYS_DB_TROVE);
184 if (db_error(SYS_DB_TROVE)) {
185 $err .= "Error: ".db_error(SYS_DB_TROVE);