3 * FusionForge reporting system
5 * Copyright 2003-2004, Tim Perdue/GForge, LLC
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
11 * by the Free Software Foundation; either version 2 of the License,
12 * or (at your option) any later version.
14 * FusionForge is distributed in the hope that it will be useful, but
15 * WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
17 * 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
25 require_once $gfcommon.'reporting/Report.class.php';
27 class ReportSetup extends Report {
29 function ReportSetup() {
34 function initialSetup() {
35 $this->createTables();
36 if (!$this->initialData()) {
43 function createTables() {
45 //DROP TABLE rep_time_category;
46 $sql[]="CREATE TABLE rep_time_category (
47 time_code serial UNIQUE,
50 //$sql[]="DROP TABLE rep_time_tracking;";
51 $sql1="CREATE TABLE rep_time_tracking (
53 report_date int not null,
55 project_task_id int not null,
56 time_code int not null CONSTRAINT reptimetrk_timecode REFERENCES rep_time_category(time_code),
57 hours float not null);";
59 // $sql[]="CREATE UNIQUE INDEX reptimetrk_weekusrtskcde ON
60 // rep_time_tracking (week,user_id,project_task_id,time_code);";
61 $sql[]="CREATE INDEX reptimetracking_userdate ON
62 rep_time_tracking (user_id,week);";
64 $sql[]="INSERT INTO rep_time_category VALUES ('1','Coding');";
65 $sql[]="INSERT INTO rep_time_category VALUES ('2','Testing');";
66 $sql[]="INSERT INTO rep_time_category VALUES ('3','Meeting');";
67 $sql[]="SELECT setval('rep_time_category_time_code_seq',(SELECT max(time_code) FROM rep_time_category));";
70 $sql[]="DROP TABLE rep_users_added_daily;";
71 $sql[]="CREATE TABLE rep_users_added_daily (
72 day int not null primary key,
73 added int not null default 0);";
75 $sql[]="DROP TABLE rep_users_added_weekly";
76 $sql[]="CREATE TABLE rep_users_added_weekly (
77 week int not null primary key,
78 added int not null default 0);";
80 $sql[]="DROP TABLE rep_users_added_monthly";
81 $sql[]="CREATE TABLE rep_users_added_monthly (
82 month int not null primary key,
83 added int not null default 0);";
86 $sql[]="DROP TABLE rep_users_cum_daily";
87 $sql[]="CREATE TABLE rep_users_cum_daily (
88 day int not null primary key,
89 total int not null default 0);";
91 $sql[]="DROP TABLE rep_users_cum_weekly";
92 $sql[]="CREATE TABLE rep_users_cum_weekly (
93 week int not null primary key,
94 total int not null default 0);";
96 $sql[]="DROP TABLE rep_users_cum_monthly";
97 $sql[]="CREATE TABLE rep_users_cum_monthly (
98 month int not null primary key,
99 total int not null default 0);";
102 $sql[]="DROP TABLE rep_groups_added_daily;";
103 $sql[]="CREATE TABLE rep_groups_added_daily (
104 day int not null primary key,
105 added int not null default 0);";
107 $sql[]="DROP TABLE rep_groups_added_weekly";
108 $sql[]="CREATE TABLE rep_groups_added_weekly (
109 week int not null primary key,
110 added int not null default 0);";
112 $sql[]="DROP TABLE rep_groups_added_monthly";
113 $sql[]="CREATE TABLE rep_groups_added_monthly (
114 month int not null primary key,
115 added int not null default 0);";
118 $sql[]="DROP TABLE rep_groups_cum_daily";
119 $sql[]="CREATE TABLE rep_groups_cum_daily (
120 day int not null primary key,
121 total int not null default 0);";
123 $sql[]="DROP TABLE rep_groups_cum_weekly";
124 $sql[]="CREATE TABLE rep_groups_cum_weekly (
125 week int not null primary key,
126 total int not null default 0);";
128 $sql[]="DROP TABLE rep_groups_cum_monthly";
129 $sql[]="CREATE TABLE rep_groups_cum_monthly (
130 month int not null primary key,
131 total int not null default 0);";
134 $sql[]="DROP TABLE rep_user_act_daily";
135 $sql[]="CREATE TABLE rep_user_act_daily (
136 user_id int not null,
138 tracker_opened int not null,
139 tracker_closed int not null,
142 cvs_commits int not null,
143 tasks_opened int not null,
144 tasks_closed int not null,
145 PRIMARY KEY (user_id,day));";
147 $sql[]="DROP TABLE rep_user_act_weekly";
148 $sql[]="CREATE TABLE rep_user_act_weekly (
149 user_id int not null,
151 tracker_opened int not null,
152 tracker_closed int not null,
155 cvs_commits int not null,
156 tasks_opened int not null,
157 tasks_closed int not null,
158 PRIMARY KEY (user_id,week));";
160 $sql[]="DROP TABLE rep_user_act_monthly";
161 $sql[]="CREATE TABLE rep_user_act_monthly (
162 user_id int not null,
164 tracker_opened int not null,
165 tracker_closed int not null,
168 cvs_commits int not null,
169 tasks_opened int not null,
170 tasks_closed int not null,
171 PRIMARY KEY (user_id,month));";
173 $sql[]="DROP VIEW rep_user_act_oa_vw";
174 $sql[]="CREATE VIEW rep_user_act_oa_vw AS
176 sum(tracker_opened) AS tracker_opened,
177 sum(tracker_closed) AS tracker_closed,
180 sum(cvs_commits) AS cvs_commits,
181 sum(tasks_opened) AS tasks_opened,
182 sum(tasks_closed) AS tasks_closed
183 FROM rep_user_act_monthly
186 //per-project activity
187 $sql[]="DROP TABLE rep_group_act_daily";
188 $sql[]="CREATE TABLE rep_group_act_daily (
189 group_id int not null,
191 tracker_opened int not null,
192 tracker_closed int not null,
195 downloads int not null,
196 cvs_commits int not null,
197 tasks_opened int not null,
198 tasks_closed int not null,
199 PRIMARY KEY (group_id,day));";
201 $sql[]="DROP INDEX repgroupactdaily_day";
202 $sql[]="CREATE INDEX repgroupactdaily_day ON rep_group_act_daily(day)";
204 $sql[]="DROP TABLE rep_group_act_weekly";
205 $sql[]="CREATE TABLE rep_group_act_weekly (
206 group_id int not null,
208 tracker_opened int not null,
209 tracker_closed int not null,
212 downloads int not null,
213 cvs_commits int not null,
214 tasks_opened int not null,
215 tasks_closed int not null,
216 PRIMARY KEY (group_id,week));";
218 $sql[]="DROP INDEX repgroupactweekly_week";
219 $sql[]="CREATE INDEX repgroupactweekly_week ON rep_group_act_weekly(week)";
221 $sql[]="DROP TABLE rep_group_act_monthly";
222 $sql[]="CREATE TABLE rep_group_act_monthly (
223 group_id int not null,
225 tracker_opened int not null,
226 tracker_closed int not null,
229 downloads int not null,
230 cvs_commits int not null,
231 tasks_opened int not null,
232 tasks_closed int not null,
233 PRIMARY KEY (group_id,month));";
235 $sql[]="DROP INDEX repgroupactmonthly_month";
236 $sql[]="CREATE INDEX repgroupactmonthly_month ON rep_group_act_monthly(month)";
238 $sql[]="DROP VIEW rep_group_act_oa_vw";
239 $sql[]="CREATE VIEW rep_group_act_oa_vw AS
241 sum(tracker_opened) AS tracker_opened,
242 sum(tracker_closed) AS tracker_closed,
245 sum(downloads) AS downloads,
246 sum(cvs_commits) AS cvs_commits,
247 sum(tasks_opened) AS tasks_opened,
248 sum(tasks_closed) AS tasks_closed
249 FROM rep_group_act_monthly
253 $sql[]="DROP VIEW rep_site_act_daily_vw";
254 $sql[]="CREATE VIEW rep_site_act_daily_vw AS
256 sum(tracker_opened) AS tracker_opened,
257 sum(tracker_closed) AS tracker_closed,
260 sum(downloads) AS downloads,
261 sum(cvs_commits) AS cvs_commits,
262 sum(tasks_opened) AS tasks_opened,
263 sum(tasks_closed) AS tasks_closed
264 FROM rep_group_act_daily
267 $sql[]="DROP VIEW rep_site_act_weekly_vw";
268 $sql[]="CREATE VIEW rep_site_act_weekly_vw AS
270 sum(tracker_opened) AS tracker_opened,
271 sum(tracker_closed) AS tracker_closed,
274 sum(downloads) AS downloads,
275 sum(cvs_commits) AS cvs_commits,
276 sum(tasks_opened) AS tasks_opened,
277 sum(tasks_closed) AS tasks_closed
278 FROM rep_group_act_weekly
281 $sql[]="DROP VIEW rep_site_act_monthly_vw";
282 $sql[]="CREATE VIEW rep_site_act_monthly_vw AS
284 sum(tracker_opened) AS tracker_opened,
285 sum(tracker_closed) AS tracker_closed,
288 sum(downloads) AS downloads,
289 sum(cvs_commits) AS cvs_commits,
290 sum(tasks_opened) AS tasks_opened,
291 sum(tasks_closed) AS tasks_closed
292 FROM rep_group_act_monthly
295 $sql[]="DROP VIEW rep_site_act_oa_vw";
296 $sql[]="CREATE VIEW rep_site_act_oa_vw AS
297 sum(tracker_opened) AS tracker_opened,
298 sum(tracker_closed) AS tracker_closed,
301 sum(downloads) AS downloads,
302 sum(cvs_commits) AS cvs_commits,
303 sum(tasks_opened) AS tasks_opened,
304 sum(tasks_closed) AS tasks_closed
305 FROM rep_group_act_monthly;";
307 for ($i=0; $i<count($sql); $i++) {
309 $res=db_query_params($sql[$i], array());
315 function initialData() {
316 if (!$this->backfill_users_added_daily()) {
319 if (!$this->backfill_users_added_weekly()) {
322 if (!$this->backfill_users_added_monthly()) {
325 if (!$this->backfill_users_cum_daily()) {
328 if (!$this->backfill_users_cum_weekly()) {
331 if (!$this->backfill_users_cum_monthly()) {
334 if (!$this->backfill_groups_added_daily()) {
337 if (!$this->backfill_groups_added_weekly()) {
340 if (!$this->backfill_groups_added_monthly()) {
343 if (!$this->backfill_groups_cum_daily()) {
346 if (!$this->backfill_groups_cum_weekly()) {
349 if (!$this->backfill_groups_cum_monthly()) {
352 if (!$this->backfill_user_act_daily()) {
355 if (!$this->backfill_user_act_weekly()) {
358 if (!$this->backfill_user_act_monthly()) {
361 if (!$this->backfill_group_act_daily()) {
364 if (!$this->backfill_group_act_weekly()) {
367 if (!$this->backfill_group_act_monthly()) {
374 function dailyData() {
375 if (!$this->backfill_users_added_daily(1)) {
378 if (!$this->backfill_users_added_weekly(1)) {
381 if (!$this->backfill_users_added_monthly(2)) {
384 if (!$this->backfill_users_cum_daily(1)) {
387 if (!$this->backfill_users_cum_weekly(1)) {
390 if (!$this->backfill_users_cum_monthly(2)) {
393 if (!$this->backfill_user_act_daily(1)) {
396 if (!$this->backfill_user_act_weekly(1)) {
399 if (!$this->backfill_user_act_monthly(2)) {
402 if (!$this->backfill_group_act_daily(1)) {
405 if (!$this->backfill_group_act_weekly(1)) {
408 if (!$this->backfill_group_act_monthly(2)) {
414 * Add a row to the users_added_daily report table.
416 * @param int Day - the unix time of the beginning of the day.
417 * @return boolean Success.
419 function users_added_daily($day) {
420 db_query_params ('DELETE FROM rep_users_added_daily WHERE day=$1',
424 return db_query_params ('INSERT INTO rep_users_added_daily (day,added)
425 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
426 BETWEEN $3 AND $4 ))',
430 ($day + REPORT_DAY_SPAN - 1) ));
434 * Populate the users_added_daily report table.
436 * @return boolean Success.
438 function backfill_users_added_daily($count=10000) {
439 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
440 if (!$start_date=$this->getMinDate()) {
441 $this->setError('backfill_users_added_daily:: Could Not Get Start Date');
446 $day=($today-($i*REPORT_DAY_SPAN));
447 if (!$this->users_added_daily($day)) {
448 $this->setError('backfill_users_added_daily:: Error adding daily row: '.db_error());
451 if ($day < $start_date) {
463 * Add a row to the groups_added_daily report table.
465 * @param int Day - the unix time of the beginning of the day.
466 * @return boolean Success.
468 function groups_added_daily($day) {
469 db_query_params ('DELETE FROM rep_groups_added_daily WHERE day=$1',
473 return db_query_params ('INSERT INTO rep_groups_added_daily (day,added)
474 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
475 BETWEEN $3 AND $4 ))',
479 ($day + REPORT_DAY_SPAN - 1) ));
483 * Populate the groups_added_daily report table.
485 * @return boolean Success.
487 function backfill_groups_added_daily($count=10000) {
488 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
489 if (!$start_date=$this->getMinDate()) {
490 $this->setError('backfill_groups_added_daily:: Could Not Get Start Date');
495 $day=($today-($i*REPORT_DAY_SPAN));
496 if (!$this->groups_added_daily($day)) {
497 $this->setError('backfill_groups_added_daily:: Error adding daily row: '.db_error());
500 if ($day < $start_date) {
512 * Add a row to the users_added_weekly report table.
514 * @param int Week - the unix time of the beginning of the sunday for this week.
515 * @return boolean Success.
517 function users_added_weekly($week) {
518 db_query_params ('DELETE FROM rep_users_added_weekly WHERE week=$1',
522 return db_query_params ('INSERT INTO rep_users_added_weekly (week,added)
523 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
524 BETWEEN $3 AND $4 ))',
528 ($week+REPORT_WEEK_SPAN-1) ));
532 * Populate the users_added_weekly report table.
534 * @return boolean Success.
536 function backfill_users_added_weekly($count=10000) {
538 $arr =& $this->getWeekStartArr();
540 for ($i=0; $i<count($arr); $i++) {
541 if (!$this->users_added_weekly($arr[$i])) {
542 $this->setError('backfill_users_added_weekly:: Error adding weekly row: '.db_error());
553 * Add a row to the groups_added_weekly report table.
555 * @param int Week - the unix time of the beginning of the sunday for this week.
556 * @return boolean Success.
558 function groups_added_weekly($week) {
559 db_query_params ('DELETE FROM rep_groups_added_weekly WHERE week=$1',
563 return db_query_params ('INSERT INTO rep_groups_added_weekly (week,added)
564 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
565 BETWEEN $3 AND $4 ))',
569 ($week+REPORT_WEEK_SPAN-1) ));
573 * Populate the users_added_weekly report table.
575 * @return boolean Success.
577 function backfill_groups_added_weekly($count=10000) {
579 $arr =& $this->getWeekStartArr();
581 for ($i=0; $i<count($arr); $i++) {
582 if (!$this->groups_added_weekly($arr[$i])) {
583 $this->setError('backfill_groups_added_weekly:: Error adding weekly row: '.db_error());
594 * Add a row to the users_added_monthly report table.
596 * @param int month_start - the unix time of the beginning of the month.
597 * @param int month_end - the unix time of the end of the month.
598 * @return boolean Success.
600 function users_added_monthly($month,$end) {
601 db_query_params ('DELETE FROM rep_users_added_monthly WHERE month=$1',
605 return db_query_params ('INSERT INTO rep_users_added_monthly (month,added)
606 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
607 BETWEEN $3 AND $4 ))',
615 * Populate the users_added_monthly report table.
617 * @return boolean Success.
619 function backfill_users_added_monthly($count=10000) {
621 $arr =& $this->getMonthStartArr();
624 for ($i=1; $i<count($arr); $i++) {
625 if (!$this->users_added_monthly($arr[$i],($arr[$i-1]-1))) {
626 $this->setError('backfill_users_added_monthly:: Error adding monthly row: '.db_error());
637 * Add a row to the groups_added_monthly report table.
639 * @param int month_start - the unix time of the beginning of the month.
640 * @param int month_end - the unix time of the end of the month.
641 * @return boolean Success.
643 function groups_added_monthly($month,$end) {
644 db_query_params ('DELETE FROM rep_groups_added_monthly WHERE month=$1',
648 return db_query_params ('INSERT INTO rep_groups_added_monthly (month,added)
649 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
650 BETWEEN $3 AND $4 ))',
658 * Populate the groups_added_monthly report table.
660 * @return boolean Success.
662 function backfill_groups_added_monthly($count=10000) {
664 $arr =& $this->getMonthStartArr();
667 for ($i=1; $i<count($arr); $i++) {
668 if (!$this->groups_added_monthly($arr[$i],($arr[$i-1]-1))) {
669 $this->setError('backfill_groups_added_monthly:: Error adding monthly row: '.db_error());
680 // ******************************
684 * Add a row to the users_cum_daily report table.
686 * @param int Day - the unix time of the beginning of the day.
687 * @return boolean Success.
689 function users_cum_daily($day) {
690 db_query_params ('DELETE FROM rep_users_cum_daily WHERE day=$1',
694 return db_query_params ('INSERT INTO rep_users_cum_daily (day,total)
695 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
703 * Populate the users_cum_daily report table.
705 * @return boolean Success.
707 function backfill_users_cum_daily($count=10000) {
708 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
709 if (!$start_date=$this->getMinDate()) {
710 $this->setError('backfill_users_cum_daily:: Could Not Get Start Date');
715 $day=$today-($i*REPORT_DAY_SPAN);
716 if (!$this->users_cum_daily($day)) {
717 $this->setError('backfill_users_cum_daily:: Error adding daily row: '.db_error());
720 if ($day < $start_date) {
732 * Add a row to the groups_cum_daily report table.
734 * @param int Day - the unix time of the beginning of the day.
735 * @return boolean Success.
737 function groups_cum_daily($day) {
738 db_query_params ('DELETE FROM rep_groups_cum_daily WHERE day=$1',
742 return db_query_params ('INSERT INTO rep_groups_cum_daily (day,total)
743 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
751 * Populate the groups_cum_daily report table.
753 * @return boolean Success.
755 function backfill_groups_cum_daily($count=10000) {
756 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
757 if (!$start_date=$this->getMinDate()) {
758 $this->setError('backfill_groups_cum_daily:: Could Not Get Start Date');
763 $day=$today-($i*REPORT_DAY_SPAN);
764 if (!$this->groups_cum_daily($day)) {
765 $this->setError('backfill_groups_cum_daily:: Error adding daily row: '.db_error());
768 if ($day < $start_date) {
780 * Add a row to the users_cum_weekly report table.
782 * @param int Week - the unix time of the beginning of the sunday for this week.
783 * @return boolean Success.
785 function users_cum_weekly($week) {
786 db_query_params ('DELETE FROM rep_users_cum_weekly WHERE week=$1',
790 return db_query_params ('INSERT INTO rep_users_cum_weekly (week,total)
791 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
792 BETWEEN $3 AND $4))',
796 ($week+REPORT_WEEK_SPAN-1 )));
800 * Populate the users_cum_weekly report table.
802 * @return boolean Success.
804 function backfill_users_cum_weekly($count=10000) {
806 $arr =& $this->getWeekStartArr();
808 for ($i=0; $i<count($arr); $i++) {
809 if (!$this->groups_cum_weekly($arr[$i])) {
810 $this->setError('backfill_users_cum_weekly:: Error adding weekly row: '.db_error());
821 * Add a row to the groups_cum_weekly report table.
823 * @param int Week - the unix time of the beginning of the sunday for this week.
824 * @return boolean Success.
826 function groups_cum_weekly($week) {
827 db_query_params ('DELETE FROM rep_groups_cum_weekly WHERE week=$1',
831 return db_query_params ('INSERT INTO rep_groups_cum_weekly (week,total)
832 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
833 BETWEEN $3 AND $4))',
837 ($week+REPORT_WEEK_SPAN-1 )));
841 * Populate the groups_cum_weekly report table.
843 * @return boolean Success.
845 function backfill_groups_cum_weekly($count=10000) {
847 $arr =& $this->getWeekStartArr();
849 for ($i=0; $i<count($arr); $i++) {
850 if (!$this->users_cum_weekly($arr[$i])) {
851 $this->setError('backfill_groups_cum_weekly:: Error adding weekly row: '.db_error());
862 * Add a row to the users_cum_monthly report table.
864 * @param int month_start - the unix time of the beginning of the month.
865 * @param int month_end - the unix time of the end of the month.
866 * @return boolean Success.
868 function users_cum_monthly($month,$end) {
869 db_query_params ('DELETE FROM rep_users_cum_monthly WHERE month=$1',
873 return db_query_params ('INSERT INTO rep_users_cum_monthly (month,total)
874 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
882 * Populate the users_cum_monthly report table.
884 * @return boolean Success.
886 function backfill_users_cum_monthly($count=10000) {
888 $arr =& $this->getMonthStartArr();
891 for ($i=1; $i<count($arr); $i++) {
892 if (!$this->users_cum_monthly($arr[$i],($arr[$i-1]-1))) {
893 $this->setError('backfill_users_cum_monthly:: Error adding monthly row: '.db_error());
904 * Add a row to the groups_cum_monthly report table.
906 * @param int month_start - the unix time of the beginning of the month.
907 * @param int month_end - the unix time of the end of the month.
908 * @return boolean Success.
910 function groups_cum_monthly($month,$end) {
911 db_query_params ('DELETE FROM rep_groups_cum_monthly WHERE month=$1',
915 return db_query_params ('INSERT INTO rep_groups_cum_monthly (month,total)
916 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
924 * Populate the groups_cum_monthly report table.
926 * @return boolean Success.
928 function backfill_groups_cum_monthly($count=10000) {
930 $arr =& $this->getMonthStartArr();
933 for ($i=1; $i<count($arr); $i++) {
934 if (!$this->groups_cum_monthly($arr[$i],($arr[$i-1]-1))) {
935 $this->setError('backfill_groups_cum_monthly:: Error adding monthly row: '.db_error());
946 // ************************
950 * Add a row to the user_act_daily report table.
952 * @param int Day - the unix time of the beginning of the day.
953 * @return boolean Success.
955 function user_act_daily($day) {
956 db_query_params ('DELETE FROM rep_user_act_daily WHERE day=$1',
959 $end_day=$day+REPORT_DAY_SPAN-1;
961 return db_query_params ('INSERT INTO rep_user_act_daily
962 SELECT user_id,day,coalesce(tracker_opened,0) AS tracker_opened,
963 coalesce(tracker_closed,0) AS tracker_closed,
964 coalesce(forum,0) AS forum,
965 coalesce(docs,0) AS docs,
966 coalesce(cvs_commits,0) AS cvs_commits,
967 coalesce(tasks_opened,0) AS tasks_opened,
968 coalesce(tasks_closed,0) AS tasks_closed
976 (SELECT submitted_by AS user_id, $1::int AS day, count(*) AS tracker_opened
978 WHERE open_date BETWEEN $1 AND $2
979 GROUP BY user_id,day) aopen
982 (SELECT assigned_to AS user_id, $1::int AS day, count(*) AS tracker_closed
984 WHERE close_date BETWEEN $1 AND $2
985 GROUP BY user_id,day ) aclosed USING (user_id,day)) foo1
988 (SELECT posted_by AS user_id, $1::int AS day, count(*) AS forum
990 WHERE post_date BETWEEN $1 AND $2
991 GROUP BY user_id,day ) forum USING (user_id,day)) foo2
994 (SELECT created_by AS user_id, $1::int AS day, count(*) AS docs
996 WHERE createdate BETWEEN $1 AND $2
997 GROUP BY user_id,day ) docs USING (user_id,day)) foo3
1000 (SELECT user_id, $1::int AS day, sum(commits) AS cvs_commits
1002 WHERE month=$3 AND day=$2
1003 GROUP BY user_id,day ) cvs USING (user_id,day)) foo4
1006 (SELECT created_by AS user_id, $1::int AS day, count(*) AS tasks_opened
1008 WHERE start_date BETWEEN $1 AND $2
1009 GROUP BY user_id,day ) topen USING (user_id,day)) foo5
1012 (SELECT mod_by AS user_id, $1::int AS day, count(*) AS tasks_closed
1013 FROM project_history
1014 WHERE mod_date BETWEEN $1 AND $2
1015 AND old_value=$4 AND field_name=$5
1016 GROUP BY user_id,day ) tclosed USING (user_id,day)) foo6',
1025 * Populate the user_act_daily report table.
1027 * @return boolean Success.
1029 function backfill_user_act_daily($count=10000) {
1030 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1031 if (!$start_date=$this->getMinDate()) {
1032 $this->setError('backfill_user_act_daily:: Could Not Get Start Date');
1037 $day=$today-($i*REPORT_DAY_SPAN);
1038 if (!$this->user_act_daily($day)) {
1039 $this->setError('backfill_user_act_daily:: Error adding daily row: '.db_error());
1042 if ($day < $start_date) {
1054 * Add a row to the user_act_weekly report table.
1056 * @param int Week - the unix time of the beginning of the sunday for this week.
1057 * @return boolean Success.
1059 function user_act_weekly($week) {
1060 db_query_params ('DELETE FROM rep_user_act_weekly WHERE week=$1',
1063 return db_query_params ('
1064 INSERT INTO rep_user_act_weekly (user_id, week, tracker_opened, tracker_closed,
1065 forum, docs, cvs_commits, tasks_opened, tasks_closed)
1066 SELECT user_id,$1::int AS week, sum(tracker_opened) AS tracker_opened,
1067 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1068 sum(docs) AS docs, sum(cvs_commits) AS cvs_commits,
1069 sum(tasks_opened) AS tasks_opened, sum(tasks_closed) AS tasks_closed
1070 FROM rep_user_act_daily
1071 WHERE DAY BETWEEN $1 AND $2
1072 GROUP BY user_id,week',
1074 $week+REPORT_WEEK_SPAN-1)) ;
1078 * Populate the user_act_weekly report table.
1080 * @return boolean Success.
1082 function backfill_user_act_weekly($count=10000) {
1084 $arr =& $this->getWeekStartArr();
1086 for ($i=0; $i<count($arr); $i++) {
1087 if (!$this->user_act_weekly($arr[$i])) {
1088 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1099 * Add a row to the user_act_monthly report table.
1101 * @param int month_start - the unix time of the beginning of the month.
1102 * @param int month_end - the unix time of the end of the month.
1103 * @return boolean Success.
1105 function user_act_monthly($month,$end) {
1106 db_query_params ('DELETE FROM rep_user_act_monthly WHERE month=$1',
1109 return db_query_params ('
1110 INSERT INTO rep_user_act_monthly (user_id, month, tracker_opened,
1111 tracker_closed, forum, docs, cvs_commits, tasks_opened, tasks_closed)
1112 SELECT user_id, $1::int AS month, sum(tracker_opened) AS tracker_opened,
1113 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1114 sum(docs) AS docs, sum(cvs_commits) AS cvs_commits,
1115 sum(tasks_opened) AS tasks_opened, sum(tasks_closed) AS tasks_closed
1116 FROM rep_user_act_daily
1117 WHERE DAY BETWEEN $1 AND $2
1118 GROUP BY user_id, month',
1119 array ($month, $end)) ;
1123 * Populate the user_act_monthly report table.
1125 * @return boolean Success.
1127 function backfill_user_act_monthly($count=10000) {
1129 $arr =& $this->getMonthStartArr();
1131 for ($i=1; $i<count($arr); $i++) {
1132 if (!$this->user_act_monthly($arr[$i],($arr[$i-1]-1))) {
1133 $this->setError('backfill_user_act_monthly:: Error adding monthly row: '.db_error());
1143 // ************************
1147 * Add a row to the group_act_daily report table.
1149 * @param int Day - the unix time of the beginning of the day.
1150 * @return boolean Success.
1152 function group_act_daily($day) {
1153 db_query_params ('DELETE FROM rep_group_act_daily WHERE day=$1',
1156 $end_day=$day+REPORT_DAY_SPAN-1;
1158 return db_query_params ('INSERT INTO rep_group_act_daily
1159 SELECT group_id,day,coalesce(tracker_opened,0) AS tracker_opened,
1160 coalesce(tracker_closed,0) AS tracker_closed,
1161 coalesce(forum,0) AS forum,
1162 coalesce(docs,0) AS docs,
1163 coalesce(downloads,0) AS downloads,
1164 coalesce(cvs_commits,0) AS cvs_commits,
1165 coalesce(tasks_opened,0) AS tasks_opened,
1166 coalesce(tasks_closed,0) AS tasks_closed
1175 (SELECT agl.group_id, $1::int AS day, count(*) AS tracker_opened
1176 FROM artifact a, artifact_group_list agl
1177 WHERE a.open_date BETWEEN $1 AND $2
1178 AND a.group_artifact_id=agl.group_artifact_id
1179 GROUP BY group_id,day) aopen
1182 (SELECT agl.group_id, $1::int AS day, count(*) AS tracker_closed
1183 FROM artifact a, artifact_group_list agl
1184 WHERE a.close_date BETWEEN $1 AND $2
1185 AND a.group_artifact_id=agl.group_artifact_id
1186 GROUP BY group_id,day ) aclosed USING (group_id,day)) foo1
1189 (SELECT fgl.group_id, $1::int AS day, count(*) AS forum
1190 FROM forum f, forum_group_list fgl
1191 WHERE f.post_date BETWEEN $1 AND $2
1192 AND f.group_forum_id=fgl.group_forum_id
1193 GROUP BY group_id,day ) forum USING (group_id,day)) foo2
1196 (SELECT group_id, $1::int AS day, count(*) AS docs
1198 WHERE createdate BETWEEN $1 AND $2
1199 GROUP BY group_id,day ) docs USING (group_id,day)) foo3
1202 (SELECT fp.group_id, $1::int AS day, count(*) AS downloads
1203 FROM frs_package fp, frs_release fr, frs_file ff, frs_dlstats_file fdf
1204 WHERE fp.package_id=fr.package_id
1205 AND fr.release_id=ff.release_id
1206 AND ff.file_id=fdf.file_id
1207 AND fdf.month = $3 AND fdf.day = $4
1208 GROUP BY fp.group_id,day ) docs USING (group_id,day)) foo4
1211 (SELECT group_id, $1::int AS day, sum(commits) AS cvs_commits
1212 FROM stats_cvs_group
1213 WHERE month=$3 AND day=$4
1214 GROUP BY group_id,day ) cvs USING (group_id,day)) foo5
1217 (SELECT pgl.group_id, $1::int AS day,count(*) AS tasks_opened
1218 FROM project_task pt, project_group_list pgl
1219 WHERE pt.start_date BETWEEN $1 AND $2
1220 AND pt.group_project_id=pgl.group_project_id
1221 GROUP BY group_id,day ) topen USING (group_id,day)) foo6
1224 (SELECT pgl.group_id, $1::int AS day, count(*) AS tasks_closed
1225 FROM project_history ph, project_task pt, project_group_list pgl
1226 WHERE ph.mod_date BETWEEN $1 AND $2
1228 AND ph.field_name=$6
1229 AND ph.project_task_id=pt.project_task_id
1230 AND pt.group_project_id=pgl.group_project_id
1231 GROUP BY group_id,day ) tclosed USING (group_id,day)) foo7',
1243 * Populate the group_act_daily report table.
1245 * @return boolean Success.
1247 function backfill_group_act_daily($count=10000) {
1248 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1249 if (!$start_date=$this->getMinDate()) {
1250 $this->setError('backfill_group_act_daily:: Could Not Get Start Date');
1255 $day=$today-($i*REPORT_DAY_SPAN);
1256 if (!$this->group_act_daily($day)) {
1257 $this->setError('backfill_group_act_daily:: Error adding daily row: '.db_error());
1260 if ($day < $start_date) {
1272 * Add a row to the group_act_weekly report table.
1274 * @param int Week - the unix time of the beginning of the sunday for this week.
1275 * @return boolean Success.
1277 function group_act_weekly($week) {
1278 db_query_params ('DELETE FROM rep_group_act_weekly WHERE week=$1',
1281 return db_query_params ('
1282 INSERT INTO rep_group_act_weekly (group_id, week, tracker_opened,
1283 tracker_closed, forum, docs, downloads, cvs_commits, tasks_opened,
1285 SELECT group_id, $1::int AS week, sum(tracker_opened) AS tracker_opened,
1286 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1287 sum(docs) AS docs, sum(downloads) AS downloads,
1288 sum(cvs_commits) AS cvs_commits, sum(tasks_opened) AS tasks_opened,
1289 sum(tasks_closed) AS tasks_closed
1290 FROM rep_group_act_daily
1291 WHERE DAY BETWEEN $1 AND $2
1292 GROUP BY group_id, week',
1294 $week+REPORT_WEEK_SPAN-1)) ;
1298 * Populate the group_act_weekly report table.
1300 * @return boolean Success.
1302 function backfill_group_act_weekly($count=10000) {
1304 $arr =& $this->getWeekStartArr();
1306 for ($i=0; $i<count($arr); $i++) {
1307 if (!$this->group_act_weekly($arr[$i])) {
1308 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1319 * Add a row to the group_act_monthly report table.
1321 * @param int month_start - the unix time of the beginning of the month.
1322 * @param int month_end - the unix time of the end of the month.
1323 * @return boolean Success.
1325 function group_act_monthly($month,$end) {
1326 db_query_params ('DELETE FROM rep_group_act_monthly WHERE month=$1',
1329 return db_query_params ('
1330 INSERT INTO rep_group_act_monthly (group_id, month, tracker_opened,
1331 tracker_closed, forum, docs, downloads, cvs_commits, tasks_opened,
1333 SELECT group_id, $1::int AS month, sum(tracker_opened) AS tracker_opened,
1334 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1335 sum(docs) AS docs, sum(downloads) AS downloads,
1336 sum(cvs_commits) AS cvs_commits,
1337 sum(tasks_opened) AS tasks_opened,
1338 sum(tasks_closed) AS tasks_closed
1339 FROM rep_group_act_daily
1340 WHERE DAY BETWEEN $1 AND $2
1341 GROUP BY group_id,month',
1342 array ($month, $end)) ;
1346 * Populate the group_act_monthly report table.
1348 * @return boolean Success.
1350 function backfill_group_act_monthly($count=10000) {
1352 $arr =& $this->getMonthStartArr();
1354 for ($i=1; $i<count($arr); $i++) {
1355 if (!$this->group_act_monthly($arr[$i],($arr[$i-1]-1))) {
1356 $this->setError('backfill_group_act_monthly:: Error adding monthly row: '.db_error());
1367 * Add a row to the rep_time_category table.
1369 * @param string The category name.
1370 * @return boolean Success.
1372 function addTimeCode($category_name) {
1373 return db_query_params ('INSERT INTO rep_time_category (category_name) VALUES ($1)',
1374 array($category_name)) ;
1378 * Update the rep_time_category table.
1380 * @param string The category name.
1381 * @return boolean Success.
1383 function updateTimeCode($time_code, $category_name) {
1384 return db_query_params ('UPDATE rep_time_category SET category_name=$1 WHERE time_code=$2',
1385 array($category_name,
1393 // c-file-style: "bsd"