3 * FusionForge reporting system
5 * Copyright 2003-2004, Tim Perdue/GForge, LLC
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_once $gfcommon.'reporting/Report.class.php';
25 class ReportSetup extends Report {
27 function ReportSetup() {
32 function initialSetup() {
33 $this->createTables();
34 if (!$this->initialData()) {
41 function createTables() {
43 //DROP TABLE rep_time_category;
44 $sql[]="CREATE TABLE rep_time_category (
45 time_code serial UNIQUE,
48 //$sql[]="DROP TABLE rep_time_tracking;";
49 $sql1="CREATE TABLE rep_time_tracking (
51 report_date int not null,
53 project_task_id int not null,
54 time_code int not null CONSTRAINT reptimetrk_timecode REFERENCES rep_time_category(time_code),
55 hours float not null);";
57 // $sql[]="CREATE UNIQUE INDEX reptimetrk_weekusrtskcde ON
58 // rep_time_tracking (week,user_id,project_task_id,time_code);";
59 $sql[]="CREATE INDEX reptimetracking_userdate ON
60 rep_time_tracking (user_id,week);";
62 $sql[]="INSERT INTO rep_time_category VALUES ('1','Coding');";
63 $sql[]="INSERT INTO rep_time_category VALUES ('2','Testing');";
64 $sql[]="INSERT INTO rep_time_category VALUES ('3','Meeting');";
65 $sql[]="SELECT setval('rep_time_category_time_code_seq',(SELECT max(time_code) FROM rep_time_category));";
68 $sql[]="DROP TABLE rep_users_added_daily;";
69 $sql[]="CREATE TABLE rep_users_added_daily (
70 day int not null primary key,
71 added int not null default 0);";
73 $sql[]="DROP TABLE rep_users_added_weekly";
74 $sql[]="CREATE TABLE rep_users_added_weekly (
75 week int not null primary key,
76 added int not null default 0);";
78 $sql[]="DROP TABLE rep_users_added_monthly";
79 $sql[]="CREATE TABLE rep_users_added_monthly (
80 month int not null primary key,
81 added int not null default 0);";
84 $sql[]="DROP TABLE rep_users_cum_daily";
85 $sql[]="CREATE TABLE rep_users_cum_daily (
86 day int not null primary key,
87 total int not null default 0);";
89 $sql[]="DROP TABLE rep_users_cum_weekly";
90 $sql[]="CREATE TABLE rep_users_cum_weekly (
91 week int not null primary key,
92 total int not null default 0);";
94 $sql[]="DROP TABLE rep_users_cum_monthly";
95 $sql[]="CREATE TABLE rep_users_cum_monthly (
96 month int not null primary key,
97 total int not null default 0);";
100 $sql[]="DROP TABLE rep_groups_added_daily;";
101 $sql[]="CREATE TABLE rep_groups_added_daily (
102 day int not null primary key,
103 added int not null default 0);";
105 $sql[]="DROP TABLE rep_groups_added_weekly";
106 $sql[]="CREATE TABLE rep_groups_added_weekly (
107 week int not null primary key,
108 added int not null default 0);";
110 $sql[]="DROP TABLE rep_groups_added_monthly";
111 $sql[]="CREATE TABLE rep_groups_added_monthly (
112 month int not null primary key,
113 added int not null default 0);";
116 $sql[]="DROP TABLE rep_groups_cum_daily";
117 $sql[]="CREATE TABLE rep_groups_cum_daily (
118 day int not null primary key,
119 total int not null default 0);";
121 $sql[]="DROP TABLE rep_groups_cum_weekly";
122 $sql[]="CREATE TABLE rep_groups_cum_weekly (
123 week int not null primary key,
124 total int not null default 0);";
126 $sql[]="DROP TABLE rep_groups_cum_monthly";
127 $sql[]="CREATE TABLE rep_groups_cum_monthly (
128 month int not null primary key,
129 total int not null default 0);";
132 $sql[]="DROP TABLE rep_user_act_daily";
133 $sql[]="CREATE TABLE rep_user_act_daily (
134 user_id int not null,
136 tracker_opened int not null,
137 tracker_closed int not null,
140 cvs_commits int not null,
141 tasks_opened int not null,
142 tasks_closed int not null,
143 PRIMARY KEY (user_id,day));";
145 $sql[]="DROP TABLE rep_user_act_weekly";
146 $sql[]="CREATE TABLE rep_user_act_weekly (
147 user_id int not null,
149 tracker_opened int not null,
150 tracker_closed int not null,
153 cvs_commits int not null,
154 tasks_opened int not null,
155 tasks_closed int not null,
156 PRIMARY KEY (user_id,week));";
158 $sql[]="DROP TABLE rep_user_act_monthly";
159 $sql[]="CREATE TABLE rep_user_act_monthly (
160 user_id int not null,
162 tracker_opened int not null,
163 tracker_closed int not null,
166 cvs_commits int not null,
167 tasks_opened int not null,
168 tasks_closed int not null,
169 PRIMARY KEY (user_id,month));";
171 $sql[]="DROP VIEW rep_user_act_oa_vw";
172 $sql[]="CREATE VIEW rep_user_act_oa_vw AS
174 sum(tracker_opened) AS tracker_opened,
175 sum(tracker_closed) AS tracker_closed,
178 sum(cvs_commits) AS cvs_commits,
179 sum(tasks_opened) AS tasks_opened,
180 sum(tasks_closed) AS tasks_closed
181 FROM rep_user_act_monthly
184 //per-project activity
185 $sql[]="DROP TABLE rep_group_act_daily";
186 $sql[]="CREATE TABLE rep_group_act_daily (
187 group_id int not null,
189 tracker_opened int not null,
190 tracker_closed int not null,
193 downloads int not null,
194 cvs_commits int not null,
195 tasks_opened int not null,
196 tasks_closed int not null,
197 PRIMARY KEY (group_id,day));";
199 $sql[]="DROP INDEX repgroupactdaily_day";
200 $sql[]="CREATE INDEX repgroupactdaily_day ON rep_group_act_daily(day)";
202 $sql[]="DROP TABLE rep_group_act_weekly";
203 $sql[]="CREATE TABLE rep_group_act_weekly (
204 group_id int not null,
206 tracker_opened int not null,
207 tracker_closed int not null,
210 downloads int not null,
211 cvs_commits int not null,
212 tasks_opened int not null,
213 tasks_closed int not null,
214 PRIMARY KEY (group_id,week));";
216 $sql[]="DROP INDEX repgroupactweekly_week";
217 $sql[]="CREATE INDEX repgroupactweekly_week ON rep_group_act_weekly(week)";
219 $sql[]="DROP TABLE rep_group_act_monthly";
220 $sql[]="CREATE TABLE rep_group_act_monthly (
221 group_id int not null,
223 tracker_opened int not null,
224 tracker_closed int not null,
227 downloads int not null,
228 cvs_commits int not null,
229 tasks_opened int not null,
230 tasks_closed int not null,
231 PRIMARY KEY (group_id,month));";
233 $sql[]="DROP INDEX repgroupactmonthly_month";
234 $sql[]="CREATE INDEX repgroupactmonthly_month ON rep_group_act_monthly(month)";
236 $sql[]="DROP VIEW rep_group_act_oa_vw";
237 $sql[]="CREATE VIEW rep_group_act_oa_vw AS
239 sum(tracker_opened) AS tracker_opened,
240 sum(tracker_closed) AS tracker_closed,
243 sum(downloads) AS downloads,
244 sum(cvs_commits) AS cvs_commits,
245 sum(tasks_opened) AS tasks_opened,
246 sum(tasks_closed) AS tasks_closed
247 FROM rep_group_act_monthly
251 $sql[]="DROP VIEW rep_site_act_daily_vw";
252 $sql[]="CREATE VIEW rep_site_act_daily_vw AS
254 sum(tracker_opened) AS tracker_opened,
255 sum(tracker_closed) AS tracker_closed,
258 sum(downloads) AS downloads,
259 sum(cvs_commits) AS cvs_commits,
260 sum(tasks_opened) AS tasks_opened,
261 sum(tasks_closed) AS tasks_closed
262 FROM rep_group_act_daily
265 $sql[]="DROP VIEW rep_site_act_weekly_vw";
266 $sql[]="CREATE VIEW rep_site_act_weekly_vw AS
268 sum(tracker_opened) AS tracker_opened,
269 sum(tracker_closed) AS tracker_closed,
272 sum(downloads) AS downloads,
273 sum(cvs_commits) AS cvs_commits,
274 sum(tasks_opened) AS tasks_opened,
275 sum(tasks_closed) AS tasks_closed
276 FROM rep_group_act_weekly
279 $sql[]="DROP VIEW rep_site_act_monthly_vw";
280 $sql[]="CREATE VIEW rep_site_act_monthly_vw AS
282 sum(tracker_opened) AS tracker_opened,
283 sum(tracker_closed) AS tracker_closed,
286 sum(downloads) AS downloads,
287 sum(cvs_commits) AS cvs_commits,
288 sum(tasks_opened) AS tasks_opened,
289 sum(tasks_closed) AS tasks_closed
290 FROM rep_group_act_monthly
293 $sql[]="DROP VIEW rep_site_act_oa_vw";
294 $sql[]="CREATE VIEW rep_site_act_oa_vw AS
296 sum(tracker_opened) AS tracker_opened,
297 sum(tracker_closed) AS tracker_closed,
300 sum(downloads) AS downloads,
301 sum(cvs_commits) AS cvs_commits,
302 sum(tasks_opened) AS tasks_opened,
303 sum(tasks_closed) AS tasks_closed
304 FROM rep_group_act_monthly;";
306 for ($i=0; $i<count($sql); $i++) {
308 $res=db_query_params($sql[$i], array());
314 function initialData() {
315 if (!$this->backfill_users_added_daily()) {
318 if (!$this->backfill_users_added_weekly()) {
321 if (!$this->backfill_users_added_monthly()) {
324 if (!$this->backfill_users_cum_daily()) {
327 if (!$this->backfill_users_cum_weekly()) {
330 if (!$this->backfill_users_cum_monthly()) {
333 if (!$this->backfill_groups_added_daily()) {
336 if (!$this->backfill_groups_added_weekly()) {
339 if (!$this->backfill_groups_added_monthly()) {
342 if (!$this->backfill_groups_cum_daily()) {
345 if (!$this->backfill_groups_cum_weekly()) {
348 if (!$this->backfill_groups_cum_monthly()) {
351 if (!$this->backfill_user_act_daily()) {
354 if (!$this->backfill_user_act_weekly()) {
357 if (!$this->backfill_user_act_monthly()) {
360 if (!$this->backfill_group_act_daily()) {
363 if (!$this->backfill_group_act_weekly()) {
366 if (!$this->backfill_group_act_monthly()) {
373 function dailyData() {
374 if (!$this->backfill_users_added_daily(1)) {
377 if (!$this->backfill_users_added_weekly(1)) {
380 if (!$this->backfill_users_added_monthly(2)) {
383 if (!$this->backfill_users_cum_daily(1)) {
386 if (!$this->backfill_users_cum_weekly(1)) {
389 if (!$this->backfill_users_cum_monthly(2)) {
392 if (!$this->backfill_user_act_daily(1)) {
395 if (!$this->backfill_user_act_weekly(1)) {
398 if (!$this->backfill_user_act_monthly(2)) {
401 if (!$this->backfill_group_act_daily(1)) {
404 if (!$this->backfill_group_act_weekly(1)) {
407 if (!$this->backfill_group_act_monthly(2)) {
413 * Add a row to the users_added_daily report table.
415 * @param int Day - the unix time of the beginning of the day.
416 * @return boolean Success.
418 function users_added_daily($day) {
419 db_query_params ('DELETE FROM rep_users_added_daily WHERE day=$1',
423 return db_query_params ('INSERT INTO rep_users_added_daily (day,added)
424 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
425 BETWEEN $3 AND $4 ))',
429 ($day + REPORT_DAY_SPAN - 1) ));
433 * Populate the users_added_daily report table.
435 * @return boolean Success.
437 function backfill_users_added_daily($count=10000) {
438 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
439 if (!$start_date=$this->getMinDate()) {
440 $this->setError('backfill_users_added_daily:: Could Not Get Start Date');
445 $day=($today-($i*REPORT_DAY_SPAN));
446 if (!$this->users_added_daily($day)) {
447 $this->setError('backfill_users_added_daily:: Error adding daily row: '.db_error());
450 if ($day < $start_date) {
462 * Add a row to the groups_added_daily report table.
464 * @param int Day - the unix time of the beginning of the day.
465 * @return boolean Success.
467 function groups_added_daily($day) {
468 db_query_params ('DELETE FROM rep_groups_added_daily WHERE day=$1',
472 return db_query_params ('INSERT INTO rep_groups_added_daily (day,added)
473 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
474 BETWEEN $3 AND $4 ))',
478 ($day + REPORT_DAY_SPAN - 1) ));
482 * Populate the groups_added_daily report table.
484 * @return boolean Success.
486 function backfill_groups_added_daily($count=10000) {
487 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
488 if (!$start_date=$this->getMinDate()) {
489 $this->setError('backfill_groups_added_daily:: Could Not Get Start Date');
494 $day=($today-($i*REPORT_DAY_SPAN));
495 if (!$this->groups_added_daily($day)) {
496 $this->setError('backfill_groups_added_daily:: Error adding daily row: '.db_error());
499 if ($day < $start_date) {
511 * Add a row to the users_added_weekly report table.
513 * @param int Week - the unix time of the beginning of the sunday for this week.
514 * @return boolean Success.
516 function users_added_weekly($week) {
517 db_query_params ('DELETE FROM rep_users_added_weekly WHERE week=$1',
521 return db_query_params ('INSERT INTO rep_users_added_weekly (week,added)
522 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
523 BETWEEN $3 AND $4 ))',
527 ($week+REPORT_WEEK_SPAN-1) ));
531 * Populate the users_added_weekly report table.
533 * @return boolean Success.
535 function backfill_users_added_weekly($count=10000) {
537 $arr =& $this->getWeekStartArr();
539 for ($i=0; $i<count($arr); $i++) {
540 if (!$this->users_added_weekly($arr[$i])) {
541 $this->setError('backfill_users_added_weekly:: Error adding weekly row: '.db_error());
552 * Add a row to the groups_added_weekly report table.
554 * @param int Week - the unix time of the beginning of the sunday for this week.
555 * @return boolean Success.
557 function groups_added_weekly($week) {
558 db_query_params ('DELETE FROM rep_groups_added_weekly WHERE week=$1',
562 return db_query_params ('INSERT INTO rep_groups_added_weekly (week,added)
563 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
564 BETWEEN $3 AND $4 ))',
568 ($week+REPORT_WEEK_SPAN-1) ));
572 * Populate the users_added_weekly report table.
574 * @return boolean Success.
576 function backfill_groups_added_weekly($count=10000) {
578 $arr =& $this->getWeekStartArr();
580 for ($i=0; $i<count($arr); $i++) {
581 if (!$this->groups_added_weekly($arr[$i])) {
582 $this->setError('backfill_groups_added_weekly:: Error adding weekly row: '.db_error());
593 * Add a row to the users_added_monthly report table.
595 * @param int month_start - the unix time of the beginning of the month.
596 * @param int month_end - the unix time of the end of the month.
597 * @return boolean Success.
599 function users_added_monthly($month,$end) {
600 db_query_params ('DELETE FROM rep_users_added_monthly WHERE month=$1',
604 return db_query_params ('INSERT INTO rep_users_added_monthly (month,added)
605 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
606 BETWEEN $3 AND $4 ))',
614 * Populate the users_added_monthly report table.
616 * @return boolean Success.
618 function backfill_users_added_monthly($count=10000) {
620 $arr =& $this->getMonthStartArr();
623 for ($i=1; $i<count($arr); $i++) {
624 if (!$this->users_added_monthly($arr[$i],($arr[$i-1]-1))) {
625 $this->setError('backfill_users_added_monthly:: Error adding monthly row: '.db_error());
636 * Add a row to the groups_added_monthly report table.
638 * @param int month_start - the unix time of the beginning of the month.
639 * @param int month_end - the unix time of the end of the month.
640 * @return boolean Success.
642 function groups_added_monthly($month,$end) {
643 db_query_params ('DELETE FROM rep_groups_added_monthly WHERE month=$1',
647 return db_query_params ('INSERT INTO rep_groups_added_monthly (month,added)
648 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
649 BETWEEN $3 AND $4 ))',
657 * Populate the groups_added_monthly report table.
659 * @return boolean Success.
661 function backfill_groups_added_monthly($count=10000) {
663 $arr =& $this->getMonthStartArr();
666 for ($i=1; $i<count($arr); $i++) {
667 if (!$this->groups_added_monthly($arr[$i],($arr[$i-1]-1))) {
668 $this->setError('backfill_groups_added_monthly:: Error adding monthly row: '.db_error());
679 // ******************************
683 * Add a row to the users_cum_daily report table.
685 * @param int Day - the unix time of the beginning of the day.
686 * @return boolean Success.
688 function users_cum_daily($day) {
689 db_query_params ('DELETE FROM rep_users_cum_daily WHERE day=$1',
693 return db_query_params ('INSERT INTO rep_users_cum_daily (day,total)
694 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
702 * Populate the users_cum_daily report table.
704 * @return boolean Success.
706 function backfill_users_cum_daily($count=10000) {
707 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
708 if (!$start_date=$this->getMinDate()) {
709 $this->setError('backfill_users_cum_daily:: Could Not Get Start Date');
714 $day=$today-($i*REPORT_DAY_SPAN);
715 if (!$this->users_cum_daily($day)) {
716 $this->setError('backfill_users_cum_daily:: Error adding daily row: '.db_error());
719 if ($day < $start_date) {
731 * Add a row to the groups_cum_daily report table.
733 * @param int Day - the unix time of the beginning of the day.
734 * @return boolean Success.
736 function groups_cum_daily($day) {
737 db_query_params ('DELETE FROM rep_groups_cum_daily WHERE day=$1',
741 return db_query_params ('INSERT INTO rep_groups_cum_daily (day,total)
742 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
750 * Populate the groups_cum_daily report table.
752 * @return boolean Success.
754 function backfill_groups_cum_daily($count=10000) {
755 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
756 if (!$start_date=$this->getMinDate()) {
757 $this->setError('backfill_groups_cum_daily:: Could Not Get Start Date');
762 $day=$today-($i*REPORT_DAY_SPAN);
763 if (!$this->groups_cum_daily($day)) {
764 $this->setError('backfill_groups_cum_daily:: Error adding daily row: '.db_error());
767 if ($day < $start_date) {
779 * Add a row to the users_cum_weekly report table.
781 * @param int Week - the unix time of the beginning of the sunday for this week.
782 * @return boolean Success.
784 function users_cum_weekly($week) {
785 db_query_params ('DELETE FROM rep_users_cum_weekly WHERE week=$1',
789 return db_query_params ('INSERT INTO rep_users_cum_weekly (week,total)
790 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
791 BETWEEN $3 AND $4))',
795 ($week+REPORT_WEEK_SPAN-1 )));
799 * Populate the users_cum_weekly report table.
801 * @return boolean Success.
803 function backfill_users_cum_weekly($count=10000) {
805 $arr =& $this->getWeekStartArr();
807 for ($i=0; $i<count($arr); $i++) {
808 if (!$this->groups_cum_weekly($arr[$i])) {
809 $this->setError('backfill_users_cum_weekly:: Error adding weekly row: '.db_error());
820 * Add a row to the groups_cum_weekly report table.
822 * @param int Week - the unix time of the beginning of the sunday for this week.
823 * @return boolean Success.
825 function groups_cum_weekly($week) {
826 db_query_params ('DELETE FROM rep_groups_cum_weekly WHERE week=$1',
830 return db_query_params ('INSERT INTO rep_groups_cum_weekly (week,total)
831 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
832 BETWEEN $3 AND $4))',
836 ($week+REPORT_WEEK_SPAN-1 )));
840 * Populate the groups_cum_weekly report table.
842 * @return boolean Success.
844 function backfill_groups_cum_weekly($count=10000) {
846 $arr =& $this->getWeekStartArr();
848 for ($i=0; $i<count($arr); $i++) {
849 if (!$this->users_cum_weekly($arr[$i])) {
850 $this->setError('backfill_groups_cum_weekly:: Error adding weekly row: '.db_error());
861 * Add a row to the users_cum_monthly report table.
863 * @param int month_start - the unix time of the beginning of the month.
864 * @param int month_end - the unix time of the end of the month.
865 * @return boolean Success.
867 function users_cum_monthly($month,$end) {
868 db_query_params ('DELETE FROM rep_users_cum_monthly WHERE month=$1',
872 return db_query_params ('INSERT INTO rep_users_cum_monthly (month,total)
873 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
881 * Populate the users_cum_monthly report table.
883 * @return boolean Success.
885 function backfill_users_cum_monthly($count=10000) {
887 $arr =& $this->getMonthStartArr();
890 for ($i=1; $i<count($arr); $i++) {
891 if (!$this->users_cum_monthly($arr[$i],($arr[$i-1]-1))) {
892 $this->setError('backfill_users_cum_monthly:: Error adding monthly row: '.db_error());
903 * Add a row to the groups_cum_monthly report table.
905 * @param int month_start - the unix time of the beginning of the month.
906 * @param int month_end - the unix time of the end of the month.
907 * @return boolean Success.
909 function groups_cum_monthly($month,$end) {
910 db_query_params ('DELETE FROM rep_groups_cum_monthly WHERE month=$1',
914 return db_query_params ('INSERT INTO rep_groups_cum_monthly (month,total)
915 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
923 * Populate the groups_cum_monthly report table.
925 * @return boolean Success.
927 function backfill_groups_cum_monthly($count=10000) {
929 $arr =& $this->getMonthStartArr();
932 for ($i=1; $i<count($arr); $i++) {
933 if (!$this->groups_cum_monthly($arr[$i],($arr[$i-1]-1))) {
934 $this->setError('backfill_groups_cum_monthly:: Error adding monthly row: '.db_error());
945 // ************************
949 * Add a row to the user_act_daily report table.
951 * @param int Day - the unix time of the beginning of the day.
952 * @return boolean Success.
954 function user_act_daily($day) {
955 db_query_params ('DELETE FROM rep_user_act_daily WHERE day=$1',
958 $end_day=$day+REPORT_DAY_SPAN-1;
960 return db_query_params ('INSERT INTO rep_user_act_daily
961 SELECT user_id,day,coalesce(tracker_opened,0) AS tracker_opened,
962 coalesce(tracker_closed,0) AS tracker_closed,
963 coalesce(forum,0) AS forum,
964 coalesce(docs,0) AS docs,
965 coalesce(cvs_commits,0) AS cvs_commits,
966 coalesce(tasks_opened,0) AS tasks_opened,
967 coalesce(tasks_closed,0) AS tasks_closed
975 (SELECT submitted_by AS user_id, $1::int AS day, count(*) AS tracker_opened
977 WHERE open_date BETWEEN $1 AND $2
978 GROUP BY user_id,day) aopen
981 (SELECT assigned_to AS user_id, $1::int AS day, count(*) AS tracker_closed
983 WHERE close_date BETWEEN $1 AND $2
984 GROUP BY user_id,day ) aclosed USING (user_id,day)) foo1
987 (SELECT posted_by AS user_id, $1::int AS day, count(*) AS forum
989 WHERE post_date BETWEEN $1 AND $2
990 GROUP BY user_id,day ) forum USING (user_id,day)) foo2
993 (SELECT created_by AS user_id, $1::int AS day, count(*) AS docs
995 WHERE createdate BETWEEN $1 AND $2
996 GROUP BY user_id,day ) docs USING (user_id,day)) foo3
999 (SELECT user_id, $1::int AS day, sum(commits) AS cvs_commits
1001 WHERE month=$3 AND day=$2
1002 GROUP BY user_id,day ) cvs USING (user_id,day)) foo4
1005 (SELECT created_by AS user_id, $1::int AS day, count(*) AS tasks_opened
1007 WHERE start_date BETWEEN $1 AND $2
1008 GROUP BY user_id,day ) topen USING (user_id,day)) foo5
1011 (SELECT mod_by AS user_id, $1::int AS day, count(*) AS tasks_closed
1012 FROM project_history
1013 WHERE mod_date BETWEEN $1 AND $2
1014 AND old_value=$4 AND field_name=$5
1015 GROUP BY user_id,day ) tclosed USING (user_id,day)) foo6',
1024 * Populate the user_act_daily report table.
1026 * @return boolean Success.
1028 function backfill_user_act_daily($count=10000) {
1029 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1030 if (!$start_date=$this->getMinDate()) {
1031 $this->setError('backfill_user_act_daily:: Could Not Get Start Date');
1036 $day=$today-($i*REPORT_DAY_SPAN);
1037 if (!$this->user_act_daily($day)) {
1038 $this->setError('backfill_user_act_daily:: Error adding daily row: '.db_error());
1041 if ($day < $start_date) {
1053 * Add a row to the user_act_weekly report table.
1055 * @param int Week - the unix time of the beginning of the sunday for this week.
1056 * @return boolean Success.
1058 function user_act_weekly($week) {
1059 db_query_params ('DELETE FROM rep_user_act_weekly WHERE week=$1',
1062 return db_query_params ('
1063 INSERT INTO rep_user_act_weekly (user_id, week, tracker_opened, tracker_closed,
1064 forum, docs, cvs_commits, tasks_opened, tasks_closed)
1065 SELECT user_id,$1::int AS week, sum(tracker_opened) AS tracker_opened,
1066 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1067 sum(docs) AS docs, sum(cvs_commits) AS cvs_commits,
1068 sum(tasks_opened) AS tasks_opened, sum(tasks_closed) AS tasks_closed
1069 FROM rep_user_act_daily
1070 WHERE DAY BETWEEN $1 AND $2
1071 GROUP BY user_id,week',
1073 $week+REPORT_WEEK_SPAN-1)) ;
1077 * Populate the user_act_weekly report table.
1079 * @return boolean Success.
1081 function backfill_user_act_weekly($count=10000) {
1083 $arr =& $this->getWeekStartArr();
1085 for ($i=0; $i<count($arr); $i++) {
1086 if (!$this->user_act_weekly($arr[$i])) {
1087 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1098 * Add a row to the user_act_monthly report table.
1100 * @param int month_start - the unix time of the beginning of the month.
1101 * @param int month_end - the unix time of the end of the month.
1102 * @return boolean Success.
1104 function user_act_monthly($month,$end) {
1105 db_query_params ('DELETE FROM rep_user_act_monthly WHERE month=$1',
1108 return db_query_params ('
1109 INSERT INTO rep_user_act_monthly (user_id, month, tracker_opened,
1110 tracker_closed, forum, docs, cvs_commits, tasks_opened, tasks_closed)
1111 SELECT user_id, $1::int AS month, sum(tracker_opened) AS tracker_opened,
1112 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1113 sum(docs) AS docs, sum(cvs_commits) AS cvs_commits,
1114 sum(tasks_opened) AS tasks_opened, sum(tasks_closed) AS tasks_closed
1115 FROM rep_user_act_daily
1116 WHERE DAY BETWEEN $1 AND $2
1117 GROUP BY user_id, month',
1118 array ($month, $end)) ;
1122 * Populate the user_act_monthly report table.
1124 * @return boolean Success.
1126 function backfill_user_act_monthly($count=10000) {
1128 $arr =& $this->getMonthStartArr();
1130 for ($i=1; $i<count($arr); $i++) {
1131 if (!$this->user_act_monthly($arr[$i],($arr[$i-1]-1))) {
1132 $this->setError('backfill_user_act_monthly:: Error adding monthly row: '.db_error());
1142 // ************************
1146 * Add a row to the group_act_daily report table.
1148 * @param int Day - the unix time of the beginning of the day.
1149 * @return boolean Success.
1151 function group_act_daily($day) {
1152 db_query_params ('DELETE FROM rep_group_act_daily WHERE day=$1',
1155 $end_day=$day+REPORT_DAY_SPAN-1;
1157 return db_query_params ('INSERT INTO rep_group_act_daily
1158 SELECT group_id,day,coalesce(tracker_opened,0) AS tracker_opened,
1159 coalesce(tracker_closed,0) AS tracker_closed,
1160 coalesce(forum,0) AS forum,
1161 coalesce(docs,0) AS docs,
1162 coalesce(downloads,0) AS downloads,
1163 coalesce(cvs_commits,0) AS cvs_commits,
1164 coalesce(tasks_opened,0) AS tasks_opened,
1165 coalesce(tasks_closed,0) AS tasks_closed
1174 (SELECT agl.group_id, $1::int AS day, count(*) AS tracker_opened
1175 FROM artifact a, artifact_group_list agl
1176 WHERE a.open_date BETWEEN $1 AND $2
1177 AND a.group_artifact_id=agl.group_artifact_id
1178 GROUP BY group_id,day) aopen
1181 (SELECT agl.group_id, $1::int AS day, count(*) AS tracker_closed
1182 FROM artifact a, artifact_group_list agl
1183 WHERE a.close_date BETWEEN $1 AND $2
1184 AND a.group_artifact_id=agl.group_artifact_id
1185 GROUP BY group_id,day ) aclosed USING (group_id,day)) foo1
1188 (SELECT fgl.group_id, $1::int AS day, count(*) AS forum
1189 FROM forum f, forum_group_list fgl
1190 WHERE f.post_date BETWEEN $1 AND $2
1191 AND f.group_forum_id=fgl.group_forum_id
1192 GROUP BY group_id,day ) forum USING (group_id,day)) foo2
1195 (SELECT group_id, $1::int AS day, count(*) AS docs
1197 WHERE createdate BETWEEN $1 AND $2
1198 GROUP BY group_id,day ) docs USING (group_id,day)) foo3
1201 (SELECT fp.group_id, $1::int AS day, count(*) AS downloads
1202 FROM frs_package fp, frs_release fr, frs_file ff, frs_dlstats_file fdf
1203 WHERE fp.package_id=fr.package_id
1204 AND fr.release_id=ff.release_id
1205 AND ff.file_id=fdf.file_id
1206 AND fdf.month = $3 AND fdf.day = $4
1207 GROUP BY fp.group_id,day ) docs USING (group_id,day)) foo4
1210 (SELECT group_id, $1::int AS day, sum(commits) AS cvs_commits
1211 FROM stats_cvs_group
1212 WHERE month=$3 AND day=$4
1213 GROUP BY group_id,day ) cvs USING (group_id,day)) foo5
1216 (SELECT pgl.group_id, $1::int AS day,count(*) AS tasks_opened
1217 FROM project_task pt, project_group_list pgl
1218 WHERE pt.start_date BETWEEN $1 AND $2
1219 AND pt.group_project_id=pgl.group_project_id
1220 GROUP BY group_id,day ) topen USING (group_id,day)) foo6
1223 (SELECT pgl.group_id, $1::int AS day, count(*) AS tasks_closed
1224 FROM project_history ph, project_task pt, project_group_list pgl
1225 WHERE ph.mod_date BETWEEN $1 AND $2
1227 AND ph.field_name=$6
1228 AND ph.project_task_id=pt.project_task_id
1229 AND pt.group_project_id=pgl.group_project_id
1230 GROUP BY group_id,day ) tclosed USING (group_id,day)) foo7',
1242 * Populate the group_act_daily report table.
1244 * @return boolean Success.
1246 function backfill_group_act_daily($count=10000) {
1247 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1248 if (!$start_date=$this->getMinDate()) {
1249 $this->setError('backfill_group_act_daily:: Could Not Get Start Date');
1254 $day=$today-($i*REPORT_DAY_SPAN);
1255 if (!$this->group_act_daily($day)) {
1256 $this->setError('backfill_group_act_daily:: Error adding daily row: '.db_error());
1259 if ($day < $start_date) {
1271 * Add a row to the group_act_weekly report table.
1273 * @param int Week - the unix time of the beginning of the sunday for this week.
1274 * @return boolean Success.
1276 function group_act_weekly($week) {
1277 db_query_params ('DELETE FROM rep_group_act_weekly WHERE week=$1',
1280 return db_query_params ('
1281 INSERT INTO rep_group_act_weekly (group_id, week, tracker_opened,
1282 tracker_closed, forum, docs, downloads, cvs_commits, tasks_opened,
1284 SELECT group_id, $1::int AS week, sum(tracker_opened) AS tracker_opened,
1285 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1286 sum(docs) AS docs, sum(downloads) AS downloads,
1287 sum(cvs_commits) AS cvs_commits, sum(tasks_opened) AS tasks_opened,
1288 sum(tasks_closed) AS tasks_closed
1289 FROM rep_group_act_daily
1290 WHERE DAY BETWEEN $1 AND $2
1291 GROUP BY group_id, week',
1293 $week+REPORT_WEEK_SPAN-1)) ;
1297 * Populate the group_act_weekly report table.
1299 * @return boolean Success.
1301 function backfill_group_act_weekly($count=10000) {
1303 $arr =& $this->getWeekStartArr();
1305 for ($i=0; $i<count($arr); $i++) {
1306 if (!$this->group_act_weekly($arr[$i])) {
1307 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1318 * Add a row to the group_act_monthly report table.
1320 * @param int month_start - the unix time of the beginning of the month.
1321 * @param int month_end - the unix time of the end of the month.
1322 * @return boolean Success.
1324 function group_act_monthly($month,$end) {
1325 db_query_params ('DELETE FROM rep_group_act_monthly WHERE month=$1',
1328 return db_query_params ('
1329 INSERT INTO rep_group_act_monthly (group_id, month, tracker_opened,
1330 tracker_closed, forum, docs, downloads, cvs_commits, tasks_opened,
1332 SELECT group_id, $1::int AS month, sum(tracker_opened) AS tracker_opened,
1333 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1334 sum(docs) AS docs, sum(downloads) AS downloads,
1335 sum(cvs_commits) AS cvs_commits,
1336 sum(tasks_opened) AS tasks_opened,
1337 sum(tasks_closed) AS tasks_closed
1338 FROM rep_group_act_daily
1339 WHERE DAY BETWEEN $1 AND $2
1340 GROUP BY group_id,month',
1341 array ($month, $end)) ;
1345 * Populate the group_act_monthly report table.
1347 * @return boolean Success.
1349 function backfill_group_act_monthly($count=10000) {
1351 $arr =& $this->getMonthStartArr();
1353 for ($i=1; $i<count($arr); $i++) {
1354 if (!$this->group_act_monthly($arr[$i],($arr[$i-1]-1))) {
1355 $this->setError('backfill_group_act_monthly:: Error adding monthly row: '.db_error());
1366 * Add a row to the rep_time_category table.
1368 * @param string The category name.
1369 * @return boolean Success.
1371 function addTimeCode($category_name) {
1372 return db_query_params ('INSERT INTO rep_time_category (category_name) VALUES ($1)',
1373 array($category_name)) ;
1377 * Update the rep_time_category table.
1379 * @param string The category name.
1380 * @return boolean Success.
1382 function updateTimeCode($time_code, $category_name) {
1383 return db_query_params ('UPDATE rep_time_category SET category_name=$1 WHERE time_code=$2',
1384 array($category_name,
1392 // c-file-style: "bsd"