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.php');
30 class ReportSetup extends Report {
32 function ReportSetup() {
37 function initialSetup() {
38 $this->createTables();
39 if (!$this->initialData()) {
46 function createTables() {
47 global $sys_database_type;
50 //DROP TABLE rep_time_category;
51 $sql[]="CREATE TABLE rep_time_category (
52 time_code serial UNIQUE,
55 //$sql[]="DROP TABLE rep_time_tracking;";
56 $sql1="CREATE TABLE rep_time_tracking (
58 report_date int not null,
60 project_task_id int not null,
61 time_code int not null";
63 if ($sys_database_type != "mysql") {
64 $sql1.=" CONSTRAINT reptimetrk_timecode REFERENCES rep_time_category(time_code)";
66 $sql1.=",hours float not null);";
68 // $sql[]="CREATE UNIQUE INDEX reptimetrk_weekusrtskcde ON
69 // rep_time_tracking (week,user_id,project_task_id,time_code);";
70 $sql[]="CREATE INDEX reptimetracking_userdate ON
71 rep_time_tracking (user_id,week);";
73 $sql[]="INSERT INTO rep_time_category VALUES ('1','Coding');";
74 $sql[]="INSERT INTO rep_time_category VALUES ('2','Testing');";
75 $sql[]="INSERT INTO rep_time_category VALUES ('3','Meeting');";
76 $sql[]="SELECT setval('rep_time_category_time_code_seq',(SELECT max(time_code) FROM rep_time_category));";
79 $sql[]="DROP TABLE rep_users_added_daily;";
80 $sql[]="CREATE TABLE rep_users_added_daily (
81 day int not null primary key,
82 added int not null default 0);";
84 $sql[]="DROP TABLE rep_users_added_weekly";
85 $sql[]="CREATE TABLE rep_users_added_weekly (
86 week int not null primary key,
87 added int not null default 0);";
89 $sql[]="DROP TABLE rep_users_added_monthly";
90 $sql[]="CREATE TABLE rep_users_added_monthly (
91 month int not null primary key,
92 added int not null default 0);";
95 $sql[]="DROP TABLE rep_users_cum_daily";
96 $sql[]="CREATE TABLE rep_users_cum_daily (
97 day int not null primary key,
98 total int not null default 0);";
100 $sql[]="DROP TABLE rep_users_cum_weekly";
101 $sql[]="CREATE TABLE rep_users_cum_weekly (
102 week int not null primary key,
103 total int not null default 0);";
105 $sql[]="DROP TABLE rep_users_cum_monthly";
106 $sql[]="CREATE TABLE rep_users_cum_monthly (
107 month int not null primary key,
108 total int not null default 0);";
111 $sql[]="DROP TABLE rep_groups_added_daily;";
112 $sql[]="CREATE TABLE rep_groups_added_daily (
113 day int not null primary key,
114 added int not null default 0);";
116 $sql[]="DROP TABLE rep_groups_added_weekly";
117 $sql[]="CREATE TABLE rep_groups_added_weekly (
118 week int not null primary key,
119 added int not null default 0);";
121 $sql[]="DROP TABLE rep_groups_added_monthly";
122 $sql[]="CREATE TABLE rep_groups_added_monthly (
123 month int not null primary key,
124 added int not null default 0);";
127 $sql[]="DROP TABLE rep_groups_cum_daily";
128 $sql[]="CREATE TABLE rep_groups_cum_daily (
129 day int not null primary key,
130 total int not null default 0);";
132 $sql[]="DROP TABLE rep_groups_cum_weekly";
133 $sql[]="CREATE TABLE rep_groups_cum_weekly (
134 week int not null primary key,
135 total int not null default 0);";
137 $sql[]="DROP TABLE rep_groups_cum_monthly";
138 $sql[]="CREATE TABLE rep_groups_cum_monthly (
139 month int not null primary key,
140 total int not null default 0);";
143 $sql[]="DROP TABLE rep_user_act_daily";
144 $sql[]="CREATE TABLE rep_user_act_daily (
145 user_id int not null,
147 tracker_opened int not null,
148 tracker_closed int not null,
151 cvs_commits int not null,
152 tasks_opened int not null,
153 tasks_closed int not null,
154 PRIMARY KEY (user_id,day));";
156 $sql[]="DROP TABLE rep_user_act_weekly";
157 $sql[]="CREATE TABLE rep_user_act_weekly (
158 user_id int not null,
160 tracker_opened int not null,
161 tracker_closed int not null,
164 cvs_commits int not null,
165 tasks_opened int not null,
166 tasks_closed int not null,
167 PRIMARY KEY (user_id,week));";
169 $sql[]="DROP TABLE rep_user_act_monthly";
170 $sql[]="CREATE TABLE rep_user_act_monthly (
171 user_id int not null,
173 tracker_opened int not null,
174 tracker_closed int not null,
177 cvs_commits int not null,
178 tasks_opened int not null,
179 tasks_closed int not null,
180 PRIMARY KEY (user_id,month));";
182 $sql[]="DROP VIEW rep_user_act_oa_vw";
183 $sql[]="CREATE VIEW rep_user_act_oa_vw AS
185 sum(tracker_opened) AS tracker_opened,
186 sum(tracker_closed) AS tracker_closed,
189 sum(cvs_commits) AS cvs_commits,
190 sum(tasks_opened) AS tasks_opened,
191 sum(tasks_closed) AS tasks_closed
192 FROM rep_user_act_monthly
195 //per-project activity
196 $sql[]="DROP TABLE rep_group_act_daily";
197 $sql[]="CREATE TABLE rep_group_act_daily (
198 group_id int not null,
200 tracker_opened int not null,
201 tracker_closed int not null,
204 downloads int not null,
205 cvs_commits int not null,
206 tasks_opened int not null,
207 tasks_closed int not null,
208 PRIMARY KEY (group_id,day));";
210 $sql[]="DROP INDEX repgroupactdaily_day";
211 $sql[]="CREATE INDEX repgroupactdaily_day ON rep_group_act_daily(day)";
213 $sql[]="DROP TABLE rep_group_act_weekly";
214 $sql[]="CREATE TABLE rep_group_act_weekly (
215 group_id int not null,
217 tracker_opened int not null,
218 tracker_closed int not null,
221 downloads int not null,
222 cvs_commits int not null,
223 tasks_opened int not null,
224 tasks_closed int not null,
225 PRIMARY KEY (group_id,week));";
227 $sql[]="DROP INDEX repgroupactweekly_week";
228 $sql[]="CREATE INDEX repgroupactweekly_week ON rep_group_act_weekly(week)";
230 $sql[]="DROP TABLE rep_group_act_monthly";
231 $sql[]="CREATE TABLE rep_group_act_monthly (
232 group_id int not null,
234 tracker_opened int not null,
235 tracker_closed int not null,
238 downloads int not null,
239 cvs_commits int not null,
240 tasks_opened int not null,
241 tasks_closed int not null,
242 PRIMARY KEY (group_id,month));";
244 $sql[]="DROP INDEX repgroupactmonthly_month";
245 $sql[]="CREATE INDEX repgroupactmonthly_month ON rep_group_act_monthly(month)";
247 $sql[]="DROP VIEW rep_group_act_oa_vw";
248 $sql[]="CREATE VIEW rep_group_act_oa_vw AS
250 sum(tracker_opened) AS tracker_opened,
251 sum(tracker_closed) AS tracker_closed,
254 sum(downloads) AS downloads,
255 sum(cvs_commits) AS cvs_commits,
256 sum(tasks_opened) AS tasks_opened,
257 sum(tasks_closed) AS tasks_closed
258 FROM rep_group_act_monthly
262 $sql[]="DROP VIEW rep_site_act_daily_vw";
263 $sql[]="CREATE VIEW rep_site_act_daily_vw AS
265 sum(tracker_opened) AS tracker_opened,
266 sum(tracker_closed) AS tracker_closed,
269 sum(downloads) AS downloads,
270 sum(cvs_commits) AS cvs_commits,
271 sum(tasks_opened) AS tasks_opened,
272 sum(tasks_closed) AS tasks_closed
273 FROM rep_group_act_daily
276 $sql[]="DROP VIEW rep_site_act_weekly_vw";
277 $sql[]="CREATE VIEW rep_site_act_weekly_vw AS
279 sum(tracker_opened) AS tracker_opened,
280 sum(tracker_closed) AS tracker_closed,
283 sum(downloads) AS downloads,
284 sum(cvs_commits) AS cvs_commits,
285 sum(tasks_opened) AS tasks_opened,
286 sum(tasks_closed) AS tasks_closed
287 FROM rep_group_act_weekly
290 $sql[]="DROP VIEW rep_site_act_monthly_vw";
291 $sql[]="CREATE VIEW rep_site_act_monthly_vw AS
293 sum(tracker_opened) AS tracker_opened,
294 sum(tracker_closed) AS tracker_closed,
297 sum(downloads) AS downloads,
298 sum(cvs_commits) AS cvs_commits,
299 sum(tasks_opened) AS tasks_opened,
300 sum(tasks_closed) AS tasks_closed
301 FROM rep_group_act_monthly
304 $sql[]="DROP VIEW rep_site_act_oa_vw";
305 $sql[]="CREATE VIEW rep_site_act_oa_vw AS
306 sum(tracker_opened) AS tracker_opened,
307 sum(tracker_closed) AS tracker_closed,
310 sum(downloads) AS downloads,
311 sum(cvs_commits) AS cvs_commits,
312 sum(tasks_opened) AS tasks_opened,
313 sum(tasks_closed) AS tasks_closed
314 FROM rep_group_act_monthly;";
316 for ($i=0; $i<count($sql); $i++) {
318 $res=db_query($sql[$i]);
324 function initialData() {
325 if (!$this->backfill_users_added_daily()) {
328 if (!$this->backfill_users_added_weekly()) {
331 if (!$this->backfill_users_added_monthly()) {
334 if (!$this->backfill_users_cum_daily()) {
337 if (!$this->backfill_users_cum_weekly()) {
340 if (!$this->backfill_users_cum_monthly()) {
343 if (!$this->backfill_groups_added_daily()) {
346 if (!$this->backfill_groups_added_weekly()) {
349 if (!$this->backfill_groups_added_monthly()) {
352 if (!$this->backfill_groups_cum_daily()) {
355 if (!$this->backfill_groups_cum_weekly()) {
358 if (!$this->backfill_groups_cum_monthly()) {
361 if (!$this->backfill_user_act_daily()) {
364 if (!$this->backfill_user_act_weekly()) {
367 if (!$this->backfill_user_act_monthly()) {
370 if (!$this->backfill_group_act_daily()) {
373 if (!$this->backfill_group_act_weekly()) {
376 if (!$this->backfill_group_act_monthly()) {
383 function dailyData() {
384 if (!$this->backfill_users_added_daily(1)) {
387 if (!$this->backfill_users_added_weekly(1)) {
390 if (!$this->backfill_users_added_monthly(2)) {
393 if (!$this->backfill_users_cum_daily(1)) {
396 if (!$this->backfill_users_cum_weekly(1)) {
399 if (!$this->backfill_users_cum_monthly(2)) {
402 if (!$this->backfill_user_act_daily(1)) {
405 if (!$this->backfill_user_act_weekly(1)) {
408 if (!$this->backfill_user_act_monthly(2)) {
411 if (!$this->backfill_group_act_daily(1)) {
414 if (!$this->backfill_group_act_weekly(1)) {
417 if (!$this->backfill_group_act_monthly(2)) {
423 * Add a row to the users_added_daily report table.
425 * @param int Day - the unix time of the beginning of the day.
426 * @return boolean Success.
428 function users_added_daily($day) {
429 db_query("DELETE FROM rep_users_added_daily WHERE day='$day'");
431 $sql="INSERT INTO rep_users_added_daily (day,added)
432 VALUES ('$day',(SELECT count(*) FROM users WHERE status='A' AND add_date
433 BETWEEN '$day' AND '". ($day + REPORT_DAY_SPAN - 1) ."' ))";
434 return db_query($sql);
438 * Populate the users_added_daily report table.
440 * @return boolean Success.
442 function backfill_users_added_daily($count=10000) {
443 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
444 if (!$start_date=$this->getMinDate()) {
445 $this->setError('backfill_users_added_daily:: Could Not Get Start Date');
450 $day=($today-($i*REPORT_DAY_SPAN));
451 if (!$this->users_added_daily($day)) {
452 $this->setError('backfill_users_added_daily:: Error adding daily row: '.db_error());
455 if ($day < $start_date) {
467 * Add a row to the groups_added_daily report table.
469 * @param int Day - the unix time of the beginning of the day.
470 * @return boolean Success.
472 function groups_added_daily($day) {
473 db_query("DELETE FROM rep_groups_added_daily WHERE day='$day'");
475 $sql="INSERT INTO rep_groups_added_daily (day,added)
476 VALUES ('$day',(SELECT count(*) FROM groups WHERE status='A' AND register_time
477 BETWEEN '$day' AND '". ($day + REPORT_DAY_SPAN - 1) ."' ))";
478 return db_query($sql);
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("DELETE FROM rep_users_added_weekly WHERE week='$week'");
519 $sql="INSERT INTO rep_users_added_weekly (week,added)
520 VALUES ('$week',(SELECT count(*) FROM users WHERE status='A' AND add_date
521 BETWEEN '$week' AND '". ($week+REPORT_WEEK_SPAN-1) ."' ))";
522 return db_query($sql);
526 * Populate the users_added_weekly report table.
528 * @return boolean Success.
530 function backfill_users_added_weekly($count=10000) {
532 $arr =& $this->getWeekStartArr();
534 for ($i=0; $i<count($arr); $i++) {
535 if (!$this->users_added_weekly($arr[$i])) {
536 $this->setError('backfill_users_added_weekly:: Error adding weekly row: '.db_error());
547 * Add a row to the groups_added_weekly report table.
549 * @param int Week - the unix time of the beginning of the sunday for this week.
550 * @return boolean Success.
552 function groups_added_weekly($week) {
553 db_query("DELETE FROM rep_groups_added_weekly WHERE week='$week'");
555 $sql="INSERT INTO rep_groups_added_weekly (week,added)
556 VALUES ('$week',(SELECT count(*) FROM groups WHERE status='A' AND register_time
557 BETWEEN '$week' AND '". ($week+REPORT_WEEK_SPAN-1) ."' ))";
558 return db_query($sql);
562 * Populate the users_added_weekly report table.
564 * @return boolean Success.
566 function backfill_groups_added_weekly($count=10000) {
568 $arr =& $this->getWeekStartArr();
570 for ($i=0; $i<count($arr); $i++) {
571 if (!$this->groups_added_weekly($arr[$i])) {
572 $this->setError('backfill_groups_added_weekly:: Error adding weekly row: '.db_error());
583 * Add a row to the users_added_monthly report table.
585 * @param int month_start - the unix time of the beginning of the month.
586 * @param int month_end - the unix time of the end of the month.
587 * @return boolean Success.
589 function users_added_monthly($month,$end) {
590 db_query("DELETE FROM rep_users_added_monthly WHERE month='$month'");
592 $sql="INSERT INTO rep_users_added_monthly (month,added)
593 VALUES ('$month',(SELECT count(*) FROM users WHERE status='A' AND add_date
594 BETWEEN '$month' AND '$end' ))";
595 return db_query($sql);
599 * Populate the users_added_monthly report table.
601 * @return boolean Success.
603 function backfill_users_added_monthly($count=10000) {
605 $arr =& $this->getMonthStartArr();
608 for ($i=1; $i<count($arr); $i++) {
609 if (!$this->users_added_monthly($arr[$i],($arr[$i-1]-1))) {
610 $this->setError('backfill_users_added_monthly:: Error adding monthly row: '.db_error());
621 * Add a row to the groups_added_monthly report table.
623 * @param int month_start - the unix time of the beginning of the month.
624 * @param int month_end - the unix time of the end of the month.
625 * @return boolean Success.
627 function groups_added_monthly($month,$end) {
628 db_query("DELETE FROM rep_groups_added_monthly WHERE month='$month'");
630 $sql="INSERT INTO rep_groups_added_monthly (month,added)
631 VALUES ('$month',(SELECT count(*) FROM groups WHERE status='A' AND register_time
632 BETWEEN '$month' AND '$end' ))";
633 return db_query($sql);
637 * Populate the groups_added_monthly report table.
639 * @return boolean Success.
641 function backfill_groups_added_monthly($count=10000) {
643 $arr =& $this->getMonthStartArr();
646 for ($i=1; $i<count($arr); $i++) {
647 if (!$this->groups_added_monthly($arr[$i],($arr[$i-1]-1))) {
648 $this->setError('backfill_groups_added_monthly:: Error adding monthly row: '.db_error());
659 // ******************************
663 * Add a row to the users_cum_daily report table.
665 * @param int Day - the unix time of the beginning of the day.
666 * @return boolean Success.
668 function users_cum_daily($day) {
669 db_query("DELETE FROM rep_users_cum_daily WHERE day='$day'");
671 $sql="INSERT INTO rep_users_cum_daily (day,total)
672 VALUES ('$day',(SELECT count(*) FROM users WHERE status='A' AND add_date
673 BETWEEN '0' AND '$day'))";
674 return db_query($sql);
678 * Populate the users_cum_daily report table.
680 * @return boolean Success.
682 function backfill_users_cum_daily($count=10000) {
683 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
684 if (!$start_date=$this->getMinDate()) {
685 $this->setError('backfill_users_cum_daily:: Could Not Get Start Date');
690 $day=$today-($i*REPORT_DAY_SPAN);
691 if (!$this->users_cum_daily($day)) {
692 $this->setError('backfill_users_cum_daily:: Error adding daily row: '.db_error());
695 if ($day < $start_date) {
707 * Add a row to the groups_cum_daily report table.
709 * @param int Day - the unix time of the beginning of the day.
710 * @return boolean Success.
712 function groups_cum_daily($day) {
713 db_query("DELETE FROM rep_groups_cum_daily WHERE day='$day'");
715 $sql="INSERT INTO rep_groups_cum_daily (day,total)
716 VALUES ('$day',(SELECT count(*) FROM groups WHERE status='A' AND register_time
717 BETWEEN '0' AND '$day'))";
718 return db_query($sql);
722 * Populate the groups_cum_daily report table.
724 * @return boolean Success.
726 function backfill_groups_cum_daily($count=10000) {
727 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
728 if (!$start_date=$this->getMinDate()) {
729 $this->setError('backfill_groups_cum_daily:: Could Not Get Start Date');
734 $day=$today-($i*REPORT_DAY_SPAN);
735 if (!$this->groups_cum_daily($day)) {
736 $this->setError('backfill_groups_cum_daily:: Error adding daily row: '.db_error());
739 if ($day < $start_date) {
751 * Add a row to the users_cum_weekly report table.
753 * @param int Week - the unix time of the beginning of the sunday for this week.
754 * @return boolean Success.
756 function users_cum_weekly($week) {
757 db_query("DELETE FROM rep_users_cum_weekly WHERE week='$week'");
759 $sql="INSERT INTO rep_users_cum_weekly (week,total)
760 VALUES ('$week',(SELECT count(*) FROM users WHERE status='A' AND add_date
761 BETWEEN '0' AND '". ($week+REPORT_WEEK_SPAN-1 ). "'))";
762 return db_query($sql);
766 * Populate the users_cum_weekly report table.
768 * @return boolean Success.
770 function backfill_users_cum_weekly($count=10000) {
772 $arr =& $this->getWeekStartArr();
774 for ($i=0; $i<count($arr); $i++) {
775 if (!$this->groups_cum_weekly($arr[$i])) {
776 $this->setError('backfill_users_cum_weekly:: Error adding weekly row: '.db_error());
787 * Add a row to the groups_cum_weekly report table.
789 * @param int Week - the unix time of the beginning of the sunday for this week.
790 * @return boolean Success.
792 function groups_cum_weekly($week) {
793 db_query("DELETE FROM rep_groups_cum_weekly WHERE week='$week'");
795 $sql="INSERT INTO rep_groups_cum_weekly (week,total)
796 VALUES ('$week',(SELECT count(*) FROM groups WHERE status='A' AND register_time
797 BETWEEN '0' AND '". ($week+REPORT_WEEK_SPAN-1 ). "'))";
798 return db_query($sql);
802 * Populate the groups_cum_weekly report table.
804 * @return boolean Success.
806 function backfill_groups_cum_weekly($count=10000) {
808 $arr =& $this->getWeekStartArr();
810 for ($i=0; $i<count($arr); $i++) {
811 if (!$this->users_cum_weekly($arr[$i])) {
812 $this->setError('backfill_groups_cum_weekly:: Error adding weekly row: '.db_error());
823 * Add a row to the users_cum_monthly report table.
825 * @param int month_start - the unix time of the beginning of the month.
826 * @param int month_end - the unix time of the end of the month.
827 * @return boolean Success.
829 function users_cum_monthly($month,$end) {
830 db_query("DELETE FROM rep_users_cum_monthly WHERE month='$month'");
832 $sql="INSERT INTO rep_users_cum_monthly (month,total)
833 VALUES ('$month',(SELECT count(*) FROM users WHERE status='A' AND add_date
834 BETWEEN '0' AND '$end'))";
835 return db_query($sql);
839 * Populate the users_cum_monthly report table.
841 * @return boolean Success.
843 function backfill_users_cum_monthly($count=10000) {
845 $arr =& $this->getMonthStartArr();
848 for ($i=1; $i<count($arr); $i++) {
849 if (!$this->users_cum_monthly($arr[$i],($arr[$i-1]-1))) {
850 $this->setError('backfill_users_cum_monthly:: Error adding monthly row: '.db_error());
861 * Add a row to the groups_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 groups_cum_monthly($month,$end) {
868 db_query("DELETE FROM rep_groups_cum_monthly WHERE month='$month'");
870 $sql="INSERT INTO rep_groups_cum_monthly (month,total)
871 VALUES ('$month',(SELECT count(*) FROM groups WHERE status='A' AND register_time
872 BETWEEN '0' AND '$end'))";
873 return db_query($sql);
877 * Populate the groups_cum_monthly report table.
879 * @return boolean Success.
881 function backfill_groups_cum_monthly($count=10000) {
883 $arr =& $this->getMonthStartArr();
886 for ($i=1; $i<count($arr); $i++) {
887 if (!$this->groups_cum_monthly($arr[$i],($arr[$i-1]-1))) {
888 $this->setError('backfill_groups_cum_monthly:: Error adding monthly row: '.db_error());
899 // ************************
903 * Add a row to the user_act_daily report table.
905 * @param int Day - the unix time of the beginning of the day.
906 * @return boolean Success.
908 function user_act_daily($day) {
909 global $sys_database_type;
911 db_query("DELETE FROM rep_user_act_daily WHERE day='$day'");
913 $end_day=$day+REPORT_DAY_SPAN-1;
915 if ( $sys_database_type == "mysql" ) {
916 $sql="INSERT INTO rep_user_act_daily
917 SELECT user_id,$day,coalesce(tracker_opened,0) AS tracker_opened,
918 coalesce(tracker_closed,0) AS tracker_closed,
919 coalesce(forum,0) AS forum,
920 coalesce(docs,0) AS docs,
921 coalesce(cvs_commits,0) AS cvs_commits,
922 coalesce(tasks_opened,0) AS tasks_opened,
923 coalesce(tasks_closed,0) AS tasks_closed
925 (((((((((SELECT submitted_by AS user_id FROM artifact WHERE open_date BETWEEN '$day' AND '$end_day')
927 (SELECT assigned_to AS user_id FROM artifact WHERE close_date BETWEEN '$day' AND '$end_day')
929 (SELECT posted_by AS user_id FROM forum WHERE post_date BETWEEN '$day' AND '$end_day')
931 (SELECT created_by AS user_id FROM doc_data WHERE createdate BETWEEN '$day' AND '$end_day' )
933 (SELECT user_id FROM stats_cvs_user WHERE month='". date('Ym') ."' AND day='". date('d') ."')
935 (SELECT created_by AS user_id FROM project_task WHERE start_date BETWEEN '$day' AND '$end_day')
937 (SELECT mod_by AS user_id FROM project_history WHERE mod_date BETWEEN '$day' AND '$end_day')) AS t_users
939 (SELECT submitted_by AS user_id, count(*) AS tracker_opened FROM artifact
940 WHERE open_date BETWEEN '$day' AND '$end_day'
941 GROUP BY user_id) AS tmp1 USING (user_id))
943 (SELECT assigned_to AS user_id, count(*) AS tracker_closed FROM artifact
944 WHERE close_date BETWEEN '$day' AND '$end_day'
945 GROUP BY user_id) AS tmp2 USING (user_id))
947 (SELECT posted_by AS user_id, count(*) AS forum
949 WHERE post_date BETWEEN '$day' AND '$end_day'
950 GROUP BY user_id) AS tmp3 USING (user_id))
952 (SELECT created_by AS user_id, count(*) AS docs
954 WHERE createdate BETWEEN '$day' AND '$end_day'
955 GROUP BY user_id) AS tmp4 USING (user_id))
957 (SELECT user_id, sum(commits) AS cvs_commits
959 WHERE month='". date('Ym') ."' AND day='". date('d') ."'
960 GROUP BY user_id) AS tmp5 USING (user_id))
962 (SELECT created_by AS user_id, count(*) AS tasks_opened
964 WHERE start_date BETWEEN '$day' AND '$end_day'
965 GROUP BY user_id) AS tmp6 USING (user_id))
967 (SELECT mod_by AS user_id, count(*) AS tasks_closed
969 WHERE mod_date BETWEEN '$day' AND '$end_day' AND old_value='1' AND field_name='status_id'
970 GROUP BY user_id) AS tmp7 USING (user_id))";
972 $sql="INSERT INTO rep_user_act_daily
973 SELECT user_id,day,coalesce(tracker_opened,0) AS tracker_opened,
974 coalesce(tracker_closed,0) AS tracker_closed,
975 coalesce(forum,0) AS forum,
976 coalesce(docs,0) AS docs,
977 coalesce(cvs_commits,0) AS cvs_commits,
978 coalesce(tasks_opened,0) AS tasks_opened,
979 coalesce(tasks_closed,0) AS tasks_closed
987 (SELECT submitted_by AS user_id, '$day'::int AS day, count(*) AS tracker_opened
989 WHERE open_date BETWEEN '$day' AND '$end_day'
990 GROUP BY user_id,day) aopen
993 (SELECT assigned_to AS user_id, '$day'::int AS day, count(*) AS tracker_closed
995 WHERE close_date BETWEEN '$day' AND '$end_day'
996 GROUP BY user_id,day ) aclosed USING (user_id,day)) foo1
999 (SELECT posted_by AS user_id, '$day'::int AS day, count(*) AS forum
1001 WHERE post_date BETWEEN '$day' AND '$end_day'
1002 GROUP BY user_id,day ) forum USING (user_id,day)) foo2
1005 (SELECT created_by AS user_id, '$day'::int AS day, count(*) AS docs
1007 WHERE createdate BETWEEN '$day' AND '$end_day'
1008 GROUP BY user_id,day ) docs USING (user_id,day)) foo3
1011 (SELECT user_id,$day AS day, sum(commits) AS cvs_commits
1013 WHERE month='". date('Ym') ."' AND day='$end_day'
1014 GROUP BY user_id,day ) cvs USING (user_id,day)) foo4
1017 (SELECT created_by AS user_id, '$day'::int AS day, count(*) AS tasks_opened
1019 WHERE start_date BETWEEN '$day' AND '$end_day'
1020 GROUP BY user_id,day ) topen USING (user_id,day)) foo5
1023 (SELECT mod_by AS user_id, '$day'::int AS day, count(*) AS tasks_closed
1024 FROM project_history
1025 WHERE mod_date BETWEEN '$day' AND '$end_day'
1026 AND old_value='1' AND field_name='status_id'
1027 GROUP BY user_id,day ) tclosed USING (user_id,day)) foo6";
1030 return db_query($sql);
1035 * Populate the user_act_daily report table.
1037 * @return boolean Success.
1039 function backfill_user_act_daily($count=10000) {
1040 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1041 if (!$start_date=$this->getMinDate()) {
1042 $this->setError('backfill_user_act_daily:: Could Not Get Start Date');
1047 $day=$today-($i*REPORT_DAY_SPAN);
1048 if (!$this->user_act_daily($day)) {
1049 $this->setError('backfill_user_act_daily:: Error adding daily row: '.db_error());
1052 if ($day < $start_date) {
1064 * Add a row to the user_act_weekly report table.
1066 * @param int Week - the unix time of the beginning of the sunday for this week.
1067 * @return boolean Success.
1069 function user_act_weekly($week) {
1070 global $sys_database_type;
1072 db_query("DELETE FROM rep_user_act_weekly WHERE week='$week'");
1074 $sql="INSERT INTO rep_user_act_weekly (user_id,week,tracker_opened,tracker_closed,
1075 forum,docs,cvs_commits,tasks_opened,tasks_closed)
1077 if ( $sys_database_type == "mysql" ) {
1078 $sql.="SELECT user_id,$week AS week, sum(tracker_opened) AS tracker_opened,";
1080 $sql.="SELECT user_id,'$week'::int AS week, sum(tracker_opened) AS tracker_opened,";
1083 sum(tracker_closed) AS tracker_closed,
1084 sum(forum) AS forum,
1086 sum(cvs_commits) AS cvs_commits,
1087 sum(tasks_opened) AS tasks_opened,
1088 sum(tasks_closed) AS tasks_closed
1089 FROM rep_user_act_daily
1091 BETWEEN '$week' AND '". ($week+REPORT_WEEK_SPAN-1) ."'
1092 GROUP BY user_id,week";
1093 return db_query($sql);
1097 * Populate the user_act_weekly report table.
1099 * @return boolean Success.
1101 function backfill_user_act_weekly($count=10000) {
1103 $arr =& $this->getWeekStartArr();
1105 for ($i=0; $i<count($arr); $i++) {
1106 if (!$this->user_act_weekly($arr[$i])) {
1107 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1118 * Add a row to the user_act_monthly report table.
1120 * @param int month_start - the unix time of the beginning of the month.
1121 * @param int month_end - the unix time of the end of the month.
1122 * @return boolean Success.
1124 function user_act_monthly($month,$end) {
1125 global $sys_database_type;
1127 db_query("DELETE FROM rep_user_act_monthly WHERE month='$month'");
1129 $sql="INSERT INTO rep_user_act_monthly (user_id,month,tracker_opened,tracker_closed,
1130 forum,docs,cvs_commits,tasks_opened,tasks_closed)
1132 if ($sys_database_type == "mysql") {
1133 $sql.="SELECT user_id,$month AS month, sum(tracker_opened) AS tracker_opened,";
1135 $sql.="SELECT user_id,'$month'::int AS month, sum(tracker_opened) AS tracker_opened,";
1138 sum(tracker_closed) AS tracker_closed,
1139 sum(forum) AS forum,
1141 sum(cvs_commits) AS cvs_commits,
1142 sum(tasks_opened) AS tasks_opened,
1143 sum(tasks_closed) AS tasks_closed
1144 FROM rep_user_act_daily
1146 BETWEEN '$month' AND '$end'
1147 GROUP BY user_id,month";
1148 return db_query($sql);
1152 * Populate the user_act_monthly report table.
1154 * @return boolean Success.
1156 function backfill_user_act_monthly($count=10000) {
1158 $arr =& $this->getMonthStartArr();
1160 for ($i=1; $i<count($arr); $i++) {
1161 if (!$this->user_act_monthly($arr[$i],($arr[$i-1]-1))) {
1162 $this->setError('backfill_user_act_monthly:: Error adding monthly row: '.db_error());
1172 // ************************
1176 * Add a row to the group_act_daily report table.
1178 * @param int Day - the unix time of the beginning of the day.
1179 * @return boolean Success.
1181 function group_act_daily($day) {
1182 global $sys_database_type;
1184 db_query("DELETE FROM rep_group_act_daily WHERE day='$day'");
1186 $end_day=$day+REPORT_DAY_SPAN-1;
1188 if ($sys_database_type == "mysql") {
1189 $sql="INSERT INTO rep_group_act_daily
1190 SELECT group_id,'$day',coalesce(tracker_opened,0) AS tracker_opened,
1191 coalesce(tracker_closed,0) AS tracker_closed,
1192 coalesce(forum,0) AS forum,
1193 coalesce(docs,0) AS docs,
1194 coalesce(downloads,0) AS downloads,
1195 coalesce(cvs_commits,0) AS cvs_commits,
1196 coalesce(tasks_opened,0) AS tasks_opened,
1197 coalesce(tasks_closed,0) AS tasks_closed
1199 ((((((((((SELECT agl.group_id FROM artifact a, artifact_group_list agl
1200 WHERE a.open_date BETWEEN '$day' AND '$end_day' AND a.group_artifact_id=agl.group_artifact_id)
1202 (SELECT agl.group_id FROM artifact a, artifact_group_list agl
1203 WHERE a.close_date BETWEEN '$day' AND '$end_day' AND a.group_artifact_id=agl.group_artifact_id)
1205 (SELECT fgl.group_id FROM forum f, forum_group_list fgl
1206 WHERE f.post_date BETWEEN '$day' AND '$end_day' AND f.group_forum_id=fgl.group_forum_id)
1208 (SELECT group_id FROM doc_data WHERE createdate BETWEEN '$day' AND '$end_day')
1210 (SELECT fp.group_id FROM frs_package fp, frs_release fr, frs_file ff, frs_dlstats_file fdf
1211 WHERE fp.package_id=fr.package_id AND fr.release_id=ff.release_id AND ff.file_id=fdf.file_id
1212 AND fdf.month = '". date('Ym',$day) ."' AND fdf.day = '". date('d',$day) ."')
1214 (SELECT group_id FROM stats_cvs_group WHERE month='".date('Ym',$day)."' AND day='".date('d',$day)."')
1216 (SELECT pgl.group_id FROM project_task pt, project_group_list pgl
1217 WHERE pt.start_date BETWEEN '$day' AND '$end_day' AND pt.group_project_id=pgl.group_project_id)
1219 (SELECT pgl.group_id FROM project_history ph, project_task pt, project_group_list pgl
1220 WHERE ph.mod_date BETWEEN '$day' AND '$end_day'
1221 AND ph.old_value='1' AND ph.field_name='status_id' AND ph.project_task_id=pt.project_task_id
1222 AND pt.group_project_id=pgl.group_project_id)) t_groups
1224 (SELECT agl.group_id, count(*) AS tracker_opened
1225 FROM artifact a, artifact_group_list agl
1226 WHERE a.open_date BETWEEN '$day' AND '$end_day' AND a.group_artifact_id=agl.group_artifact_id
1227 GROUP BY group_id) AS tmp1 USING (group_id))
1229 (SELECT agl.group_id, count(*) AS tracker_closed
1230 FROM artifact a, artifact_group_list agl
1231 WHERE a.close_date BETWEEN '$day' AND '$end_day' AND a.group_artifact_id=agl.group_artifact_id
1232 GROUP BY group_id) AS tmp2 USING (group_id))
1234 (SELECT fgl.group_id, count(*) AS forum
1235 FROM forum f, forum_group_list fgl
1236 WHERE f.post_date BETWEEN '$day' AND '$end_day' AND f.group_forum_id=fgl.group_forum_id
1237 GROUP BY group_id) AS tmp3 USING (group_id))
1239 (SELECT group_id, count(*) AS docs
1241 WHERE createdate BETWEEN '$day' AND '$end_day'
1242 GROUP BY group_id) AS tmp4 USING (group_id))
1244 (SELECT fp.group_id, count(*) AS downloads
1245 FROM frs_package fp, frs_release fr, frs_file ff, frs_dlstats_file fdf
1246 WHERE fp.package_id=fr.package_id AND fr.release_id=ff.release_id AND ff.file_id=fdf.file_id
1247 AND fdf.month = '". date('Ym',$day) ."' AND fdf.day = '". date('d',$day) ."'
1248 GROUP BY fp.group_id) AS tmp5 USING (group_id))
1250 (SELECT group_id, sum(commits) AS cvs_commits
1251 FROM stats_cvs_group
1252 WHERE month='". date('Ym',$day) ."' AND day='". date('d',$day) ."'
1253 GROUP BY group_id) AS tmp6 USING (group_id))
1255 (SELECT pgl.group_id, count(*) AS tasks_opened
1256 FROM project_task pt, project_group_list pgl
1257 WHERE pt.start_date BETWEEN '$day' AND '$end_day' AND pt.group_project_id=pgl.group_project_id
1258 GROUP BY group_id) AS tmp7 USING (group_id))
1260 (SELECT pgl.group_id, count(*) AS tasks_closed
1261 FROM project_history ph, project_task pt, project_group_list pgl
1262 WHERE ph.mod_date BETWEEN '$day' AND '$end_day'
1263 AND ph.old_value='1' AND ph.field_name='status_id' AND ph.project_task_id=pt.project_task_id
1264 AND pt.group_project_id=pgl.group_project_id
1265 GROUP BY group_id) AS tmp8 USING (group_id))";
1267 $sql="INSERT INTO rep_group_act_daily
1268 SELECT group_id,day,coalesce(tracker_opened,0) AS tracker_opened,
1269 coalesce(tracker_closed,0) AS tracker_closed,
1270 coalesce(forum,0) AS forum,
1271 coalesce(docs,0) AS docs,
1272 coalesce(downloads,0) AS downloads,
1273 coalesce(cvs_commits,0) AS cvs_commits,
1274 coalesce(tasks_opened,0) AS tasks_opened,
1275 coalesce(tasks_closed,0) AS tasks_closed
1284 (SELECT agl.group_id, '$day'::int AS day, count(*) AS tracker_opened
1285 FROM artifact a, artifact_group_list agl
1286 WHERE a.open_date BETWEEN '$day' AND '$end_day'
1287 AND a.group_artifact_id=agl.group_artifact_id
1288 GROUP BY group_id,day) aopen
1291 (SELECT agl.group_id, '$day'::int AS day, count(*) AS tracker_closed
1292 FROM artifact a, artifact_group_list agl
1293 WHERE a.close_date BETWEEN '$day' AND '$end_day'
1294 AND a.group_artifact_id=agl.group_artifact_id
1295 GROUP BY group_id,day ) aclosed USING (group_id,day)) foo1
1298 (SELECT fgl.group_id, '$day'::int AS day, count(*) AS forum
1299 FROM forum f, forum_group_list fgl
1300 WHERE f.post_date BETWEEN '$day' AND '$end_day'
1301 AND f.group_forum_id=fgl.group_forum_id
1302 GROUP BY group_id,day ) forum USING (group_id,day)) foo2
1305 (SELECT group_id, '$day'::int AS day, count(*) AS docs
1307 WHERE createdate BETWEEN '$day' AND '$end_day'
1308 GROUP BY group_id,day ) docs USING (group_id,day)) foo3
1311 (SELECT fp.group_id, '$day'::int AS day, count(*) AS downloads
1312 FROM frs_package fp, frs_release fr, frs_file ff, frs_dlstats_file fdf
1313 WHERE fp.package_id=fr.package_id
1314 AND fr.release_id=ff.release_id
1315 AND ff.file_id=fdf.file_id
1316 AND fdf.month = '". date('Ym',$day) ."' AND fdf.day = '". date('d',$day) ."'
1317 GROUP BY fp.group_id,day ) docs USING (group_id,day)) foo4
1320 (SELECT group_id,$day AS day, sum(commits) AS cvs_commits
1321 FROM stats_cvs_group
1322 WHERE month='". date('Ym',$day) ."' AND day='". date('d',$day) ."'
1323 GROUP BY group_id,day ) cvs USING (group_id,day)) foo5
1326 (SELECT pgl.group_id, '$day'::int AS day,count(*) AS tasks_opened
1327 FROM project_task pt, project_group_list pgl
1328 WHERE pt.start_date BETWEEN '$day' AND '$end_day'
1329 AND pt.group_project_id=pgl.group_project_id
1330 GROUP BY group_id,day ) topen USING (group_id,day)) foo6
1333 (SELECT pgl.group_id, '$day'::int AS day, count(*) AS tasks_closed
1334 FROM project_history ph, project_task pt, project_group_list pgl
1335 WHERE ph.mod_date BETWEEN '$day' AND '$end_day'
1336 AND ph.old_value='1'
1337 AND ph.field_name='status_id'
1338 AND ph.project_task_id=pt.project_task_id
1339 AND pt.group_project_id=pgl.group_project_id
1340 GROUP BY group_id,day ) tclosed USING (group_id,day)) foo7";
1343 return db_query($sql);
1348 * Populate the group_act_daily report table.
1350 * @return boolean Success.
1352 function backfill_group_act_daily($count=10000) {
1353 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1354 if (!$start_date=$this->getMinDate()) {
1355 $this->setError('backfill_group_act_daily:: Could Not Get Start Date');
1360 $day=$today-($i*REPORT_DAY_SPAN);
1361 if (!$this->group_act_daily($day)) {
1362 $this->setError('backfill_group_act_daily:: Error adding daily row: '.db_error());
1365 if ($day < $start_date) {
1377 * Add a row to the group_act_weekly report table.
1379 * @param int Week - the unix time of the beginning of the sunday for this week.
1380 * @return boolean Success.
1382 function group_act_weekly($week) {
1383 global $sys_database_type;
1385 db_query("DELETE FROM rep_group_act_weekly WHERE week='$week'");
1387 $sql="INSERT INTO rep_group_act_weekly (group_id,week,tracker_opened,tracker_closed,
1388 forum,docs,downloads,cvs_commits,tasks_opened,tasks_closed)
1390 if ( $sys_database_type == "mysql" ) {
1391 $sql.="SELECT group_id,$week AS week, sum(tracker_opened) AS tracker_opened,";
1393 $sql.="SELECT group_id,'$week'::int AS week, sum(tracker_opened) AS tracker_opened,";
1397 sum(tracker_closed) AS tracker_closed,
1398 sum(forum) AS forum,
1400 sum(downloads) AS downloads,
1401 sum(cvs_commits) AS cvs_commits,
1402 sum(tasks_opened) AS tasks_opened,
1403 sum(tasks_closed) AS tasks_closed
1404 FROM rep_group_act_daily
1406 BETWEEN '$week' AND '". ($week+REPORT_WEEK_SPAN-1) ."'
1407 GROUP BY group_id,week";
1408 return db_query($sql);
1412 * Populate the group_act_weekly report table.
1414 * @return boolean Success.
1416 function backfill_group_act_weekly($count=10000) {
1418 $arr =& $this->getWeekStartArr();
1420 for ($i=0; $i<count($arr); $i++) {
1421 if (!$this->group_act_weekly($arr[$i])) {
1422 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1433 * Add a row to the group_act_monthly report table.
1435 * @param int month_start - the unix time of the beginning of the month.
1436 * @param int month_end - the unix time of the end of the month.
1437 * @return boolean Success.
1439 function group_act_monthly($month,$end) {
1440 global $sys_database_type;
1442 db_query("DELETE FROM rep_group_act_monthly WHERE month='$month'");
1444 $sql="INSERT INTO rep_group_act_monthly (group_id,month,tracker_opened,tracker_closed,
1445 forum,docs,downloads,cvs_commits,tasks_opened,tasks_closed)
1447 if ($sys_database_type == "mysql") {
1448 $sql.="SELECT group_id,'$month' AS month, sum(tracker_opened) AS tracker_opened,";
1450 $sql.="SELECT group_id,'$month'::int AS month, sum(tracker_opened) AS tracker_opened,";
1453 sum(tracker_closed) AS tracker_closed,
1454 sum(forum) AS forum,
1456 sum(downloads) AS downloads,
1457 sum(cvs_commits) AS cvs_commits,
1458 sum(tasks_opened) AS tasks_opened,
1459 sum(tasks_closed) AS tasks_closed
1460 FROM rep_group_act_daily
1462 BETWEEN '$month' AND '$end'
1463 GROUP BY group_id,month";
1464 return db_query($sql);
1468 * Populate the group_act_monthly report table.
1470 * @return boolean Success.
1472 function backfill_group_act_monthly($count=10000) {
1474 $arr =& $this->getMonthStartArr();
1476 for ($i=1; $i<count($arr); $i++) {
1477 if (!$this->group_act_monthly($arr[$i],($arr[$i-1]-1))) {
1478 $this->setError('backfill_group_act_monthly:: Error adding monthly row: '.db_error());
1489 * Add a row to the rep_time_category table.
1491 * @param string The category name.
1492 * @return boolean Success.
1494 function addTimeCode($category_name) {
1495 return db_query("INSERT INTO rep_time_category (category_name) VALUES ('$category_name')");
1499 * Update the rep_time_category table.
1501 * @param string The category name.
1502 * @return boolean Success.
1504 function updateTimeCode($time_code, $category_name) {
1505 return db_query("UPDATE rep_time_category SET category_name='$category_name' WHERE time_code='$time_code'");