3 * FusionForge reporting system
5 * Copyright 2003-2004, Tim Perdue/GForge, LLC
7 * This file is part of FusionForge. FusionForge is free software;
8 * you can redistribute it and/or modify it under the terms of the
9 * GNU General Public License as published by the Free Software
10 * Foundation; either version 2 of the Licence, or (at your option)
13 * FusionForge is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
18 * You should have received a copy of the GNU General Public License along
19 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
20 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
23 require_once $gfcommon.'reporting/Report.class.php';
25 class ReportSetup extends Report {
27 function ReportSetup() {
32 function initialSetup() {
33 $this->createTables();
34 if (!$this->initialData()) {
41 function createTables() {
43 //DROP TABLE rep_time_category;
44 $sql[]="CREATE TABLE rep_time_category (
45 time_code serial UNIQUE,
48 //$sql[]="DROP TABLE rep_time_tracking;";
49 $sql1="CREATE TABLE rep_time_tracking (
51 report_date int not null,
53 project_task_id int not null,
54 time_code int not null CONSTRAINT reptimetrk_timecode REFERENCES rep_time_category(time_code),
55 hours float not null);";
57 // $sql[]="CREATE UNIQUE INDEX reptimetrk_weekusrtskcde ON
58 // rep_time_tracking (week,user_id,project_task_id,time_code);";
59 $sql[]="CREATE INDEX reptimetracking_userdate ON
60 rep_time_tracking (user_id,week);";
62 $sql[]="INSERT INTO rep_time_category VALUES ('1','Coding');";
63 $sql[]="INSERT INTO rep_time_category VALUES ('2','Testing');";
64 $sql[]="INSERT INTO rep_time_category VALUES ('3','Meeting');";
65 $sql[]="SELECT setval('rep_time_category_time_code_seq',(SELECT max(time_code) FROM rep_time_category));";
68 $sql[]="DROP TABLE rep_users_added_daily;";
69 $sql[]="CREATE TABLE rep_users_added_daily (
70 day int not null primary key,
71 added int not null default 0);";
73 $sql[]="DROP TABLE rep_users_added_weekly";
74 $sql[]="CREATE TABLE rep_users_added_weekly (
75 week int not null primary key,
76 added int not null default 0);";
78 $sql[]="DROP TABLE rep_users_added_monthly";
79 $sql[]="CREATE TABLE rep_users_added_monthly (
80 month int not null primary key,
81 added int not null default 0);";
84 $sql[]="DROP TABLE rep_users_cum_daily";
85 $sql[]="CREATE TABLE rep_users_cum_daily (
86 day int not null primary key,
87 total int not null default 0);";
89 $sql[]="DROP TABLE rep_users_cum_weekly";
90 $sql[]="CREATE TABLE rep_users_cum_weekly (
91 week int not null primary key,
92 total int not null default 0);";
94 $sql[]="DROP TABLE rep_users_cum_monthly";
95 $sql[]="CREATE TABLE rep_users_cum_monthly (
96 month int not null primary key,
97 total int not null default 0);";
100 $sql[]="DROP TABLE rep_groups_added_daily;";
101 $sql[]="CREATE TABLE rep_groups_added_daily (
102 day int not null primary key,
103 added int not null default 0);";
105 $sql[]="DROP TABLE rep_groups_added_weekly";
106 $sql[]="CREATE TABLE rep_groups_added_weekly (
107 week int not null primary key,
108 added int not null default 0);";
110 $sql[]="DROP TABLE rep_groups_added_monthly";
111 $sql[]="CREATE TABLE rep_groups_added_monthly (
112 month int not null primary key,
113 added int not null default 0);";
116 $sql[]="DROP TABLE rep_groups_cum_daily";
117 $sql[]="CREATE TABLE rep_groups_cum_daily (
118 day int not null primary key,
119 total int not null default 0);";
121 $sql[]="DROP TABLE rep_groups_cum_weekly";
122 $sql[]="CREATE TABLE rep_groups_cum_weekly (
123 week int not null primary key,
124 total int not null default 0);";
126 $sql[]="DROP TABLE rep_groups_cum_monthly";
127 $sql[]="CREATE TABLE rep_groups_cum_monthly (
128 month int not null primary key,
129 total int not null default 0);";
132 $sql[]="DROP TABLE rep_user_act_daily";
133 $sql[]="CREATE TABLE rep_user_act_daily (
134 user_id int not null,
136 tracker_opened int not null,
137 tracker_closed int not null,
140 cvs_commits int not null,
141 tasks_opened int not null,
142 tasks_closed int not null,
143 PRIMARY KEY (user_id,day));";
145 $sql[]="DROP TABLE rep_user_act_weekly";
146 $sql[]="CREATE TABLE rep_user_act_weekly (
147 user_id int not null,
149 tracker_opened int not null,
150 tracker_closed int not null,
153 cvs_commits int not null,
154 tasks_opened int not null,
155 tasks_closed int not null,
156 PRIMARY KEY (user_id,week));";
158 $sql[]="DROP TABLE rep_user_act_monthly";
159 $sql[]="CREATE TABLE rep_user_act_monthly (
160 user_id int not null,
162 tracker_opened int not null,
163 tracker_closed int not null,
166 cvs_commits int not null,
167 tasks_opened int not null,
168 tasks_closed int not null,
169 PRIMARY KEY (user_id,month));";
171 $sql[]="DROP VIEW rep_user_act_oa_vw";
172 $sql[]="CREATE VIEW rep_user_act_oa_vw AS
174 sum(tracker_opened) AS tracker_opened,
175 sum(tracker_closed) AS tracker_closed,
178 sum(cvs_commits) AS cvs_commits,
179 sum(tasks_opened) AS tasks_opened,
180 sum(tasks_closed) AS tasks_closed
181 FROM rep_user_act_monthly
184 //per-project activity
185 $sql[]="DROP TABLE rep_group_act_daily";
186 $sql[]="CREATE TABLE rep_group_act_daily (
187 group_id int not null,
189 tracker_opened int not null,
190 tracker_closed int not null,
193 downloads int not null,
194 cvs_commits int not null,
195 tasks_opened int not null,
196 tasks_closed int not null,
197 PRIMARY KEY (group_id,day));";
199 $sql[]="DROP INDEX repgroupactdaily_day";
200 $sql[]="CREATE INDEX repgroupactdaily_day ON rep_group_act_daily(day)";
202 $sql[]="DROP TABLE rep_group_act_weekly";
203 $sql[]="CREATE TABLE rep_group_act_weekly (
204 group_id int not null,
206 tracker_opened int not null,
207 tracker_closed int not null,
210 downloads int not null,
211 cvs_commits int not null,
212 tasks_opened int not null,
213 tasks_closed int not null,
214 PRIMARY KEY (group_id,week));";
216 $sql[]="DROP INDEX repgroupactweekly_week";
217 $sql[]="CREATE INDEX repgroupactweekly_week ON rep_group_act_weekly(week)";
219 $sql[]="DROP TABLE rep_group_act_monthly";
220 $sql[]="CREATE TABLE rep_group_act_monthly (
221 group_id int not null,
223 tracker_opened int not null,
224 tracker_closed int not null,
227 downloads int not null,
228 cvs_commits int not null,
229 tasks_opened int not null,
230 tasks_closed int not null,
231 PRIMARY KEY (group_id,month));";
233 $sql[]="DROP INDEX repgroupactmonthly_month";
234 $sql[]="CREATE INDEX repgroupactmonthly_month ON rep_group_act_monthly(month)";
236 $sql[]="DROP VIEW rep_group_act_oa_vw";
237 $sql[]="CREATE VIEW rep_group_act_oa_vw AS
239 sum(tracker_opened) AS tracker_opened,
240 sum(tracker_closed) AS tracker_closed,
243 sum(downloads) AS downloads,
244 sum(cvs_commits) AS cvs_commits,
245 sum(tasks_opened) AS tasks_opened,
246 sum(tasks_closed) AS tasks_closed
247 FROM rep_group_act_monthly
251 $sql[]="DROP VIEW rep_site_act_daily_vw";
252 $sql[]="CREATE VIEW rep_site_act_daily_vw AS
254 sum(tracker_opened) AS tracker_opened,
255 sum(tracker_closed) AS tracker_closed,
258 sum(downloads) AS downloads,
259 sum(cvs_commits) AS cvs_commits,
260 sum(tasks_opened) AS tasks_opened,
261 sum(tasks_closed) AS tasks_closed
262 FROM rep_group_act_daily
265 $sql[]="DROP VIEW rep_site_act_weekly_vw";
266 $sql[]="CREATE VIEW rep_site_act_weekly_vw AS
268 sum(tracker_opened) AS tracker_opened,
269 sum(tracker_closed) AS tracker_closed,
272 sum(downloads) AS downloads,
273 sum(cvs_commits) AS cvs_commits,
274 sum(tasks_opened) AS tasks_opened,
275 sum(tasks_closed) AS tasks_closed
276 FROM rep_group_act_weekly
279 $sql[]="DROP VIEW rep_site_act_monthly_vw";
280 $sql[]="CREATE VIEW rep_site_act_monthly_vw AS
282 sum(tracker_opened) AS tracker_opened,
283 sum(tracker_closed) AS tracker_closed,
286 sum(downloads) AS downloads,
287 sum(cvs_commits) AS cvs_commits,
288 sum(tasks_opened) AS tasks_opened,
289 sum(tasks_closed) AS tasks_closed
290 FROM rep_group_act_monthly
293 $sql[]="DROP VIEW rep_site_act_oa_vw";
294 $sql[]="CREATE VIEW rep_site_act_oa_vw AS
296 sum(tracker_opened) AS tracker_opened,
297 sum(tracker_closed) AS tracker_closed,
300 sum(downloads) AS downloads,
301 sum(cvs_commits) AS cvs_commits,
302 sum(tasks_opened) AS tasks_opened,
303 sum(tasks_closed) AS tasks_closed
304 FROM rep_group_act_monthly;";
306 for ($i=0; $i<count($sql); $i++) {
308 $res=db_query_params($sql[$i], array());
314 function initialData() {
315 if (!$this->backfill_users_added_daily()) {
318 if (!$this->backfill_users_added_weekly()) {
321 if (!$this->backfill_users_added_monthly()) {
324 if (!$this->backfill_users_cum_daily()) {
327 if (!$this->backfill_users_cum_weekly()) {
330 if (!$this->backfill_users_cum_monthly()) {
333 if (!$this->backfill_groups_added_daily()) {
336 if (!$this->backfill_groups_added_weekly()) {
339 if (!$this->backfill_groups_added_monthly()) {
342 if (!$this->backfill_groups_cum_daily()) {
345 if (!$this->backfill_groups_cum_weekly()) {
348 if (!$this->backfill_groups_cum_monthly()) {
351 if (!$this->backfill_user_act_daily()) {
354 if (!$this->backfill_user_act_weekly()) {
357 if (!$this->backfill_user_act_monthly()) {
360 if (!$this->backfill_group_act_daily()) {
363 if (!$this->backfill_group_act_weekly()) {
366 if (!$this->backfill_group_act_monthly()) {
373 function dailyData() {
374 if (!$this->backfill_users_added_daily(1)) {
377 if (!$this->backfill_users_added_weekly(1)) {
380 if (!$this->backfill_users_added_monthly(2)) {
383 if (!$this->backfill_users_cum_daily(1)) {
386 if (!$this->backfill_users_cum_weekly(1)) {
389 if (!$this->backfill_users_cum_monthly(2)) {
392 if (!$this->backfill_user_act_daily(1)) {
395 if (!$this->backfill_user_act_weekly(1)) {
398 if (!$this->backfill_user_act_monthly(2)) {
401 if (!$this->backfill_group_act_daily(1)) {
404 if (!$this->backfill_group_act_weekly(1)) {
407 if (!$this->backfill_group_act_monthly(2)) {
413 * Add a row to the users_added_daily report table.
415 * @param int Day - the unix time of the beginning of the day.
416 * @return boolean Success.
418 function users_added_daily($day) {
419 db_query_params ('DELETE FROM rep_users_added_daily WHERE day=$1',
423 return db_query_params ('INSERT INTO rep_users_added_daily (day,added)
424 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
425 BETWEEN $3 AND $4 ))',
429 ($day + REPORT_DAY_SPAN - 1) ));
433 * Populate the users_added_daily report table.
435 * @return boolean Success.
437 function backfill_users_added_daily($count=10000) {
438 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
439 if (!$start_date=$this->getMinDate()) {
440 $this->setError('backfill_users_added_daily:: Could Not Get Start Date');
445 $day=($today-($i*REPORT_DAY_SPAN));
446 if (!$this->users_added_daily($day)) {
447 $this->setError('backfill_users_added_daily:: Error adding daily row: '.db_error());
450 if ($day < $start_date) {
462 * Add a row to the groups_added_daily report table.
464 * @param int Day - the unix time of the beginning of the day.
465 * @return boolean Success.
467 function groups_added_daily($day) {
468 db_query_params ('DELETE FROM rep_groups_added_daily WHERE day=$1',
472 return db_query_params ('INSERT INTO rep_groups_added_daily (day,added)
473 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
474 BETWEEN $3 AND $4 ))',
478 ($day + REPORT_DAY_SPAN - 1) ));
482 * Populate the groups_added_daily report table.
484 * @return boolean Success.
486 function backfill_groups_added_daily($count=10000) {
487 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
488 if (!$start_date=$this->getMinDate()) {
489 $this->setError('backfill_groups_added_daily:: Could Not Get Start Date');
494 $day=($today-($i*REPORT_DAY_SPAN));
495 if (!$this->groups_added_daily($day)) {
496 $this->setError('backfill_groups_added_daily:: Error adding daily row: '.db_error());
499 if ($day < $start_date) {
511 * Add a row to the users_added_weekly report table.
513 * @param int Week - the unix time of the beginning of the sunday for this week.
514 * @return boolean Success.
516 function users_added_weekly($week) {
517 db_query_params ('DELETE FROM rep_users_added_weekly WHERE week=$1',
521 return db_query_params ('INSERT INTO rep_users_added_weekly (week,added)
522 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
523 BETWEEN $3 AND $4 ))',
527 ($week+REPORT_WEEK_SPAN-1) ));
531 * Populate the users_added_weekly report table.
533 * @return boolean Success.
535 function backfill_users_added_weekly($count=10000) {
537 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
540 for ($i=0; $i<count($arr); $i++) {
541 if (!$this->users_added_weekly($arr[$i])) {
542 $this->setError('backfill_users_added_weekly:: Error adding weekly row: '.db_error());
550 * Add a row to the groups_added_weekly report table.
552 * @param int Week - the unix time of the beginning of the sunday for this week.
553 * @return boolean Success.
555 function groups_added_weekly($week) {
556 db_query_params ('DELETE FROM rep_groups_added_weekly WHERE week=$1',
560 return db_query_params ('INSERT INTO rep_groups_added_weekly (week,added)
561 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
562 BETWEEN $3 AND $4 ))',
566 ($week+REPORT_WEEK_SPAN-1) ));
570 * Populate the users_added_weekly report table.
572 * @return boolean Success.
574 function backfill_groups_added_weekly($count=10000) {
576 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
579 for ($i=0; $i<count($arr); $i++) {
580 if (!$this->groups_added_weekly($arr[$i])) {
581 $this->setError('backfill_groups_added_weekly:: Error adding weekly row: '.db_error());
589 * Add a row to the users_added_monthly report table.
591 * @param int month_start - the unix time of the beginning of the month.
592 * @param int month_end - the unix time of the end of the month.
593 * @return boolean Success.
595 function users_added_monthly($month,$end) {
596 db_query_params ('DELETE FROM rep_users_added_monthly WHERE month=$1',
600 return db_query_params ('INSERT INTO rep_users_added_monthly (month,added)
601 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
602 BETWEEN $3 AND $4 ))',
610 * Populate the users_added_monthly report table.
612 * @return boolean Success.
614 function backfill_users_added_monthly($count=10000) {
616 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
620 for ($i=1; $i<count($arr); $i++) {
621 if (!$this->users_added_monthly($arr[$i],($arr[$i-1]-1))) {
622 $this->setError('backfill_users_added_monthly:: Error adding monthly row: '.db_error());
630 * Add a row to the groups_added_monthly report table.
632 * @param int month_start - the unix time of the beginning of the month.
633 * @param int month_end - the unix time of the end of the month.
634 * @return boolean Success.
636 function groups_added_monthly($month,$end) {
637 db_query_params ('DELETE FROM rep_groups_added_monthly WHERE month=$1',
641 return db_query_params ('INSERT INTO rep_groups_added_monthly (month,added)
642 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
643 BETWEEN $3 AND $4 ))',
651 * Populate the groups_added_monthly report table.
653 * @return boolean Success.
655 function backfill_groups_added_monthly($count=10000) {
657 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
661 for ($i=1; $i<count($arr); $i++) {
662 if (!$this->groups_added_monthly($arr[$i],($arr[$i-1]-1))) {
663 $this->setError('backfill_groups_added_monthly:: Error adding monthly row: '.db_error());
671 // ******************************
675 * Add a row to the users_cum_daily report table.
677 * @param int Day - the unix time of the beginning of the day.
678 * @return boolean Success.
680 function users_cum_daily($day) {
681 db_query_params ('DELETE FROM rep_users_cum_daily WHERE day=$1',
685 return db_query_params ('INSERT INTO rep_users_cum_daily (day,total)
686 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
694 * Populate the users_cum_daily report table.
696 * @return boolean Success.
698 function backfill_users_cum_daily($count=10000) {
699 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
700 if (!$start_date=$this->getMinDate()) {
701 $this->setError('backfill_users_cum_daily:: Could Not Get Start Date');
706 $day=$today-($i*REPORT_DAY_SPAN);
707 if (!$this->users_cum_daily($day)) {
708 $this->setError('backfill_users_cum_daily:: Error adding daily row: '.db_error());
711 if ($day < $start_date) {
723 * Add a row to the groups_cum_daily report table.
725 * @param int Day - the unix time of the beginning of the day.
726 * @return boolean Success.
728 function groups_cum_daily($day) {
729 db_query_params ('DELETE FROM rep_groups_cum_daily WHERE day=$1',
733 return db_query_params ('INSERT INTO rep_groups_cum_daily (day,total)
734 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
742 * Populate the groups_cum_daily report table.
744 * @return boolean Success.
746 function backfill_groups_cum_daily($count=10000) {
747 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
748 if (!$start_date=$this->getMinDate()) {
749 $this->setError('backfill_groups_cum_daily:: Could Not Get Start Date');
754 $day=$today-($i*REPORT_DAY_SPAN);
755 if (!$this->groups_cum_daily($day)) {
756 $this->setError('backfill_groups_cum_daily:: Error adding daily row: '.db_error());
759 if ($day < $start_date) {
771 * Add a row to the users_cum_weekly report table.
773 * @param int Week - the unix time of the beginning of the sunday for this week.
774 * @return boolean Success.
776 function users_cum_weekly($week) {
777 db_query_params ('DELETE FROM rep_users_cum_weekly WHERE week=$1',
781 return db_query_params ('INSERT INTO rep_users_cum_weekly (week,total)
782 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
783 BETWEEN $3 AND $4))',
787 ($week+REPORT_WEEK_SPAN-1 )));
791 * Populate the users_cum_weekly report table.
793 * @return boolean Success.
795 function backfill_users_cum_weekly($count=10000) {
797 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
800 for ($i=0; $i<count($arr); $i++) {
801 if (!$this->groups_cum_weekly($arr[$i])) {
802 $this->setError('backfill_users_cum_weekly:: Error adding weekly row: '.db_error());
810 * Add a row to the groups_cum_weekly report table.
812 * @param int Week - the unix time of the beginning of the sunday for this week.
813 * @return boolean Success.
815 function groups_cum_weekly($week) {
816 db_query_params ('DELETE FROM rep_groups_cum_weekly WHERE week=$1',
820 return db_query_params ('INSERT INTO rep_groups_cum_weekly (week,total)
821 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
822 BETWEEN $3 AND $4))',
826 ($week+REPORT_WEEK_SPAN-1 )));
830 * Populate the groups_cum_weekly report table.
832 * @return boolean Success.
834 function backfill_groups_cum_weekly($count=10000) {
836 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
839 for ($i=0; $i<count($arr); $i++) {
840 if (!$this->users_cum_weekly($arr[$i])) {
841 $this->setError('backfill_groups_cum_weekly:: Error adding weekly row: '.db_error());
849 * Add a row to the users_cum_monthly report table.
851 * @param int month_start - the unix time of the beginning of the month.
852 * @param int month_end - the unix time of the end of the month.
853 * @return boolean Success.
855 function users_cum_monthly($month,$end) {
856 db_query_params ('DELETE FROM rep_users_cum_monthly WHERE month=$1',
860 return db_query_params ('INSERT INTO rep_users_cum_monthly (month,total)
861 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
869 * Populate the users_cum_monthly report table.
871 * @return boolean Success.
873 function backfill_users_cum_monthly($count=10000) {
875 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
879 for ($i=1; $i<count($arr); $i++) {
880 if (!$this->users_cum_monthly($arr[$i],($arr[$i-1]-1))) {
881 $this->setError('backfill_users_cum_monthly:: Error adding monthly row: '.db_error());
889 * Add a row to the groups_cum_monthly report table.
891 * @param int month_start - the unix time of the beginning of the month.
892 * @param int month_end - the unix time of the end of the month.
893 * @return boolean Success.
895 function groups_cum_monthly($month,$end) {
896 db_query_params ('DELETE FROM rep_groups_cum_monthly WHERE month=$1',
900 return db_query_params ('INSERT INTO rep_groups_cum_monthly (month,total)
901 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
909 * Populate the groups_cum_monthly report table.
911 * @return boolean Success.
913 function backfill_groups_cum_monthly($count=10000) {
915 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
919 for ($i=1; $i<count($arr); $i++) {
920 if (!$this->groups_cum_monthly($arr[$i],($arr[$i-1]-1))) {
921 $this->setError('backfill_groups_cum_monthly:: Error adding monthly row: '.db_error());
929 // ************************
933 * Add a row to the user_act_daily report table.
935 * @param int Day - the unix time of the beginning of the day.
936 * @return boolean Success.
938 function user_act_daily($day) {
939 db_query_params ('DELETE FROM rep_user_act_daily WHERE day=$1',
942 $end_day=$day+REPORT_DAY_SPAN-1;
944 return db_query_params ('INSERT INTO rep_user_act_daily
945 SELECT user_id,day,coalesce(tracker_opened,0) AS tracker_opened,
946 coalesce(tracker_closed,0) AS tracker_closed,
947 coalesce(forum,0) AS forum,
948 coalesce(docs,0) AS docs,
949 coalesce(cvs_commits,0) AS cvs_commits,
950 coalesce(tasks_opened,0) AS tasks_opened,
951 coalesce(tasks_closed,0) AS tasks_closed
959 (SELECT submitted_by AS user_id, $1::int AS day, count(*) AS tracker_opened
961 WHERE open_date BETWEEN $1 AND $2
962 GROUP BY user_id,day) aopen
965 (SELECT assigned_to AS user_id, $1::int AS day, count(*) AS tracker_closed
967 WHERE close_date BETWEEN $1 AND $2
968 GROUP BY user_id,day ) aclosed USING (user_id,day)) foo1
971 (SELECT posted_by AS user_id, $1::int AS day, count(*) AS forum
973 WHERE post_date BETWEEN $1 AND $2
974 GROUP BY user_id,day ) forum USING (user_id,day)) foo2
977 (SELECT created_by AS user_id, $1::int AS day, count(*) AS docs
979 WHERE createdate BETWEEN $1 AND $2
980 GROUP BY user_id,day ) docs USING (user_id,day)) foo3
983 (SELECT user_id, $1::int AS day, sum(commits) AS cvs_commits
985 WHERE month=$3 AND day=$2
986 GROUP BY user_id,day ) cvs USING (user_id,day)) foo4
989 (SELECT created_by AS user_id, $1::int AS day, count(*) AS tasks_opened
991 WHERE start_date BETWEEN $1 AND $2
992 GROUP BY user_id,day ) topen USING (user_id,day)) foo5
995 (SELECT mod_by AS user_id, $1::int AS day, count(*) AS tasks_closed
997 WHERE mod_date BETWEEN $1 AND $2
998 AND old_value=$4 AND field_name=$5
999 GROUP BY user_id,day ) tclosed USING (user_id,day)) foo6',
1008 * Populate the user_act_daily report table.
1010 * @return boolean Success.
1012 function backfill_user_act_daily($count=10000) {
1013 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1014 if (!$start_date=$this->getMinDate()) {
1015 $this->setError('backfill_user_act_daily:: Could Not Get Start Date');
1020 $day=$today-($i*REPORT_DAY_SPAN);
1021 if (!$this->user_act_daily($day)) {
1022 $this->setError('backfill_user_act_daily:: Error adding daily row: '.db_error());
1025 if ($day < $start_date) {
1037 * Add a row to the user_act_weekly report table.
1039 * @param int Week - the unix time of the beginning of the sunday for this week.
1040 * @return boolean Success.
1042 function user_act_weekly($week) {
1043 db_query_params ('DELETE FROM rep_user_act_weekly WHERE week=$1',
1046 return db_query_params ('
1047 INSERT INTO rep_user_act_weekly (user_id, week, tracker_opened, tracker_closed,
1048 forum, docs, cvs_commits, tasks_opened, tasks_closed)
1049 SELECT user_id,$1::int AS week, sum(tracker_opened) AS tracker_opened,
1050 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1051 sum(docs) AS docs, sum(cvs_commits) AS cvs_commits,
1052 sum(tasks_opened) AS tasks_opened, sum(tasks_closed) AS tasks_closed
1053 FROM rep_user_act_daily
1054 WHERE DAY BETWEEN $1 AND $2
1055 GROUP BY user_id,week',
1057 $week+REPORT_WEEK_SPAN-1)) ;
1061 * Populate the user_act_weekly report table.
1063 * @return boolean Success.
1065 function backfill_user_act_weekly($count=10000) {
1067 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
1070 for ($i=0; $i<count($arr); $i++) {
1071 if (!$this->user_act_weekly($arr[$i])) {
1072 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1080 * Add a row to the user_act_monthly report table.
1082 * @param int month_start - the unix time of the beginning of the month.
1083 * @param int month_end - the unix time of the end of the month.
1084 * @return boolean Success.
1086 function user_act_monthly($month,$end) {
1087 db_query_params ('DELETE FROM rep_user_act_monthly WHERE month=$1',
1090 return db_query_params ('
1091 INSERT INTO rep_user_act_monthly (user_id, month, tracker_opened,
1092 tracker_closed, forum, docs, cvs_commits, tasks_opened, tasks_closed)
1093 SELECT user_id, $1::int AS month, sum(tracker_opened) AS tracker_opened,
1094 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1095 sum(docs) AS docs, sum(cvs_commits) AS cvs_commits,
1096 sum(tasks_opened) AS tasks_opened, sum(tasks_closed) AS tasks_closed
1097 FROM rep_user_act_daily
1098 WHERE DAY BETWEEN $1 AND $2
1099 GROUP BY user_id, month',
1100 array ($month, $end)) ;
1104 * Populate the user_act_monthly report table.
1106 * @return boolean Success.
1108 function backfill_user_act_monthly($count=10000) {
1110 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
1113 for ($i=1; $i<count($arr); $i++) {
1114 if (!$this->user_act_monthly($arr[$i],($arr[$i-1]-1))) {
1115 $this->setError('backfill_user_act_monthly:: Error adding monthly row: '.db_error());
1122 // ************************
1126 * Add a row to the group_act_daily report table.
1128 * @param int Day - the unix time of the beginning of the day.
1129 * @return boolean Success.
1131 function group_act_daily($day) {
1132 db_query_params ('DELETE FROM rep_group_act_daily WHERE day=$1',
1135 $end_day=$day+REPORT_DAY_SPAN-1;
1137 return db_query_params ('INSERT INTO rep_group_act_daily
1138 SELECT group_id,day,coalesce(tracker_opened,0) AS tracker_opened,
1139 coalesce(tracker_closed,0) AS tracker_closed,
1140 coalesce(forum,0) AS forum,
1141 coalesce(docs,0) AS docs,
1142 coalesce(downloads,0) AS downloads,
1143 coalesce(cvs_commits,0) AS cvs_commits,
1144 coalesce(tasks_opened,0) AS tasks_opened,
1145 coalesce(tasks_closed,0) AS tasks_closed
1154 (SELECT agl.group_id, $1::int AS day, count(*) AS tracker_opened
1155 FROM artifact a, artifact_group_list agl
1156 WHERE a.open_date BETWEEN $1 AND $2
1157 AND a.group_artifact_id=agl.group_artifact_id
1158 GROUP BY group_id,day) aopen
1161 (SELECT agl.group_id, $1::int AS day, count(*) AS tracker_closed
1162 FROM artifact a, artifact_group_list agl
1163 WHERE a.close_date BETWEEN $1 AND $2
1164 AND a.group_artifact_id=agl.group_artifact_id
1165 GROUP BY group_id,day ) aclosed USING (group_id,day)) foo1
1168 (SELECT fgl.group_id, $1::int AS day, count(*) AS forum
1169 FROM forum f, forum_group_list fgl
1170 WHERE f.post_date BETWEEN $1 AND $2
1171 AND f.group_forum_id=fgl.group_forum_id
1172 GROUP BY group_id,day ) forum USING (group_id,day)) foo2
1175 (SELECT group_id, $1::int AS day, count(*) AS docs
1177 WHERE createdate BETWEEN $1 AND $2
1178 GROUP BY group_id,day ) docs USING (group_id,day)) foo3
1181 (SELECT fp.group_id, $1::int AS day, count(*) AS downloads
1182 FROM frs_package fp, frs_release fr, frs_file ff, frs_dlstats_file fdf
1183 WHERE fp.package_id=fr.package_id
1184 AND fr.release_id=ff.release_id
1185 AND ff.file_id=fdf.file_id
1186 AND fdf.month = $3 AND fdf.day = $4
1187 GROUP BY fp.group_id,day ) docs USING (group_id,day)) foo4
1190 (SELECT group_id, $1::int AS day, sum(commits) AS cvs_commits
1191 FROM stats_cvs_group
1192 WHERE month=$3 AND day=$4
1193 GROUP BY group_id,day ) cvs USING (group_id,day)) foo5
1196 (SELECT pgl.group_id, $1::int AS day,count(*) AS tasks_opened
1197 FROM project_task pt, project_group_list pgl
1198 WHERE pt.start_date BETWEEN $1 AND $2
1199 AND pt.group_project_id=pgl.group_project_id
1200 GROUP BY group_id,day ) topen USING (group_id,day)) foo6
1203 (SELECT pgl.group_id, $1::int AS day, count(*) AS tasks_closed
1204 FROM project_history ph, project_task pt, project_group_list pgl
1205 WHERE ph.mod_date BETWEEN $1 AND $2
1207 AND ph.field_name=$6
1208 AND ph.project_task_id=pt.project_task_id
1209 AND pt.group_project_id=pgl.group_project_id
1210 GROUP BY group_id,day ) tclosed USING (group_id,day)) foo7',
1222 * Populate the group_act_daily report table.
1224 * @return boolean Success.
1226 function backfill_group_act_daily($count=10000) {
1227 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1228 if (!$start_date=$this->getMinDate()) {
1229 $this->setError('backfill_group_act_daily:: Could Not Get Start Date');
1234 $day=$today-($i*REPORT_DAY_SPAN);
1235 if (!$this->group_act_daily($day)) {
1236 $this->setError('backfill_group_act_daily:: Error adding daily row: '.db_error());
1239 if ($day < $start_date) {
1251 * Add a row to the group_act_weekly report table.
1253 * @param int Week - the unix time of the beginning of the sunday for this week.
1254 * @return boolean Success.
1256 function group_act_weekly($week) {
1257 db_query_params ('DELETE FROM rep_group_act_weekly WHERE week=$1',
1260 return db_query_params ('
1261 INSERT INTO rep_group_act_weekly (group_id, week, tracker_opened,
1262 tracker_closed, forum, docs, downloads, cvs_commits, tasks_opened,
1264 SELECT group_id, $1::int AS week, sum(tracker_opened) AS tracker_opened,
1265 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1266 sum(docs) AS docs, sum(downloads) AS downloads,
1267 sum(cvs_commits) AS cvs_commits, sum(tasks_opened) AS tasks_opened,
1268 sum(tasks_closed) AS tasks_closed
1269 FROM rep_group_act_daily
1270 WHERE DAY BETWEEN $1 AND $2
1271 GROUP BY group_id, week',
1273 $week+REPORT_WEEK_SPAN-1)) ;
1277 * Populate the group_act_weekly report table.
1279 * @return boolean Success.
1281 function backfill_group_act_weekly($count=10000) {
1283 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
1286 for ($i=0; $i<count($arr); $i++) {
1287 if (!$this->group_act_weekly($arr[$i])) {
1288 $this->setError('backfill_user_act_weekly:: Error adding weekly row: '.db_error());
1296 * Add a row to the group_act_monthly report table.
1298 * @param int month_start - the unix time of the beginning of the month.
1299 * @param int month_end - the unix time of the end of the month.
1300 * @return boolean Success.
1302 function group_act_monthly($month,$end) {
1303 db_query_params ('DELETE FROM rep_group_act_monthly WHERE month=$1',
1306 return db_query_params ('
1307 INSERT INTO rep_group_act_monthly (group_id, month, tracker_opened,
1308 tracker_closed, forum, docs, downloads, cvs_commits, tasks_opened,
1310 SELECT group_id, $1::int AS month, sum(tracker_opened) AS tracker_opened,
1311 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1312 sum(docs) AS docs, sum(downloads) AS downloads,
1313 sum(cvs_commits) AS cvs_commits,
1314 sum(tasks_opened) AS tasks_opened,
1315 sum(tasks_closed) AS tasks_closed
1316 FROM rep_group_act_daily
1317 WHERE DAY BETWEEN $1 AND $2
1318 GROUP BY group_id,month',
1319 array ($month, $end)) ;
1323 * Populate the group_act_monthly report table.
1325 * @return boolean Success.
1327 function backfill_group_act_monthly($count=10000) {
1329 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
1332 for ($i=1; $i<count($arr); $i++) {
1333 if (!$this->group_act_monthly($arr[$i],($arr[$i-1]-1))) {
1334 $this->setError('backfill_group_act_monthly:: Error adding monthly row: '.db_error());
1342 * Add a row to the rep_time_category table.
1344 * @param string The category name.
1345 * @return boolean Success.
1347 function addTimeCode($category_name) {
1348 return db_query_params ('INSERT INTO rep_time_category (category_name) VALUES ($1)',
1349 array($category_name)) ;
1353 * Update the rep_time_category table.
1355 * @param string The category name.
1356 * @return boolean Success.
1358 function updateTimeCode($time_code, $category_name) {
1359 return db_query_params ('UPDATE rep_time_category SET category_name=$1 WHERE time_code=$2',
1360 array($category_name,
1368 // c-file-style: "bsd"