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() {
44 global $sys_database_type;
47 //DROP TABLE rep_time_category;
48 $sql[]="CREATE TABLE rep_time_category (
49 time_code serial UNIQUE,
52 //$sql[]="DROP TABLE rep_time_tracking;";
53 $sql1="CREATE TABLE rep_time_tracking (
55 report_date int not null,
57 project_task_id int not null,
58 time_code int not null";
60 if ($sys_database_type != "mysql") {
61 $sql1.=" CONSTRAINT reptimetrk_timecode REFERENCES rep_time_category(time_code)";
63 $sql1.=",hours float not null);";
65 // $sql[]="CREATE UNIQUE INDEX reptimetrk_weekusrtskcde ON
66 // rep_time_tracking (week,user_id,project_task_id,time_code);";
67 $sql[]="CREATE INDEX reptimetracking_userdate ON
68 rep_time_tracking (user_id,week);";
70 $sql[]="INSERT INTO rep_time_category VALUES ('1','Coding');";
71 $sql[]="INSERT INTO rep_time_category VALUES ('2','Testing');";
72 $sql[]="INSERT INTO rep_time_category VALUES ('3','Meeting');";
73 $sql[]="SELECT setval('rep_time_category_time_code_seq',(SELECT max(time_code) FROM rep_time_category));";
76 $sql[]="DROP TABLE rep_users_added_daily;";
77 $sql[]="CREATE TABLE rep_users_added_daily (
78 day int not null primary key,
79 added int not null default 0);";
81 $sql[]="DROP TABLE rep_users_added_weekly";
82 $sql[]="CREATE TABLE rep_users_added_weekly (
83 week int not null primary key,
84 added int not null default 0);";
86 $sql[]="DROP TABLE rep_users_added_monthly";
87 $sql[]="CREATE TABLE rep_users_added_monthly (
88 month int not null primary key,
89 added int not null default 0);";
92 $sql[]="DROP TABLE rep_users_cum_daily";
93 $sql[]="CREATE TABLE rep_users_cum_daily (
94 day int not null primary key,
95 total int not null default 0);";
97 $sql[]="DROP TABLE rep_users_cum_weekly";
98 $sql[]="CREATE TABLE rep_users_cum_weekly (
99 week int not null primary key,
100 total int not null default 0);";
102 $sql[]="DROP TABLE rep_users_cum_monthly";
103 $sql[]="CREATE TABLE rep_users_cum_monthly (
104 month int not null primary key,
105 total int not null default 0);";
108 $sql[]="DROP TABLE rep_groups_added_daily;";
109 $sql[]="CREATE TABLE rep_groups_added_daily (
110 day int not null primary key,
111 added int not null default 0);";
113 $sql[]="DROP TABLE rep_groups_added_weekly";
114 $sql[]="CREATE TABLE rep_groups_added_weekly (
115 week int not null primary key,
116 added int not null default 0);";
118 $sql[]="DROP TABLE rep_groups_added_monthly";
119 $sql[]="CREATE TABLE rep_groups_added_monthly (
120 month int not null primary key,
121 added int not null default 0);";
124 $sql[]="DROP TABLE rep_groups_cum_daily";
125 $sql[]="CREATE TABLE rep_groups_cum_daily (
126 day int not null primary key,
127 total int not null default 0);";
129 $sql[]="DROP TABLE rep_groups_cum_weekly";
130 $sql[]="CREATE TABLE rep_groups_cum_weekly (
131 week int not null primary key,
132 total int not null default 0);";
134 $sql[]="DROP TABLE rep_groups_cum_monthly";
135 $sql[]="CREATE TABLE rep_groups_cum_monthly (
136 month int not null primary key,
137 total int not null default 0);";
140 $sql[]="DROP TABLE rep_user_act_daily";
141 $sql[]="CREATE TABLE rep_user_act_daily (
142 user_id int not null,
144 tracker_opened int not null,
145 tracker_closed int not null,
148 cvs_commits int not null,
149 tasks_opened int not null,
150 tasks_closed int not null,
151 PRIMARY KEY (user_id,day));";
153 $sql[]="DROP TABLE rep_user_act_weekly";
154 $sql[]="CREATE TABLE rep_user_act_weekly (
155 user_id int not null,
157 tracker_opened int not null,
158 tracker_closed int not null,
161 cvs_commits int not null,
162 tasks_opened int not null,
163 tasks_closed int not null,
164 PRIMARY KEY (user_id,week));";
166 $sql[]="DROP TABLE rep_user_act_monthly";
167 $sql[]="CREATE TABLE rep_user_act_monthly (
168 user_id int not null,
170 tracker_opened int not null,
171 tracker_closed int not null,
174 cvs_commits int not null,
175 tasks_opened int not null,
176 tasks_closed int not null,
177 PRIMARY KEY (user_id,month));";
179 $sql[]="DROP VIEW rep_user_act_oa_vw";
180 $sql[]="CREATE VIEW rep_user_act_oa_vw AS
182 sum(tracker_opened) AS tracker_opened,
183 sum(tracker_closed) AS tracker_closed,
186 sum(cvs_commits) AS cvs_commits,
187 sum(tasks_opened) AS tasks_opened,
188 sum(tasks_closed) AS tasks_closed
189 FROM rep_user_act_monthly
192 //per-project activity
193 $sql[]="DROP TABLE rep_group_act_daily";
194 $sql[]="CREATE TABLE rep_group_act_daily (
195 group_id int not null,
197 tracker_opened int not null,
198 tracker_closed int not null,
201 downloads int not null,
202 cvs_commits int not null,
203 tasks_opened int not null,
204 tasks_closed int not null,
205 PRIMARY KEY (group_id,day));";
207 $sql[]="DROP INDEX repgroupactdaily_day";
208 $sql[]="CREATE INDEX repgroupactdaily_day ON rep_group_act_daily(day)";
210 $sql[]="DROP TABLE rep_group_act_weekly";
211 $sql[]="CREATE TABLE rep_group_act_weekly (
212 group_id int not null,
214 tracker_opened int not null,
215 tracker_closed int not null,
218 downloads int not null,
219 cvs_commits int not null,
220 tasks_opened int not null,
221 tasks_closed int not null,
222 PRIMARY KEY (group_id,week));";
224 $sql[]="DROP INDEX repgroupactweekly_week";
225 $sql[]="CREATE INDEX repgroupactweekly_week ON rep_group_act_weekly(week)";
227 $sql[]="DROP TABLE rep_group_act_monthly";
228 $sql[]="CREATE TABLE rep_group_act_monthly (
229 group_id int not null,
231 tracker_opened int not null,
232 tracker_closed int not null,
235 downloads int not null,
236 cvs_commits int not null,
237 tasks_opened int not null,
238 tasks_closed int not null,
239 PRIMARY KEY (group_id,month));";
241 $sql[]="DROP INDEX repgroupactmonthly_month";
242 $sql[]="CREATE INDEX repgroupactmonthly_month ON rep_group_act_monthly(month)";
244 $sql[]="DROP VIEW rep_group_act_oa_vw";
245 $sql[]="CREATE VIEW rep_group_act_oa_vw AS
247 sum(tracker_opened) AS tracker_opened,
248 sum(tracker_closed) AS tracker_closed,
251 sum(downloads) AS downloads,
252 sum(cvs_commits) AS cvs_commits,
253 sum(tasks_opened) AS tasks_opened,
254 sum(tasks_closed) AS tasks_closed
255 FROM rep_group_act_monthly
259 $sql[]="DROP VIEW rep_site_act_daily_vw";
260 $sql[]="CREATE VIEW rep_site_act_daily_vw AS
262 sum(tracker_opened) AS tracker_opened,
263 sum(tracker_closed) AS tracker_closed,
266 sum(downloads) AS downloads,
267 sum(cvs_commits) AS cvs_commits,
268 sum(tasks_opened) AS tasks_opened,
269 sum(tasks_closed) AS tasks_closed
270 FROM rep_group_act_daily
273 $sql[]="DROP VIEW rep_site_act_weekly_vw";
274 $sql[]="CREATE VIEW rep_site_act_weekly_vw AS
276 sum(tracker_opened) AS tracker_opened,
277 sum(tracker_closed) AS tracker_closed,
280 sum(downloads) AS downloads,
281 sum(cvs_commits) AS cvs_commits,
282 sum(tasks_opened) AS tasks_opened,
283 sum(tasks_closed) AS tasks_closed
284 FROM rep_group_act_weekly
287 $sql[]="DROP VIEW rep_site_act_monthly_vw";
288 $sql[]="CREATE VIEW rep_site_act_monthly_vw AS
290 sum(tracker_opened) AS tracker_opened,
291 sum(tracker_closed) AS tracker_closed,
294 sum(downloads) AS downloads,
295 sum(cvs_commits) AS cvs_commits,
296 sum(tasks_opened) AS tasks_opened,
297 sum(tasks_closed) AS tasks_closed
298 FROM rep_group_act_monthly
301 $sql[]="DROP VIEW rep_site_act_oa_vw";
302 $sql[]="CREATE VIEW rep_site_act_oa_vw AS
303 sum(tracker_opened) AS tracker_opened,
304 sum(tracker_closed) AS tracker_closed,
307 sum(downloads) AS downloads,
308 sum(cvs_commits) AS cvs_commits,
309 sum(tasks_opened) AS tasks_opened,
310 sum(tasks_closed) AS tasks_closed
311 FROM rep_group_act_monthly;";
313 for ($i=0; $i<count($sql); $i++) {
315 $res=db_query_params($sql[$i], array());
321 function initialData() {
322 if (!$this->backfill_users_added_daily()) {
325 if (!$this->backfill_users_added_weekly()) {
328 if (!$this->backfill_users_added_monthly()) {
331 if (!$this->backfill_users_cum_daily()) {
334 if (!$this->backfill_users_cum_weekly()) {
337 if (!$this->backfill_users_cum_monthly()) {
340 if (!$this->backfill_groups_added_daily()) {
343 if (!$this->backfill_groups_added_weekly()) {
346 if (!$this->backfill_groups_added_monthly()) {
349 if (!$this->backfill_groups_cum_daily()) {
352 if (!$this->backfill_groups_cum_weekly()) {
355 if (!$this->backfill_groups_cum_monthly()) {
358 if (!$this->backfill_user_act_daily()) {
361 if (!$this->backfill_user_act_weekly()) {
364 if (!$this->backfill_user_act_monthly()) {
367 if (!$this->backfill_group_act_daily()) {
370 if (!$this->backfill_group_act_weekly()) {
373 if (!$this->backfill_group_act_monthly()) {
380 function dailyData() {
381 if (!$this->backfill_users_added_daily(1)) {
384 if (!$this->backfill_users_added_weekly(1)) {
387 if (!$this->backfill_users_added_monthly(2)) {
390 if (!$this->backfill_users_cum_daily(1)) {
393 if (!$this->backfill_users_cum_weekly(1)) {
396 if (!$this->backfill_users_cum_monthly(2)) {
399 if (!$this->backfill_user_act_daily(1)) {
402 if (!$this->backfill_user_act_weekly(1)) {
405 if (!$this->backfill_user_act_monthly(2)) {
408 if (!$this->backfill_group_act_daily(1)) {
411 if (!$this->backfill_group_act_weekly(1)) {
414 if (!$this->backfill_group_act_monthly(2)) {
420 * Add a row to the users_added_daily report table.
422 * @param int Day - the unix time of the beginning of the day.
423 * @return boolean Success.
425 function users_added_daily($day) {
426 db_query_params ('DELETE FROM rep_users_added_daily WHERE day=$1',
430 return db_query_params ('INSERT INTO rep_users_added_daily (day,added)
431 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
432 BETWEEN $3 AND $4 ))',
436 ($day + REPORT_DAY_SPAN - 1) ));
440 * Populate the users_added_daily report table.
442 * @return boolean Success.
444 function backfill_users_added_daily($count=10000) {
445 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
446 if (!$start_date=$this->getMinDate()) {
447 $this->setError('backfill_users_added_daily:: Could Not Get Start Date');
452 $day=($today-($i*REPORT_DAY_SPAN));
453 if (!$this->users_added_daily($day)) {
454 $this->setError('backfill_users_added_daily:: Error adding daily row: '.db_error());
457 if ($day < $start_date) {
469 * Add a row to the groups_added_daily report table.
471 * @param int Day - the unix time of the beginning of the day.
472 * @return boolean Success.
474 function groups_added_daily($day) {
475 db_query_params ('DELETE FROM rep_groups_added_daily WHERE day=$1',
479 return db_query_params ('INSERT INTO rep_groups_added_daily (day,added)
480 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
481 BETWEEN $3 AND $4 ))',
485 ($day + REPORT_DAY_SPAN - 1) ));
489 * Populate the groups_added_daily report table.
491 * @return boolean Success.
493 function backfill_groups_added_daily($count=10000) {
494 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
495 if (!$start_date=$this->getMinDate()) {
496 $this->setError('backfill_groups_added_daily:: Could Not Get Start Date');
501 $day=($today-($i*REPORT_DAY_SPAN));
502 if (!$this->groups_added_daily($day)) {
503 $this->setError('backfill_groups_added_daily:: Error adding daily row: '.db_error());
506 if ($day < $start_date) {
518 * Add a row to the users_added_weekly report table.
520 * @param int Week - the unix time of the beginning of the sunday for this week.
521 * @return boolean Success.
523 function users_added_weekly($week) {
524 db_query_params ('DELETE FROM rep_users_added_weekly WHERE week=$1',
528 return db_query_params ('INSERT INTO rep_users_added_weekly (week,added)
529 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
530 BETWEEN $3 AND $4 ))',
534 ($week+REPORT_WEEK_SPAN-1) ));
538 * Populate the users_added_weekly report table.
540 * @return boolean Success.
542 function backfill_users_added_weekly($count=10000) {
544 $arr =& $this->getWeekStartArr();
546 for ($i=0; $i<count($arr); $i++) {
547 if (!$this->users_added_weekly($arr[$i])) {
548 $this->setError('backfill_users_added_weekly:: Error adding weekly row: '.db_error());
559 * Add a row to the groups_added_weekly report table.
561 * @param int Week - the unix time of the beginning of the sunday for this week.
562 * @return boolean Success.
564 function groups_added_weekly($week) {
565 db_query_params ('DELETE FROM rep_groups_added_weekly WHERE week=$1',
569 return db_query_params ('INSERT INTO rep_groups_added_weekly (week,added)
570 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
571 BETWEEN $3 AND $4 ))',
575 ($week+REPORT_WEEK_SPAN-1) ));
579 * Populate the users_added_weekly report table.
581 * @return boolean Success.
583 function backfill_groups_added_weekly($count=10000) {
585 $arr =& $this->getWeekStartArr();
587 for ($i=0; $i<count($arr); $i++) {
588 if (!$this->groups_added_weekly($arr[$i])) {
589 $this->setError('backfill_groups_added_weekly:: Error adding weekly row: '.db_error());
600 * Add a row to the users_added_monthly report table.
602 * @param int month_start - the unix time of the beginning of the month.
603 * @param int month_end - the unix time of the end of the month.
604 * @return boolean Success.
606 function users_added_monthly($month,$end) {
607 db_query_params ('DELETE FROM rep_users_added_monthly WHERE month=$1',
611 return db_query_params ('INSERT INTO rep_users_added_monthly (month,added)
612 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
613 BETWEEN $3 AND $4 ))',
621 * Populate the users_added_monthly report table.
623 * @return boolean Success.
625 function backfill_users_added_monthly($count=10000) {
627 $arr =& $this->getMonthStartArr();
630 for ($i=1; $i<count($arr); $i++) {
631 if (!$this->users_added_monthly($arr[$i],($arr[$i-1]-1))) {
632 $this->setError('backfill_users_added_monthly:: Error adding monthly row: '.db_error());
643 * Add a row to the groups_added_monthly report table.
645 * @param int month_start - the unix time of the beginning of the month.
646 * @param int month_end - the unix time of the end of the month.
647 * @return boolean Success.
649 function groups_added_monthly($month,$end) {
650 db_query_params ('DELETE FROM rep_groups_added_monthly WHERE month=$1',
654 return db_query_params ('INSERT INTO rep_groups_added_monthly (month,added)
655 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
656 BETWEEN $3 AND $4 ))',
664 * Populate the groups_added_monthly report table.
666 * @return boolean Success.
668 function backfill_groups_added_monthly($count=10000) {
670 $arr =& $this->getMonthStartArr();
673 for ($i=1; $i<count($arr); $i++) {
674 if (!$this->groups_added_monthly($arr[$i],($arr[$i-1]-1))) {
675 $this->setError('backfill_groups_added_monthly:: Error adding monthly row: '.db_error());
686 // ******************************
690 * Add a row to the users_cum_daily report table.
692 * @param int Day - the unix time of the beginning of the day.
693 * @return boolean Success.
695 function users_cum_daily($day) {
696 db_query_params ('DELETE FROM rep_users_cum_daily WHERE day=$1',
700 return db_query_params ('INSERT INTO rep_users_cum_daily (day,total)
701 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
709 * Populate the users_cum_daily report table.
711 * @return boolean Success.
713 function backfill_users_cum_daily($count=10000) {
714 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
715 if (!$start_date=$this->getMinDate()) {
716 $this->setError('backfill_users_cum_daily:: Could Not Get Start Date');
721 $day=$today-($i*REPORT_DAY_SPAN);
722 if (!$this->users_cum_daily($day)) {
723 $this->setError('backfill_users_cum_daily:: Error adding daily row: '.db_error());
726 if ($day < $start_date) {
738 * Add a row to the groups_cum_daily report table.
740 * @param int Day - the unix time of the beginning of the day.
741 * @return boolean Success.
743 function groups_cum_daily($day) {
744 db_query_params ('DELETE FROM rep_groups_cum_daily WHERE day=$1',
748 return db_query_params ('INSERT INTO rep_groups_cum_daily (day,total)
749 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
757 * Populate the groups_cum_daily report table.
759 * @return boolean Success.
761 function backfill_groups_cum_daily($count=10000) {
762 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
763 if (!$start_date=$this->getMinDate()) {
764 $this->setError('backfill_groups_cum_daily:: Could Not Get Start Date');
769 $day=$today-($i*REPORT_DAY_SPAN);
770 if (!$this->groups_cum_daily($day)) {
771 $this->setError('backfill_groups_cum_daily:: Error adding daily row: '.db_error());
774 if ($day < $start_date) {
786 * Add a row to the users_cum_weekly report table.
788 * @param int Week - the unix time of the beginning of the sunday for this week.
789 * @return boolean Success.
791 function users_cum_weekly($week) {
792 db_query_params ('DELETE FROM rep_users_cum_weekly WHERE week=$1',
796 return db_query_params ('INSERT INTO rep_users_cum_weekly (week,total)
797 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
798 BETWEEN $3 AND $4))',
802 ($week+REPORT_WEEK_SPAN-1 )));
806 * Populate the users_cum_weekly report table.
808 * @return boolean Success.
810 function backfill_users_cum_weekly($count=10000) {
812 $arr =& $this->getWeekStartArr();
814 for ($i=0; $i<count($arr); $i++) {
815 if (!$this->groups_cum_weekly($arr[$i])) {
816 $this->setError('backfill_users_cum_weekly:: Error adding weekly row: '.db_error());
827 * Add a row to the groups_cum_weekly report table.
829 * @param int Week - the unix time of the beginning of the sunday for this week.
830 * @return boolean Success.
832 function groups_cum_weekly($week) {
833 db_query_params ('DELETE FROM rep_groups_cum_weekly WHERE week=$1',
837 return db_query_params ('INSERT INTO rep_groups_cum_weekly (week,total)
838 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
839 BETWEEN $3 AND $4))',
843 ($week+REPORT_WEEK_SPAN-1 )));
847 * Populate the groups_cum_weekly report table.
849 * @return boolean Success.
851 function backfill_groups_cum_weekly($count=10000) {
853 $arr =& $this->getWeekStartArr();
855 for ($i=0; $i<count($arr); $i++) {
856 if (!$this->users_cum_weekly($arr[$i])) {
857 $this->setError('backfill_groups_cum_weekly:: Error adding weekly row: '.db_error());
868 * Add a row to the users_cum_monthly report table.
870 * @param int month_start - the unix time of the beginning of the month.
871 * @param int month_end - the unix time of the end of the month.
872 * @return boolean Success.
874 function users_cum_monthly($month,$end) {
875 db_query_params ('DELETE FROM rep_users_cum_monthly WHERE month=$1',
879 return db_query_params ('INSERT INTO rep_users_cum_monthly (month,total)
880 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
888 * Populate the users_cum_monthly report table.
890 * @return boolean Success.
892 function backfill_users_cum_monthly($count=10000) {
894 $arr =& $this->getMonthStartArr();
897 for ($i=1; $i<count($arr); $i++) {
898 if (!$this->users_cum_monthly($arr[$i],($arr[$i-1]-1))) {
899 $this->setError('backfill_users_cum_monthly:: Error adding monthly row: '.db_error());
910 * Add a row to the groups_cum_monthly report table.
912 * @param int month_start - the unix time of the beginning of the month.
913 * @param int month_end - the unix time of the end of the month.
914 * @return boolean Success.
916 function groups_cum_monthly($month,$end) {
917 db_query_params ('DELETE FROM rep_groups_cum_monthly WHERE month=$1',
921 return db_query_params ('INSERT INTO rep_groups_cum_monthly (month,total)
922 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
930 * Populate the groups_cum_monthly report table.
932 * @return boolean Success.
934 function backfill_groups_cum_monthly($count=10000) {
936 $arr =& $this->getMonthStartArr();
939 for ($i=1; $i<count($arr); $i++) {
940 if (!$this->groups_cum_monthly($arr[$i],($arr[$i-1]-1))) {
941 $this->setError('backfill_groups_cum_monthly:: Error adding monthly row: '.db_error());
952 // ************************
956 * Add a row to the user_act_daily report table.
958 * @param int Day - the unix time of the beginning of the day.
959 * @return boolean Success.
961 function user_act_daily($day) {
962 global $sys_database_type;
964 db_query_params ('DELETE FROM rep_user_act_daily WHERE day=$1',
967 $end_day=$day+REPORT_DAY_SPAN-1;
969 return db_query_params ('INSERT INTO rep_user_act_daily
970 SELECT user_id,day,coalesce(tracker_opened,0) AS tracker_opened,
971 coalesce(tracker_closed,0) AS tracker_closed,
972 coalesce(forum,0) AS forum,
973 coalesce(docs,0) AS docs,
974 coalesce(cvs_commits,0) AS cvs_commits,
975 coalesce(tasks_opened,0) AS tasks_opened,
976 coalesce(tasks_closed,0) AS tasks_closed
984 (SELECT submitted_by AS user_id, $1 AS day, count(*) AS tracker_opened
986 WHERE open_date BETWEEN $1 AND $2
987 GROUP BY user_id,day) aopen
990 (SELECT assigned_to AS user_id, $1 AS day, count(*) AS tracker_closed
992 WHERE close_date BETWEEN $1 AND $2
993 GROUP BY user_id,day ) aclosed USING (user_id,day)) foo1
996 (SELECT posted_by AS user_id, $1 AS day, count(*) AS forum
998 WHERE post_date BETWEEN $1 AND $2
999 GROUP BY user_id,day ) forum USING (user_id,day)) foo2
1002 (SELECT created_by AS user_id, $1 AS day, count(*) AS docs
1004 WHERE createdate BETWEEN $1 AND $2
1005 GROUP BY user_id,day ) docs USING (user_id,day)) foo3
1008 (SELECT user_id,$1 AS day, sum(commits) AS cvs_commits
1010 WHERE month=$3 AND day=$2
1011 GROUP BY user_id,day ) cvs USING (user_id,day)) foo4
1014 (SELECT created_by AS user_id, $1 AS day, count(*) AS tasks_opened
1016 WHERE start_date BETWEEN $1 AND $2
1017 GROUP BY user_id,day ) topen USING (user_id,day)) foo5
1020 (SELECT mod_by AS user_id, $1 AS day, count(*) AS tasks_closed
1021 FROM project_history
1022 WHERE mod_date BETWEEN $1 AND $2
1023 AND old_value=$4 AND field_name=$5
1024 GROUP BY user_id,day ) tclosed USING (user_id,day)) foo6',
1033 * Populate the user_act_daily report table.
1035 * @return boolean Success.
1037 function backfill_user_act_daily($count=10000) {
1038 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1039 if (!$start_date=$this->getMinDate()) {
1040 $this->setError('backfill_user_act_daily:: Could Not Get Start Date');
1045 $day=$today-($i*REPORT_DAY_SPAN);
1046 if (!$this->user_act_daily($day)) {
1047 $this->setError('backfill_user_act_daily:: Error adding daily row: '.db_error());
1050 if ($day < $start_date) {
1062 * Add a row to the user_act_weekly report table.
1064 * @param int Week - the unix time of the beginning of the sunday for this week.
1065 * @return boolean Success.
1067 function user_act_weekly($week) {
1068 global $sys_database_type;
1070 db_query_params ('DELETE FROM rep_user_act_weekly WHERE week=$1',
1073 return db_query_params ('
1074 INSERT INTO rep_user_act_weekly (user_id, week, tracker_opened, tracker_closed,
1075 forum, docs, cvs_commits, tasks_opened, tasks_closed)
1076 SELECT user_id,$1 AS week, sum(tracker_opened) AS tracker_opened,
1077 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1078 sum(docs) AS docs, sum(cvs_commits) AS cvs_commits,
1079 sum(tasks_opened) AS tasks_opened, sum(tasks_closed) AS tasks_closed
1080 FROM rep_user_act_daily
1081 WHERE DAY BETWEEN $1 AND $2
1082 GROUP BY user_id,week',
1084 $week+REPORT_WEEK_SPAN-1)) ;
1088 * Populate the user_act_weekly report table.
1090 * @return boolean Success.
1092 function backfill_user_act_weekly($count=10000) {
1094 $arr =& $this->getWeekStartArr();
1096 for ($i=0; $i<count($arr); $i++) {
1097 if (!$this->user_act_weekly($arr[$i])) {
1098 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1109 * Add a row to the user_act_monthly report table.
1111 * @param int month_start - the unix time of the beginning of the month.
1112 * @param int month_end - the unix time of the end of the month.
1113 * @return boolean Success.
1115 function user_act_monthly($month,$end) {
1116 global $sys_database_type;
1118 db_query_params ('DELETE FROM rep_user_act_monthly WHERE month=$1',
1121 return db_query_params ('
1122 INSERT INTO rep_user_act_monthly (user_id, month, tracker_opened,
1123 tracker_closed, forum, docs, cvs_commits, tasks_opened, tasks_closed)
1124 SELECT user_id, $1 AS month, sum(tracker_opened) AS tracker_opened,
1125 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1126 sum(docs) AS docs, sum(cvs_commits) AS cvs_commits,
1127 sum(tasks_opened) AS tasks_opened, sum(tasks_closed) AS tasks_closed
1128 FROM rep_user_act_daily
1129 WHERE DAY BETWEEN $1 AND $2
1130 GROUP BY user_id, month',
1131 array ($month, $end)) ;
1135 * Populate the user_act_monthly report table.
1137 * @return boolean Success.
1139 function backfill_user_act_monthly($count=10000) {
1141 $arr =& $this->getMonthStartArr();
1143 for ($i=1; $i<count($arr); $i++) {
1144 if (!$this->user_act_monthly($arr[$i],($arr[$i-1]-1))) {
1145 $this->setError('backfill_user_act_monthly:: Error adding monthly row: '.db_error());
1155 // ************************
1159 * Add a row to the group_act_daily report table.
1161 * @param int Day - the unix time of the beginning of the day.
1162 * @return boolean Success.
1164 function group_act_daily($day) {
1165 global $sys_database_type;
1167 db_query_params ('DELETE FROM rep_group_act_daily WHERE day=$1',
1170 $end_day=$day+REPORT_DAY_SPAN-1;
1172 return db_query_params ('INSERT INTO rep_group_act_daily
1173 SELECT group_id,day,coalesce(tracker_opened,0) AS tracker_opened,
1174 coalesce(tracker_closed,0) AS tracker_closed,
1175 coalesce(forum,0) AS forum,
1176 coalesce(docs,0) AS docs,
1177 coalesce(downloads,0) AS downloads,
1178 coalesce(cvs_commits,0) AS cvs_commits,
1179 coalesce(tasks_opened,0) AS tasks_opened,
1180 coalesce(tasks_closed,0) AS tasks_closed
1189 (SELECT agl.group_id, $1 AS day, count(*) AS tracker_opened
1190 FROM artifact a, artifact_group_list agl
1191 WHERE a.open_date BETWEEN $1 AND $2
1192 AND a.group_artifact_id=agl.group_artifact_id
1193 GROUP BY group_id,day) aopen
1196 (SELECT agl.group_id, $1 AS day, count(*) AS tracker_closed
1197 FROM artifact a, artifact_group_list agl
1198 WHERE a.close_date BETWEEN $1 AND $2
1199 AND a.group_artifact_id=agl.group_artifact_id
1200 GROUP BY group_id,day ) aclosed USING (group_id,day)) foo1
1203 (SELECT fgl.group_id, $1 AS day, count(*) AS forum
1204 FROM forum f, forum_group_list fgl
1205 WHERE f.post_date BETWEEN $1 AND $2
1206 AND f.group_forum_id=fgl.group_forum_id
1207 GROUP BY group_id,day ) forum USING (group_id,day)) foo2
1210 (SELECT group_id, $1 AS day, count(*) AS docs
1212 WHERE createdate BETWEEN $1 AND $2
1213 GROUP BY group_id,day ) docs USING (group_id,day)) foo3
1216 (SELECT fp.group_id, $1 AS day, count(*) AS downloads
1217 FROM frs_package fp, frs_release fr, frs_file ff, frs_dlstats_file fdf
1218 WHERE fp.package_id=fr.package_id
1219 AND fr.release_id=ff.release_id
1220 AND ff.file_id=fdf.file_id
1221 AND fdf.month = $3 AND fdf.day = $4
1222 GROUP BY fp.group_id,day ) docs USING (group_id,day)) foo4
1225 (SELECT group_id, $1 AS day, sum(commits) AS cvs_commits
1226 FROM stats_cvs_group
1227 WHERE month=$3 AND day=$4
1228 GROUP BY group_id,day ) cvs USING (group_id,day)) foo5
1231 (SELECT pgl.group_id, $1 AS day,count(*) AS tasks_opened
1232 FROM project_task pt, project_group_list pgl
1233 WHERE pt.start_date BETWEEN $1 AND $2
1234 AND pt.group_project_id=pgl.group_project_id
1235 GROUP BY group_id,day ) topen USING (group_id,day)) foo6
1238 (SELECT pgl.group_id, $1 AS day, count(*) AS tasks_closed
1239 FROM project_history ph, project_task pt, project_group_list pgl
1240 WHERE ph.mod_date BETWEEN $1 AND $2
1242 AND ph.field_name=$6
1243 AND ph.project_task_id=pt.project_task_id
1244 AND pt.group_project_id=pgl.group_project_id
1245 GROUP BY group_id,day ) tclosed USING (group_id,day)) foo7',
1257 * Populate the group_act_daily report table.
1259 * @return boolean Success.
1261 function backfill_group_act_daily($count=10000) {
1262 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1263 if (!$start_date=$this->getMinDate()) {
1264 $this->setError('backfill_group_act_daily:: Could Not Get Start Date');
1269 $day=$today-($i*REPORT_DAY_SPAN);
1270 if (!$this->group_act_daily($day)) {
1271 $this->setError('backfill_group_act_daily:: Error adding daily row: '.db_error());
1274 if ($day < $start_date) {
1286 * Add a row to the group_act_weekly report table.
1288 * @param int Week - the unix time of the beginning of the sunday for this week.
1289 * @return boolean Success.
1291 function group_act_weekly($week) {
1292 global $sys_database_type;
1294 db_query_params ('DELETE FROM rep_group_act_weekly WHERE week=$1',
1297 return db_query_params ('
1298 INSERT INTO rep_group_act_weekly (group_id, week, tracker_opened,
1299 tracker_closed, forum, docs, downloads, cvs_commits, tasks_opened,
1301 SELECT group_id, $1 AS week, sum(tracker_opened) AS tracker_opened,
1302 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1303 sum(docs) AS docs, sum(downloads) AS downloads,
1304 sum(cvs_commits) AS cvs_commits, sum(tasks_opened) AS tasks_opened,
1305 sum(tasks_closed) AS tasks_closed
1306 FROM rep_group_act_daily
1307 WHERE DAY BETWEEN $1 AND $2
1308 GROUP BY group_id, week',
1310 $week+REPORT_WEEK_SPAN-1)) ;
1314 * Populate the group_act_weekly report table.
1316 * @return boolean Success.
1318 function backfill_group_act_weekly($count=10000) {
1320 $arr =& $this->getWeekStartArr();
1322 for ($i=0; $i<count($arr); $i++) {
1323 if (!$this->group_act_weekly($arr[$i])) {
1324 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1335 * Add a row to the group_act_monthly report table.
1337 * @param int month_start - the unix time of the beginning of the month.
1338 * @param int month_end - the unix time of the end of the month.
1339 * @return boolean Success.
1341 function group_act_monthly($month,$end) {
1342 global $sys_database_type;
1344 db_query_params ('DELETE FROM rep_group_act_monthly WHERE month=$1',
1347 return db_query_params ('
1348 INSERT INTO rep_group_act_monthly (group_id, month, tracker_opened,
1349 tracker_closed, forum, docs, downloads, cvs_commits, tasks_opened,
1351 SELECT group_id, $1 AS month, sum(tracker_opened) AS tracker_opened,
1352 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1353 sum(docs) AS docs, sum(downloads) AS downloads,
1354 sum(cvs_commits) AS cvs_commits,
1355 sum(tasks_opened) AS tasks_opened,
1356 sum(tasks_closed) AS tasks_closed
1357 FROM rep_group_act_daily
1358 WHERE DAY BETWEEN $1 AND $2
1359 GROUP BY group_id,month',
1360 array ($month, $end)) ;
1364 * Populate the group_act_monthly report table.
1366 * @return boolean Success.
1368 function backfill_group_act_monthly($count=10000) {
1370 $arr =& $this->getMonthStartArr();
1372 for ($i=1; $i<count($arr); $i++) {
1373 if (!$this->group_act_monthly($arr[$i],($arr[$i-1]-1))) {
1374 $this->setError('backfill_group_act_monthly:: Error adding monthly row: '.db_error());
1385 * Add a row to the rep_time_category table.
1387 * @param string The category name.
1388 * @return boolean Success.
1390 function addTimeCode($category_name) {
1391 return db_query_params ('INSERT INTO rep_time_category (category_name) VALUES ($1)',
1392 array($category_name)) ;
1396 * Update the rep_time_category table.
1398 * @param string The category name.
1399 * @return boolean Success.
1401 function updateTimeCode($time_code, $category_name) {
1402 return db_query_params ('UPDATE rep_time_category SET category_name=$1 WHERE time_code=$2',
1403 array($category_name,
1411 // c-file-style: "bsd"