5 * Copyright 2004 (c) GForge LLC
8 * @author Tim Perdue tim@gforge.org
11 * This file is part of GForge.
13 * GForge is free software; you can redistribute it and/or modify
14 * it under the terms of the GNU General Public License as published by
15 * the Free Software Foundation; either version 2 of the License, or
16 * (at your option) any later version.
18 * GForge is distributed in the hope that it will be useful,
19 * but WITHOUT ANY WARRANTY; without even the implied warranty of
20 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21 * GNU General Public License for more details.
23 * You should have received a copy of the GNU General Public License
24 * along with GForge; if not, write to the Free Software
25 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
28 require_once('common/reporting/Report.class');
30 class ReportSetup extends Report {
32 function ReportSetup() {
37 function initialSetup() {
38 $this->createTables();
39 if (!$this->initialData()) {
46 function createTables() {
49 //DROP TABLE rep_time_category;
50 $sql[]="CREATE TABLE rep_time_category (
51 time_code serial UNIQUE,
54 //$sql[]="DROP TABLE rep_time_tracking;";
55 $sql[]="CREATE TABLE rep_time_tracking (
57 report_date int not null,
59 project_task_id int not null,
60 time_code int not null CONSTRAINT reptimetrk_timecode REFERENCES rep_time_category(time_code),
63 // $sql[]="CREATE UNIQUE INDEX reptimetrk_weekusrtskcde ON
64 // rep_time_tracking (week,user_id,project_task_id,time_code);";
65 $sql[]="CREATE INDEX reptimetracking_userdate ON
66 rep_time_tracking (user_id,week);";
68 $sql[]="INSERT INTO rep_time_category VALUES ('1','Coding');";
69 $sql[]="INSERT INTO rep_time_category VALUES ('2','Testing');";
70 $sql[]="INSERT INTO rep_time_category VALUES ('3','Meeting');";
71 $sql[]="SELECT setval('rep_time_category_time_code_seq',(SELECT max(time_code) FROM rep_time_category));";
74 $sql[]="DROP TABLE rep_users_added_daily;";
75 $sql[]="CREATE TABLE rep_users_added_daily (
76 day int not null primary key,
77 added int not null default 0);";
79 $sql[]="DROP TABLE rep_users_added_weekly";
80 $sql[]="CREATE TABLE rep_users_added_weekly (
81 week int not null primary key,
82 added int not null default 0);";
84 $sql[]="DROP TABLE rep_users_added_monthly";
85 $sql[]="CREATE TABLE rep_users_added_monthly (
86 month int not null primary key,
87 added int not null default 0);";
90 $sql[]="DROP TABLE rep_users_cum_daily";
91 $sql[]="CREATE TABLE rep_users_cum_daily (
92 day int not null primary key,
93 total int not null default 0);";
95 $sql[]="DROP TABLE rep_users_cum_weekly";
96 $sql[]="CREATE TABLE rep_users_cum_weekly (
97 week int not null primary key,
98 total int not null default 0);";
100 $sql[]="DROP TABLE rep_users_cum_monthly";
101 $sql[]="CREATE TABLE rep_users_cum_monthly (
102 month int not null primary key,
103 total int not null default 0);";
106 $sql[]="DROP TABLE rep_groups_added_daily;";
107 $sql[]="CREATE TABLE rep_groups_added_daily (
108 day int not null primary key,
109 added int not null default 0);";
111 $sql[]="DROP TABLE rep_groups_added_weekly";
112 $sql[]="CREATE TABLE rep_groups_added_weekly (
113 week int not null primary key,
114 added int not null default 0);";
116 $sql[]="DROP TABLE rep_groups_added_monthly";
117 $sql[]="CREATE TABLE rep_groups_added_monthly (
118 month int not null primary key,
119 added int not null default 0);";
122 $sql[]="DROP TABLE rep_groups_cum_daily";
123 $sql[]="CREATE TABLE rep_groups_cum_daily (
124 day int not null primary key,
125 total int not null default 0);";
127 $sql[]="DROP TABLE rep_groups_cum_weekly";
128 $sql[]="CREATE TABLE rep_groups_cum_weekly (
129 week int not null primary key,
130 total int not null default 0);";
132 $sql[]="DROP TABLE rep_groups_cum_monthly";
133 $sql[]="CREATE TABLE rep_groups_cum_monthly (
134 month int not null primary key,
135 total int not null default 0);";
138 $sql[]="DROP TABLE rep_user_act_daily";
139 $sql[]="CREATE TABLE rep_user_act_daily (
140 user_id int not null,
142 tracker_opened int not null,
143 tracker_closed int not null,
146 cvs_commits int not null,
147 tasks_opened int not null,
148 tasks_closed int not null,
149 PRIMARY KEY (user_id,day));";
151 $sql[]="DROP TABLE rep_user_act_weekly";
152 $sql[]="CREATE TABLE rep_user_act_weekly (
153 user_id int not null,
155 tracker_opened int not null,
156 tracker_closed int not null,
159 cvs_commits int not null,
160 tasks_opened int not null,
161 tasks_closed int not null,
162 PRIMARY KEY (user_id,week));";
164 $sql[]="DROP TABLE rep_user_act_monthly";
165 $sql[]="CREATE TABLE rep_user_act_monthly (
166 user_id int not null,
168 tracker_opened int not null,
169 tracker_closed int not null,
172 cvs_commits int not null,
173 tasks_opened int not null,
174 tasks_closed int not null,
175 PRIMARY KEY (user_id,month));";
177 $sql[]="DROP VIEW rep_user_act_oa_vw";
178 $sql[]="CREATE VIEW rep_user_act_oa_vw AS
180 sum(tracker_opened) AS tracker_opened,
181 sum(tracker_closed) AS tracker_closed,
184 sum(cvs_commits) AS cvs_commits,
185 sum(tasks_opened) AS tasks_opened,
186 sum(tasks_closed) AS tasks_closed
187 FROM rep_user_act_monthly
190 //per-project activity
191 $sql[]="DROP TABLE rep_group_act_daily";
192 $sql[]="CREATE TABLE rep_group_act_daily (
193 group_id int not null,
195 tracker_opened int not null,
196 tracker_closed int not null,
199 downloads int not null,
200 cvs_commits int not null,
201 tasks_opened int not null,
202 tasks_closed int not null,
203 PRIMARY KEY (group_id,day));";
205 $sql[]="DROP INDEX repgroupactdaily_day";
206 $sql[]="CREATE INDEX repgroupactdaily_day ON rep_group_act_daily(day)";
208 $sql[]="DROP TABLE rep_group_act_weekly";
209 $sql[]="CREATE TABLE rep_group_act_weekly (
210 group_id int not null,
212 tracker_opened int not null,
213 tracker_closed int not null,
216 downloads int not null,
217 cvs_commits int not null,
218 tasks_opened int not null,
219 tasks_closed int not null,
220 PRIMARY KEY (group_id,week));";
222 $sql[]="DROP INDEX repgroupactweekly_week";
223 $sql[]="CREATE INDEX repgroupactweekly_week ON rep_group_act_weekly(week)";
225 $sql[]="DROP TABLE rep_group_act_monthly";
226 $sql[]="CREATE TABLE rep_group_act_monthly (
227 group_id int not null,
229 tracker_opened int not null,
230 tracker_closed int not null,
233 downloads int not null,
234 cvs_commits int not null,
235 tasks_opened int not null,
236 tasks_closed int not null,
237 PRIMARY KEY (group_id,month));";
239 $sql[]="DROP INDEX repgroupactmonthly_month";
240 $sql[]="CREATE INDEX repgroupactmonthly_month ON rep_group_act_monthly(month)";
242 $sql[]="DROP VIEW rep_group_act_oa_vw";
243 $sql[]="CREATE VIEW rep_group_act_oa_vw AS
245 sum(tracker_opened) AS tracker_opened,
246 sum(tracker_closed) AS tracker_closed,
249 sum(downloads) AS downloads,
250 sum(cvs_commits) AS cvs_commits,
251 sum(tasks_opened) AS tasks_opened,
252 sum(tasks_closed) AS tasks_closed
253 FROM rep_group_act_monthly
257 $sql[]="DROP VIEW rep_site_act_daily_vw";
258 $sql[]="CREATE VIEW rep_site_act_daily_vw AS
260 sum(tracker_opened) AS tracker_opened,
261 sum(tracker_closed) AS tracker_closed,
264 sum(downloads) AS downloads,
265 sum(cvs_commits) AS cvs_commits,
266 sum(tasks_opened) AS tasks_opened,
267 sum(tasks_closed) AS tasks_closed
268 FROM rep_group_act_daily
271 $sql[]="DROP VIEW rep_site_act_weekly_vw";
272 $sql[]="CREATE VIEW rep_site_act_weekly_vw AS
274 sum(tracker_opened) AS tracker_opened,
275 sum(tracker_closed) AS tracker_closed,
278 sum(downloads) AS downloads,
279 sum(cvs_commits) AS cvs_commits,
280 sum(tasks_opened) AS tasks_opened,
281 sum(tasks_closed) AS tasks_closed
282 FROM rep_group_act_weekly
285 $sql[]="DROP VIEW rep_site_act_monthly_vw";
286 $sql[]="CREATE VIEW rep_site_act_monthly_vw AS
288 sum(tracker_opened) AS tracker_opened,
289 sum(tracker_closed) AS tracker_closed,
292 sum(downloads) AS downloads,
293 sum(cvs_commits) AS cvs_commits,
294 sum(tasks_opened) AS tasks_opened,
295 sum(tasks_closed) AS tasks_closed
296 FROM rep_group_act_monthly
299 $sql[]="DROP VIEW rep_site_act_oa_vw";
300 $sql[]="CREATE VIEW rep_site_act_oa_vw AS
301 sum(tracker_opened) AS tracker_opened,
302 sum(tracker_closed) AS tracker_closed,
305 sum(downloads) AS downloads,
306 sum(cvs_commits) AS cvs_commits,
307 sum(tasks_opened) AS tasks_opened,
308 sum(tasks_closed) AS tasks_closed
309 FROM rep_group_act_monthly;";
311 for ($i=0; $i<count($sql); $i++) {
313 $res=db_query($sql[$i]);
319 function initialData() {
320 if (!$this->backfill_users_added_daily()) {
323 if (!$this->backfill_users_added_weekly()) {
326 if (!$this->backfill_users_added_monthly()) {
329 if (!$this->backfill_users_cum_daily()) {
332 if (!$this->backfill_users_cum_weekly()) {
335 if (!$this->backfill_users_cum_monthly()) {
338 if (!$this->backfill_groups_added_daily()) {
341 if (!$this->backfill_groups_added_weekly()) {
344 if (!$this->backfill_groups_added_monthly()) {
347 if (!$this->backfill_groups_cum_daily()) {
350 if (!$this->backfill_groups_cum_weekly()) {
353 if (!$this->backfill_groups_cum_monthly()) {
356 if (!$this->backfill_user_act_daily()) {
359 if (!$this->backfill_user_act_weekly()) {
362 if (!$this->backfill_user_act_monthly()) {
365 if (!$this->backfill_group_act_daily()) {
368 if (!$this->backfill_group_act_weekly()) {
371 if (!$this->backfill_group_act_monthly()) {
378 function dailyData() {
379 if (!$this->backfill_users_added_daily(1)) {
382 if (!$this->backfill_users_added_weekly(1)) {
385 if (!$this->backfill_users_added_monthly(2)) {
388 if (!$this->backfill_users_cum_daily(1)) {
391 if (!$this->backfill_users_cum_weekly(1)) {
394 if (!$this->backfill_users_cum_monthly(2)) {
397 if (!$this->backfill_user_act_daily(1)) {
400 if (!$this->backfill_user_act_weekly(1)) {
403 if (!$this->backfill_user_act_monthly(2)) {
406 if (!$this->backfill_group_act_daily(1)) {
409 if (!$this->backfill_group_act_weekly(1)) {
412 if (!$this->backfill_group_act_monthly(2)) {
418 * Add a row to the users_added_daily report table.
420 * @param int Day - the unix time of the beginning of the day.
421 * @return boolean Success.
423 function users_added_daily($day) {
424 db_query("DELETE FROM rep_users_added_daily WHERE day='$day'");
426 $sql="INSERT INTO rep_users_added_daily (day,added)
427 VALUES ('$day',(SELECT count(*) FROM users WHERE status='A' AND add_date
428 BETWEEN '$day' AND '". ($day + REPORT_DAY_SPAN - 1) ."' ))";
429 return db_query($sql);
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("DELETE FROM rep_groups_added_daily WHERE day='$day'");
470 $sql="INSERT INTO rep_groups_added_daily (day,added)
471 VALUES ('$day',(SELECT count(*) FROM groups WHERE status='A' AND register_time
472 BETWEEN '$day' AND '". ($day + REPORT_DAY_SPAN - 1) ."' ))";
473 return db_query($sql);
477 * Populate the groups_added_daily report table.
479 * @return boolean Success.
481 function backfill_groups_added_daily($count=10000) {
482 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
483 if (!$start_date=$this->getMinDate()) {
484 $this->setError('backfill_groups_added_daily:: Could Not Get Start Date');
489 $day=($today-($i*REPORT_DAY_SPAN));
490 if (!$this->groups_added_daily($day)) {
491 $this->setError('backfill_groups_added_daily:: Error adding daily row: '.db_error());
494 if ($day < $start_date) {
506 * Add a row to the users_added_weekly report table.
508 * @param int Week - the unix time of the beginning of the sunday for this week.
509 * @return boolean Success.
511 function users_added_weekly($week) {
512 db_query("DELETE FROM rep_users_added_weekly WHERE week='$week'");
514 $sql="INSERT INTO rep_users_added_weekly (week,added)
515 VALUES ('$week',(SELECT count(*) FROM users WHERE status='A' AND add_date
516 BETWEEN '$week' AND '". ($week+REPORT_WEEK_SPAN-1) ."' ))";
517 return db_query($sql);
521 * Populate the users_added_weekly report table.
523 * @return boolean Success.
525 function backfill_users_added_weekly($count=10000) {
527 $arr =& $this->getWeekStartArr();
529 for ($i=0; $i<count($arr); $i++) {
530 if (!$this->users_added_weekly($arr[$i])) {
531 $this->setError('backfill_users_added_weekly:: Error adding weekly row: '.db_error());
542 * Add a row to the groups_added_weekly report table.
544 * @param int Week - the unix time of the beginning of the sunday for this week.
545 * @return boolean Success.
547 function groups_added_weekly($week) {
548 db_query("DELETE FROM rep_groups_added_weekly WHERE week='$week'");
550 $sql="INSERT INTO rep_groups_added_weekly (week,added)
551 VALUES ('$week',(SELECT count(*) FROM groups WHERE status='A' AND register_time
552 BETWEEN '$week' AND '". ($week+REPORT_WEEK_SPAN-1) ."' ))";
553 return db_query($sql);
557 * Populate the users_added_weekly report table.
559 * @return boolean Success.
561 function backfill_groups_added_weekly($count=10000) {
563 $arr =& $this->getWeekStartArr();
565 for ($i=0; $i<count($arr); $i++) {
566 if (!$this->groups_added_weekly($arr[$i])) {
567 $this->setError('backfill_groups_added_weekly:: Error adding weekly row: '.db_error());
578 * Add a row to the users_added_monthly report table.
580 * @param int month_start - the unix time of the beginning of the month.
581 * @param int month_end - the unix time of the end of the month.
582 * @return boolean Success.
584 function users_added_monthly($month,$end) {
585 db_query("DELETE FROM rep_users_added_monthly WHERE month='$month'");
587 $sql="INSERT INTO rep_users_added_monthly (month,added)
588 VALUES ('$month',(SELECT count(*) FROM users WHERE status='A' AND add_date
589 BETWEEN '$month' AND '$end' ))";
590 return db_query($sql);
594 * Populate the users_added_monthly report table.
596 * @return boolean Success.
598 function backfill_users_added_monthly($count=10000) {
600 $arr =& $this->getMonthStartArr();
603 for ($i=1; $i<count($arr); $i++) {
604 if (!$this->users_added_monthly($arr[$i],($arr[$i-1]-1))) {
605 $this->setError('backfill_users_added_monthly:: Error adding monthly row: '.db_error());
616 * Add a row to the groups_added_monthly report table.
618 * @param int month_start - the unix time of the beginning of the month.
619 * @param int month_end - the unix time of the end of the month.
620 * @return boolean Success.
622 function groups_added_monthly($month,$end) {
623 db_query("DELETE FROM rep_groups_added_monthly WHERE month='$month'");
625 $sql="INSERT INTO rep_groups_added_monthly (month,added)
626 VALUES ('$month',(SELECT count(*) FROM groups WHERE status='A' AND register_time
627 BETWEEN '$month' AND '$end' ))";
628 return db_query($sql);
632 * Populate the groups_added_monthly report table.
634 * @return boolean Success.
636 function backfill_groups_added_monthly($count=10000) {
638 $arr =& $this->getMonthStartArr();
641 for ($i=1; $i<count($arr); $i++) {
642 if (!$this->groups_added_monthly($arr[$i],($arr[$i-1]-1))) {
643 $this->setError('backfill_groups_added_monthly:: Error adding monthly row: '.db_error());
654 // ******************************
658 * Add a row to the users_cum_daily report table.
660 * @param int Day - the unix time of the beginning of the day.
661 * @return boolean Success.
663 function users_cum_daily($day) {
664 db_query("DELETE FROM rep_users_cum_daily WHERE day='$day'");
666 $sql="INSERT INTO rep_users_cum_daily (day,total)
667 VALUES ('$day',(SELECT count(*) FROM users WHERE status='A' AND add_date
668 BETWEEN '0' AND '$day'))";
669 return db_query($sql);
673 * Populate the users_cum_daily report table.
675 * @return boolean Success.
677 function backfill_users_cum_daily($count=10000) {
678 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
679 if (!$start_date=$this->getMinDate()) {
680 $this->setError('backfill_users_cum_daily:: Could Not Get Start Date');
685 $day=$today-($i*REPORT_DAY_SPAN);
686 if (!$this->users_cum_daily($day)) {
687 $this->setError('backfill_users_cum_daily:: Error adding daily row: '.db_error());
690 if ($day < $start_date) {
702 * Add a row to the groups_cum_daily report table.
704 * @param int Day - the unix time of the beginning of the day.
705 * @return boolean Success.
707 function groups_cum_daily($day) {
708 db_query("DELETE FROM rep_groups_cum_daily WHERE day='$day'");
710 $sql="INSERT INTO rep_groups_cum_daily (day,total)
711 VALUES ('$day',(SELECT count(*) FROM groups WHERE status='A' AND register_time
712 BETWEEN '0' AND '$day'))";
713 return db_query($sql);
717 * Populate the groups_cum_daily report table.
719 * @return boolean Success.
721 function backfill_groups_cum_daily($count=10000) {
722 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
723 if (!$start_date=$this->getMinDate()) {
724 $this->setError('backfill_groups_cum_daily:: Could Not Get Start Date');
729 $day=$today-($i*REPORT_DAY_SPAN);
730 if (!$this->groups_cum_daily($day)) {
731 $this->setError('backfill_groups_cum_daily:: Error adding daily row: '.db_error());
734 if ($day < $start_date) {
746 * Add a row to the users_cum_weekly report table.
748 * @param int Week - the unix time of the beginning of the sunday for this week.
749 * @return boolean Success.
751 function users_cum_weekly($week) {
752 db_query("DELETE FROM rep_users_cum_weekly WHERE week='$week'");
754 $sql="INSERT INTO rep_users_cum_weekly (week,total)
755 VALUES ('$week',(SELECT count(*) FROM users WHERE status='A' AND add_date
756 BETWEEN '0' AND '". ($week+REPORT_WEEK_SPAN-1 ). "'))";
757 return db_query($sql);
761 * Populate the users_cum_weekly report table.
763 * @return boolean Success.
765 function backfill_users_cum_weekly($count=10000) {
767 $arr =& $this->getWeekStartArr();
769 for ($i=0; $i<count($arr); $i++) {
770 if (!$this->groups_cum_weekly($arr[$i])) {
771 $this->setError('backfill_users_cum_weekly:: Error adding weekly row: '.db_error());
782 * Add a row to the groups_cum_weekly report table.
784 * @param int Week - the unix time of the beginning of the sunday for this week.
785 * @return boolean Success.
787 function groups_cum_weekly($week) {
788 db_query("DELETE FROM rep_groups_cum_weekly WHERE week='$week'");
790 $sql="INSERT INTO rep_groups_cum_weekly (week,total)
791 VALUES ('$week',(SELECT count(*) FROM groups WHERE status='A' AND register_time
792 BETWEEN '0' AND '". ($week+REPORT_WEEK_SPAN-1 ). "'))";
793 return db_query($sql);
797 * Populate the groups_cum_weekly report table.
799 * @return boolean Success.
801 function backfill_groups_cum_weekly($count=10000) {
803 $arr =& $this->getWeekStartArr();
805 for ($i=0; $i<count($arr); $i++) {
806 if (!$this->users_cum_weekly($arr[$i])) {
807 $this->setError('backfill_groups_cum_weekly:: Error adding weekly row: '.db_error());
818 * Add a row to the users_cum_monthly report table.
820 * @param int month_start - the unix time of the beginning of the month.
821 * @param int month_end - the unix time of the end of the month.
822 * @return boolean Success.
824 function users_cum_monthly($month,$end) {
825 db_query("DELETE FROM rep_users_cum_monthly WHERE month='$month'");
827 $sql="INSERT INTO rep_users_cum_monthly (month,total)
828 VALUES ('$month',(SELECT count(*) FROM users WHERE status='A' AND add_date
829 BETWEEN '0' AND '$end'))";
830 return db_query($sql);
834 * Populate the users_cum_monthly report table.
836 * @return boolean Success.
838 function backfill_users_cum_monthly($count=10000) {
840 $arr =& $this->getMonthStartArr();
843 for ($i=1; $i<count($arr); $i++) {
844 if (!$this->users_cum_monthly($arr[$i],($arr[$i-1]-1))) {
845 $this->setError('backfill_users_cum_monthly:: Error adding monthly row: '.db_error());
856 * Add a row to the groups_cum_monthly report table.
858 * @param int month_start - the unix time of the beginning of the month.
859 * @param int month_end - the unix time of the end of the month.
860 * @return boolean Success.
862 function groups_cum_monthly($month,$end) {
863 db_query("DELETE FROM rep_groups_cum_monthly WHERE month='$month'");
865 $sql="INSERT INTO rep_groups_cum_monthly (month,total)
866 VALUES ('$month',(SELECT count(*) FROM groups WHERE status='A' AND register_time
867 BETWEEN '0' AND '$end'))";
868 return db_query($sql);
872 * Populate the groups_cum_monthly report table.
874 * @return boolean Success.
876 function backfill_groups_cum_monthly($count=10000) {
878 $arr =& $this->getMonthStartArr();
881 for ($i=1; $i<count($arr); $i++) {
882 if (!$this->groups_cum_monthly($arr[$i],($arr[$i-1]-1))) {
883 $this->setError('backfill_groups_cum_monthly:: Error adding monthly row: '.db_error());
894 // ************************
898 * Add a row to the user_act_daily report table.
900 * @param int Day - the unix time of the beginning of the day.
901 * @return boolean Success.
903 function user_act_daily($day) {
905 db_query("DELETE FROM rep_user_act_daily WHERE day='$day'");
907 $sql="INSERT INTO rep_user_act_daily
908 SELECT user_id,day,coalesce(tracker_opened,0) AS tracker_opened,
909 coalesce(tracker_closed,0) AS tracker_closed,
910 coalesce(forum,0) AS forum,
911 coalesce(docs,0) AS docs,
912 coalesce(cvs_commits,0) AS cvs_commits,
913 coalesce(tasks_opened,0) AS tasks_opened,
914 coalesce(tasks_closed,0) AS tasks_closed
922 (SELECT submitted_by AS user_id, '$day'::int AS day, count(*) AS tracker_opened
924 WHERE open_date BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
925 GROUP BY user_id,day) aopen
928 (SELECT assigned_to AS user_id, '$day'::int AS day, count(*) AS tracker_closed
930 WHERE close_date BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
931 GROUP BY user_id,day ) aclosed USING (user_id,day)) foo1
934 (SELECT posted_by AS user_id, '$day'::int AS day, count(*) AS forum
936 WHERE post_date BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
937 GROUP BY user_id,day ) forum USING (user_id,day)) foo2
940 (SELECT created_by AS user_id, '$day'::int AS day, count(*) AS docs
942 WHERE createdate BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
943 GROUP BY user_id,day ) docs USING (user_id,day)) foo3
946 (SELECT user_id,$day AS day, sum(commits) AS cvs_commits
948 WHERE month='". date('Ym') ."' AND day='". date('d') ."'
949 GROUP BY user_id,day ) cvs USING (user_id,day)) foo4
952 (SELECT created_by AS user_id, '$day'::int AS day, count(*) AS tasks_opened
954 WHERE start_date BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
955 GROUP BY user_id,day ) topen USING (user_id,day)) foo5
958 (SELECT mod_by AS user_id, '$day'::int AS day, count(*) AS tasks_closed
960 WHERE mod_date BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
961 AND old_value='1' AND field_name='status_id'
962 GROUP BY user_id,day ) tclosed USING (user_id,day)) foo6";
964 return db_query($sql);
969 * Populate the user_act_daily report table.
971 * @return boolean Success.
973 function backfill_user_act_daily($count=10000) {
974 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
975 if (!$start_date=$this->getMinDate()) {
976 $this->setError('backfill_user_act_daily:: Could Not Get Start Date');
981 $day=$today-($i*REPORT_DAY_SPAN);
982 if (!$this->user_act_daily($day)) {
983 $this->setError('backfill_user_act_daily:: Error adding daily row: '.db_error());
986 if ($day < $start_date) {
998 * Add a row to the user_act_weekly report table.
1000 * @param int Week - the unix time of the beginning of the sunday for this week.
1001 * @return boolean Success.
1003 function user_act_weekly($week) {
1004 db_query("DELETE FROM rep_user_act_weekly WHERE week='$week'");
1006 $sql="INSERT INTO rep_user_act_weekly (user_id,week,tracker_opened,tracker_closed,
1007 forum,docs,cvs_commits,tasks_opened,tasks_closed)
1008 SELECT user_id,'$week'::int AS week, sum(tracker_opened) AS tracker_opened,
1009 sum(tracker_closed) AS tracker_closed,
1010 sum(forum) AS forum,
1012 sum(cvs_commits) AS cvs_commits,
1013 sum(tasks_opened) AS tasks_opened,
1014 sum(tasks_closed) AS tasks_closed
1015 FROM rep_user_act_daily
1017 BETWEEN '$week' AND '". ($week+REPORT_WEEK_SPAN-1) ."'
1018 GROUP BY user_id,week";
1019 return db_query($sql);
1023 * Populate the user_act_weekly report table.
1025 * @return boolean Success.
1027 function backfill_user_act_weekly($count=10000) {
1029 $arr =& $this->getWeekStartArr();
1031 for ($i=0; $i<count($arr); $i++) {
1032 if (!$this->user_act_weekly($arr[$i])) {
1033 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1044 * Add a row to the user_act_monthly report table.
1046 * @param int month_start - the unix time of the beginning of the month.
1047 * @param int month_end - the unix time of the end of the month.
1048 * @return boolean Success.
1050 function user_act_monthly($month,$end) {
1051 db_query("DELETE FROM rep_user_act_monthly WHERE month='$month'");
1053 $sql="INSERT INTO rep_user_act_monthly (user_id,month,tracker_opened,tracker_closed,
1054 forum,docs,cvs_commits,tasks_opened,tasks_closed)
1055 SELECT user_id,'$month'::int AS month, sum(tracker_opened) AS tracker_opened,
1056 sum(tracker_closed) AS tracker_closed,
1057 sum(forum) AS forum,
1059 sum(cvs_commits) AS cvs_commits,
1060 sum(tasks_opened) AS tasks_opened,
1061 sum(tasks_closed) AS tasks_closed
1062 FROM rep_user_act_daily
1064 BETWEEN '$month' AND '$end'
1065 GROUP BY user_id,month";
1066 return db_query($sql);
1070 * Populate the user_act_monthly report table.
1072 * @return boolean Success.
1074 function backfill_user_act_monthly($count=10000) {
1076 $arr =& $this->getMonthStartArr();
1078 for ($i=1; $i<count($arr); $i++) {
1079 if (!$this->user_act_monthly($arr[$i],($arr[$i-1]-1))) {
1080 $this->setError('backfill_user_act_monthly:: Error adding monthly row: '.db_error());
1090 // ************************
1094 * Add a row to the group_act_daily report table.
1096 * @param int Day - the unix time of the beginning of the day.
1097 * @return boolean Success.
1099 function group_act_daily($day) {
1101 db_query("DELETE FROM rep_group_act_daily WHERE day='$day'");
1103 $sql="INSERT INTO rep_group_act_daily
1104 SELECT group_id,day,coalesce(tracker_opened,0) AS tracker_opened,
1105 coalesce(tracker_closed,0) AS tracker_closed,
1106 coalesce(forum,0) AS forum,
1107 coalesce(docs,0) AS docs,
1108 coalesce(downloads,0) AS downloads,
1109 coalesce(cvs_commits,0) AS cvs_commits,
1110 coalesce(tasks_opened,0) AS tasks_opened,
1111 coalesce(tasks_closed,0) AS tasks_closed
1120 (SELECT agl.group_id, '$day'::int AS day, count(*) AS tracker_opened
1121 FROM artifact a, artifact_group_list agl
1122 WHERE a.open_date BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
1123 AND a.group_artifact_id=agl.group_artifact_id
1124 GROUP BY group_id,day) aopen
1127 (SELECT agl.group_id, '$day'::int AS day, count(*) AS tracker_closed
1128 FROM artifact a, artifact_group_list agl
1129 WHERE a.close_date BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
1130 AND a.group_artifact_id=agl.group_artifact_id
1131 GROUP BY group_id,day ) aclosed USING (group_id,day)) foo1
1134 (SELECT fgl.group_id, '$day'::int AS day, count(*) AS forum
1135 FROM forum f, forum_group_list fgl
1136 WHERE f.post_date BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
1137 AND f.group_forum_id=fgl.group_forum_id
1138 GROUP BY group_id,day ) forum USING (group_id,day)) foo2
1141 (SELECT group_id, '$day'::int AS day, count(*) AS docs
1143 WHERE createdate BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
1144 GROUP BY group_id,day ) docs USING (group_id,day)) foo3
1147 (SELECT fp.group_id, '$day'::int AS day, count(*) AS downloads
1148 FROM frs_package fp, frs_release fr, frs_file ff, frs_dlstats_file fdf
1149 WHERE fp.package_id=fr.package_id
1150 AND fr.release_id=ff.release_id
1151 AND ff.file_id=fdf.file_id
1152 AND fdf.month = '". date('Ym',$day) ."' AND fdf.day = '". date('d',$day) ."'
1153 GROUP BY fp.group_id,day ) docs USING (group_id,day)) foo4
1156 (SELECT group_id,$day AS day, sum(commits) AS cvs_commits
1157 FROM stats_cvs_group
1158 WHERE month='". date('Ym',$day) ."' AND day='". date('d',$day) ."'
1159 GROUP BY group_id,day ) cvs USING (group_id,day)) foo5
1162 (SELECT pgl.group_id, '$day'::int AS day,count(*) AS tasks_opened
1163 FROM project_task pt, project_group_list pgl
1164 WHERE pt.start_date BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
1165 AND pt.group_project_id=pgl.group_project_id
1166 GROUP BY group_id,day ) topen USING (group_id,day)) foo6
1169 (SELECT pgl.group_id, '$day'::int AS day, count(*) AS tasks_closed
1170 FROM project_history ph, project_task pt, project_group_list pgl
1171 WHERE ph.mod_date BETWEEN '$day' AND '". ($day+REPORT_DAY_SPAN-1) ."'
1172 AND ph.old_value='1'
1173 AND ph.field_name='status_id'
1174 AND ph.project_task_id=pt.project_task_id
1175 AND pt.group_project_id=pgl.group_project_id
1176 GROUP BY group_id,day ) tclosed USING (group_id,day)) foo7";
1178 return db_query($sql);
1183 * Populate the group_act_daily report table.
1185 * @return boolean Success.
1187 function backfill_group_act_daily($count=10000) {
1188 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1189 if (!$start_date=$this->getMinDate()) {
1190 $this->setError('backfill_group_act_daily:: Could Not Get Start Date');
1195 $day=$today-($i*REPORT_DAY_SPAN);
1196 if (!$this->group_act_daily($day)) {
1197 $this->setError('backfill_group_act_daily:: Error adding daily row: '.db_error());
1200 if ($day < $start_date) {
1212 * Add a row to the group_act_weekly report table.
1214 * @param int Week - the unix time of the beginning of the sunday for this week.
1215 * @return boolean Success.
1217 function group_act_weekly($week) {
1218 db_query("DELETE FROM rep_group_act_weekly WHERE week='$week'");
1220 $sql="INSERT INTO rep_group_act_weekly (group_id,week,tracker_opened,tracker_closed,
1221 forum,docs,downloads,cvs_commits,tasks_opened,tasks_closed)
1222 SELECT group_id,'$week'::int AS week, sum(tracker_opened) AS tracker_opened,
1223 sum(tracker_closed) AS tracker_closed,
1224 sum(forum) AS forum,
1226 sum(downloads) AS downloads,
1227 sum(cvs_commits) AS cvs_commits,
1228 sum(tasks_opened) AS tasks_opened,
1229 sum(tasks_closed) AS tasks_closed
1230 FROM rep_group_act_daily
1232 BETWEEN '$week' AND '". ($week+REPORT_WEEK_SPAN-1) ."'
1233 GROUP BY group_id,week";
1234 return db_query($sql);
1238 * Populate the group_act_weekly report table.
1240 * @return boolean Success.
1242 function backfill_group_act_weekly($count=10000) {
1244 $arr =& $this->getWeekStartArr();
1246 for ($i=0; $i<count($arr); $i++) {
1247 if (!$this->group_act_weekly($arr[$i])) {
1248 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1259 * Add a row to the group_act_monthly report table.
1261 * @param int month_start - the unix time of the beginning of the month.
1262 * @param int month_end - the unix time of the end of the month.
1263 * @return boolean Success.
1265 function group_act_monthly($month,$end) {
1266 db_query("DELETE FROM rep_group_act_monthly WHERE month='$month'");
1268 $sql="INSERT INTO rep_group_act_monthly (group_id,month,tracker_opened,tracker_closed,
1269 forum,docs,downloads,cvs_commits,tasks_opened,tasks_closed)
1270 SELECT group_id,'$month'::int AS month, sum(tracker_opened) AS tracker_opened,
1271 sum(tracker_closed) AS tracker_closed,
1272 sum(forum) AS forum,
1274 sum(downloads) AS downloads,
1275 sum(cvs_commits) AS cvs_commits,
1276 sum(tasks_opened) AS tasks_opened,
1277 sum(tasks_closed) AS tasks_closed
1278 FROM rep_group_act_daily
1280 BETWEEN '$month' AND '$end'
1281 GROUP BY group_id,month";
1282 return db_query($sql);
1286 * Populate the group_act_monthly report table.
1288 * @return boolean Success.
1290 function backfill_group_act_monthly($count=10000) {
1292 $arr =& $this->getMonthStartArr();
1294 for ($i=1; $i<count($arr); $i++) {
1295 if (!$this->group_act_monthly($arr[$i],($arr[$i-1]-1))) {
1296 $this->setError('backfill_group_act_monthly:: Error adding monthly row: '.db_error());
1307 * Add a row to the rep_time_category table.
1309 * @param string The category name.
1310 * @return boolean Success.
1312 function addTimeCode($category_name) {
1313 return db_query("INSERT INTO rep_time_category (category_name) VALUES ('$category_name')");
1317 * Update the rep_time_category table.
1319 * @param string The category name.
1320 * @return boolean Success.
1322 function updateTimeCode($time_code, $category_name) {
1323 return db_query("UPDATE rep_time_category SET category_name='$category_name' WHERE time_code='$time_code'");