4 * Copyright 1999-2001 (c) VA Linux Systems
5 * Copyright 2009, Roland Mas
7 * This file is part of FusionForge.
9 * FusionForge is free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
14 * FusionForge is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
19 * You should have received a copy of the GNU General Public License
20 * along with FusionForge; if not, write to the Free Software
21 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 US
24 require (dirname(__FILE__).'/../www/env.inc.php');
25 require $gfwww.'include/squal_pre.php';
26 require $gfcommon.'include/cron_utils.php';
32 $last_week = ( $time - (86400 * 7) );
33 $this_week = ( $time );
35 $last_year = date('Y',$last_week);
36 $last_month = date('m',$last_week);
37 $last_day = date('d',$last_week);
39 $this_year = date('Y',$this_week);
40 $this_month = date('m',$this_week);
41 $this_day = date('d',$this_week);
43 $err .= "\nlast_week: $last_week $last_day ";
44 $err .= "\n\nthis_week: $this_week $this_day";
46 db_drop_table_if_exists ("project_counts_weekly_tmp");
47 $err .= "\n\nDROP TABLE project_counts_weekly_tmp" ;
48 db_drop_table_if_exists ("project_metric_weekly_tmp1");
49 $err .= "\n\nDROP TABLE project_metric_weekly_tmp1" ;
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,log(5 * 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");