4 * Copyright 1999-2001 (c) VA Linux Systems
5 * Copyright 2009, Roland Mas
7 * This file is part of FusionForge. FusionForge is free software;
8 * you can redistribute it and/or modify it under the terms of the
9 * GNU General Public License as published by the Free Software
10 * Foundation; either version 2 of the Licence, or (at your option)
13 * FusionForge is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
18 * You should have received a copy of the GNU General Public License along
19 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
20 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
23 require (dirname(__FILE__).'/../www/env.inc.php');
24 require_once $gfcommon.'include/pre.php';
25 require $gfcommon.'include/cron_utils.php';
31 $last_week = ( $time - (86400 * 7) );
32 $this_week = ( $time );
34 $last_year = date('Y',$last_week);
35 $last_month = date('m',$last_week);
36 $last_day = date('d',$last_week);
38 $this_year = date('Y',$this_week);
39 $this_month = date('m',$this_week);
40 $this_day = date('d',$this_week);
42 $err .= "\nlast_week: $last_week $last_day ";
43 $err .= "\n\nthis_week: $this_week $this_day";
45 // Clean temporary tables & sequences if present
46 db_drop_sequence_if_exists("project_metric_weekly_seq") ;
47 db_drop_table_if_exists("project_counts_weekly_tmp");
48 db_drop_table_if_exists("project_metric_weekly_tmp1");
49 db_drop_sequence_if_exists("project_metric_week_ranking_seq");
51 #create a table to put the aggregates in
52 $rel = db_query_params ('CREATE TABLE project_counts_weekly_tmp (group_id INT, type TEXT, count FLOAT(8))',
55 $err .= "\n\n***ERROR: \n\n".db_error();
60 $rel = db_query_params ('INSERT INTO project_counts_weekly_tmp
61 SELECT forum_group_list.group_id,$1,3*log(1+count(forum.msg_id)::float) AS count
62 FROM forum,forum_group_list
63 WHERE forum.group_forum_id=forum_group_list.group_forum_id
72 $err .= "\n\n***ERROR:\n\n".db_error();
75 #project manager tasks
76 $rel = db_query_params ('INSERT INTO project_counts_weekly_tmp
77 SELECT project_group_list.group_id,$1,4*log(1+count(project_task.project_task_id)::float) AS count
78 FROM project_task,project_group_list
79 WHERE project_task.group_project_id=project_group_list.group_project_id
88 $err .= "\n\n***ERROR:\n\n".db_error();
92 $rel = db_query_params ('INSERT INTO project_counts_weekly_tmp
93 SELECT agl.group_id,$1,3*log(1+count(*)::float) AS count
94 FROM artifact_group_list agl,artifact a
95 WHERE a.open_date >= $2
97 AND a.group_artifact_id=agl.group_artifact_id
99 GROUP BY agl.group_id',
106 $err .= "\n\n***ERROR:\n\n".db_error();
110 $rel = db_query_params ('INSERT INTO project_counts_weekly_tmp
111 SELECT agl.group_id,$1,10*log(1+count(*)::float) AS count
112 FROM artifact_group_list agl,artifact a
113 WHERE a.open_date >= $2
115 AND a.group_artifact_id=agl.group_artifact_id
117 GROUP BY agl.group_id',
123 $err .= "\n\n***ERROR: \n\n".db_error();
127 $rel = db_query_params ('INSERT INTO project_counts_weekly_tmp
128 SELECT agl.group_id,$1,5*log(1+count(*)::float) AS count
129 FROM artifact_group_list agl,artifact a
130 WHERE a.open_date >= $2
132 AND a.group_artifact_id=agl.group_artifact_id
134 GROUP BY agl.group_id',
141 $err .= "\n\n***ERROR:\n\n".db_error();
145 $rel = db_query_params ('INSERT INTO project_counts_weekly_tmp
146 SELECT group_id,$1,log(1+sum(commits)::float) AS count
148 WHERE ((month = $2 AND day >= $3) OR (month > $4))
152 "$last_year$last_month",
154 "$last_year$last_month"));
156 $err .= "\n\n***ERROR:\n\n".db_error();
160 $rel = db_query_params ('INSERT INTO project_counts_weekly_tmp
161 SELECT frs_package.group_id,$1,5*log(1+count(*)::float)
162 FROM frs_release,frs_package
163 WHERE frs_package.package_id = frs_release.package_id
164 AND frs_release.release_date >= $2
165 AND frs_release.release_date < $3
166 GROUP BY frs_package.group_id',
167 array('filereleases',
172 $err .= "\n\n***ERROR:\n\n".db_error();
178 $rel = db_query_params ('INSERT INTO project_counts_weekly_tmp
179 SELECT group_id,$1, .3*log(1+sum(downloads)::float) AS downloads
180 FROM frs_dlstats_group_vw
181 WHERE (month = $2 AND day >= $3) OR (month > $4)
184 "$last_year$last_month",
186 "$last_year$last_month"));
188 $err .= "\n\n***ERROR:\n\n".db_error();
193 $rel = db_query_params ('CREATE SEQUENCE project_metric_weekly_seq',
196 $err .= "\n\n***ERROR: \n\n".db_error();
199 #create a new table to insert the final records into
200 $rel = db_query_params ('CREATE TABLE project_metric_weekly_tmp1 (ranking SERIAL PRIMARY KEY, group_id INT NOT NULL, value FLOAT (10))',
203 $err .= "\n\n***ERROR: \n\n".db_error();
208 #insert the rows into the table in order, adding a sequential rank #
209 $rel = db_query_params ('INSERT INTO project_metric_weekly_tmp1 (group_id,value)
210 SELECT project_counts_weekly_tmp.group_id,sum(project_counts_weekly_tmp.count) AS value
211 FROM project_counts_weekly_tmp
212 WHERE project_counts_weekly_tmp.count > 0
213 GROUP BY group_id ORDER BY value DESC',
216 $err .= "\n\n***ERROR: \n\n".db_error();
221 $rel = db_query_params ('SELECT count(*) FROM project_metric_weekly_tmp1',
224 $err .= "\n\n***ERROR:\n\n".db_error();
227 $counts = db_result($rel,0,0);
228 $err .= "\n\nCounts: ".$counts;
231 #drop the old metrics table
233 $rel = db_query_params ('DELETE FROM project_weekly_metric',
236 $err .= "\n\n***ERROR: \n\n".db_error();
240 $rel = db_query_params ('INSERT INTO project_weekly_metric (ranking,percentile,group_id)
241 SELECT ranking,100-(100*((ranking::float-1)/$1)),group_id
242 FROM project_metric_weekly_tmp1
243 ORDER BY ranking ASC',
246 $err .= "\n\n***ERROR:\n\n".db_error();
251 // Now archive the metric
253 db_query_params ('DELETE FROM stats_project_metric WHERE month=$1 AND day=$2',
254 array("$this_year$this_month",
257 $rel = db_query_params ('INSERT INTO stats_project_metric (month,day,group_id,ranking,percentile)
258 SELECT $1::int, $2::int,group_id,ranking,percentile
259 FROM project_weekly_metric',
260 array("$this_year$this_month",
263 $err .= "\n\n***ERROR:\n\n".db_error();
268 db_drop_sequence_if_exists("project_metric_weekly_seq") ;
269 db_drop_table_if_exists("project_counts_weekly_tmp");
270 db_drop_table_if_exists("project_metric_weekly_tmp1");
271 db_drop_sequence_if_exists("project_metric_week_ranking_seq");