3 * FusionForge reporting system
5 * Copyright 2003-2004, Tim Perdue/GForge, LLC
6 * Copyright 2015, nitendra tripathi
8 * This file is part of FusionForge. FusionForge is free software;
9 * you can redistribute it and/or modify it under the terms of the
10 * GNU General Public License as published by the Free Software
11 * Foundation; either version 2 of the Licence, or (at your option)
14 * FusionForge is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
19 * You should have received a copy of the GNU General Public License along
20 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
21 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
24 require_once $gfcommon.'reporting/Report.class.php';
26 class ReportSetup extends Report {
28 function __construct() {
29 parent::__construct();
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,
49 //$sql[]="DROP TABLE rep_time_tracking;";
50 $sql1="CREATE TABLE rep_time_tracking (
52 report_date int not null,
54 project_task_id int not null,
55 time_code int not null CONSTRAINT reptimetrk_timecode REFERENCES rep_time_category(time_code),
56 hours float not null);";
58 // $sql[]="CREATE UNIQUE INDEX reptimetrk_weekusrtskcde ON
59 // rep_time_tracking (week,user_id,project_task_id,time_code);";
60 $sql[]="CREATE INDEX reptimetracking_userdate ON
61 rep_time_tracking (user_id,week);";
63 $sql[]="INSERT INTO rep_time_category VALUES ('1','Coding');";
64 $sql[]="INSERT INTO rep_time_category VALUES ('2','Testing');";
65 $sql[]="INSERT INTO rep_time_category VALUES ('3','Meeting');";
66 $sql[]="SELECT setval('rep_time_category_time_code_seq',(SELECT max(time_code) FROM rep_time_category));";
69 $sql[]="DROP TABLE rep_users_added_daily;";
70 $sql[]="CREATE TABLE rep_users_added_daily (
71 day int not null primary key,
72 added int not null default 0);";
74 $sql[]="DROP TABLE rep_users_added_weekly";
75 $sql[]="CREATE TABLE rep_users_added_weekly (
76 week int not null primary key,
77 added int not null default 0);";
79 $sql[]="DROP TABLE rep_users_added_monthly";
80 $sql[]="CREATE TABLE rep_users_added_monthly (
81 month int not null primary key,
82 added int not null default 0);";
85 $sql[]="DROP TABLE rep_users_cum_daily";
86 $sql[]="CREATE TABLE rep_users_cum_daily (
87 day int not null primary key,
88 total int not null default 0);";
90 $sql[]="DROP TABLE rep_users_cum_weekly";
91 $sql[]="CREATE TABLE rep_users_cum_weekly (
92 week int not null primary key,
93 total int not null default 0);";
95 $sql[]="DROP TABLE rep_users_cum_monthly";
96 $sql[]="CREATE TABLE rep_users_cum_monthly (
97 month int not null primary key,
98 total int not null default 0);";
101 $sql[]="DROP TABLE rep_groups_added_daily;";
102 $sql[]="CREATE TABLE rep_groups_added_daily (
103 day int not null primary key,
104 added int not null default 0);";
106 $sql[]="DROP TABLE rep_groups_added_weekly";
107 $sql[]="CREATE TABLE rep_groups_added_weekly (
108 week int not null primary key,
109 added int not null default 0);";
111 $sql[]="DROP TABLE rep_groups_added_monthly";
112 $sql[]="CREATE TABLE rep_groups_added_monthly (
113 month int not null primary key,
114 added int not null default 0);";
117 $sql[]="DROP TABLE rep_groups_cum_daily";
118 $sql[]="CREATE TABLE rep_groups_cum_daily (
119 day int not null primary key,
120 total int not null default 0);";
122 $sql[]="DROP TABLE rep_groups_cum_weekly";
123 $sql[]="CREATE TABLE rep_groups_cum_weekly (
124 week int not null primary key,
125 total int not null default 0);";
127 $sql[]="DROP TABLE rep_groups_cum_monthly";
128 $sql[]="CREATE TABLE rep_groups_cum_monthly (
129 month int not null primary key,
130 total int not null default 0);";
133 $sql[]="DROP TABLE rep_user_act_daily";
134 $sql[]="CREATE TABLE rep_user_act_daily (
135 user_id int not null,
137 tracker_opened int not null,
138 tracker_closed int not null,
141 cvs_commits int not null,
142 tasks_opened int not null,
143 tasks_closed int not null,
144 PRIMARY KEY (user_id,day));";
146 $sql[]="DROP TABLE rep_user_act_weekly";
147 $sql[]="CREATE TABLE rep_user_act_weekly (
148 user_id int not null,
150 tracker_opened int not null,
151 tracker_closed int not null,
154 cvs_commits int not null,
155 tasks_opened int not null,
156 tasks_closed int not null,
157 PRIMARY KEY (user_id,week));";
159 $sql[]="DROP TABLE rep_user_act_monthly";
160 $sql[]="CREATE TABLE rep_user_act_monthly (
161 user_id int not null,
163 tracker_opened int not null,
164 tracker_closed int not null,
167 cvs_commits int not null,
168 tasks_opened int not null,
169 tasks_closed int not null,
170 PRIMARY KEY (user_id,month));";
172 $sql[]="DROP VIEW rep_user_act_oa_vw";
173 $sql[]="CREATE VIEW rep_user_act_oa_vw AS
175 sum(tracker_opened) AS tracker_opened,
176 sum(tracker_closed) AS tracker_closed,
179 sum(cvs_commits) AS cvs_commits,
180 sum(tasks_opened) AS tasks_opened,
181 sum(tasks_closed) AS tasks_closed
182 FROM rep_user_act_monthly
185 //per-project activity
186 $sql[]="DROP TABLE rep_group_act_daily";
187 $sql[]="CREATE TABLE rep_group_act_daily (
188 group_id int not null,
190 tracker_opened int not null,
191 tracker_closed int not null,
194 downloads int not null,
195 cvs_commits int not null,
196 tasks_opened int not null,
197 tasks_closed int not null,
198 PRIMARY KEY (group_id,day));";
200 $sql[]="DROP INDEX repgroupactdaily_day";
201 $sql[]="CREATE INDEX repgroupactdaily_day ON rep_group_act_daily(day)";
203 $sql[]="DROP TABLE rep_group_act_weekly";
204 $sql[]="CREATE TABLE rep_group_act_weekly (
205 group_id int not null,
207 tracker_opened int not null,
208 tracker_closed int not null,
211 downloads int not null,
212 cvs_commits int not null,
213 tasks_opened int not null,
214 tasks_closed int not null,
215 PRIMARY KEY (group_id,week));";
217 $sql[]="DROP INDEX repgroupactweekly_week";
218 $sql[]="CREATE INDEX repgroupactweekly_week ON rep_group_act_weekly(week)";
220 $sql[]="DROP TABLE rep_group_act_monthly";
221 $sql[]="CREATE TABLE rep_group_act_monthly (
222 group_id int not null,
224 tracker_opened int not null,
225 tracker_closed int not null,
228 downloads int not null,
229 cvs_commits int not null,
230 tasks_opened int not null,
231 tasks_closed int not null,
232 PRIMARY KEY (group_id,month));";
234 $sql[]="DROP INDEX repgroupactmonthly_month";
235 $sql[]="CREATE INDEX repgroupactmonthly_month ON rep_group_act_monthly(month)";
237 $sql[]="DROP VIEW rep_group_act_oa_vw";
238 $sql[]="CREATE VIEW rep_group_act_oa_vw AS
240 sum(tracker_opened) AS tracker_opened,
241 sum(tracker_closed) AS tracker_closed,
244 sum(downloads) AS downloads,
245 sum(cvs_commits) AS cvs_commits,
246 sum(tasks_opened) AS tasks_opened,
247 sum(tasks_closed) AS tasks_closed
248 FROM rep_group_act_monthly
252 $sql[]="DROP VIEW rep_site_act_daily_vw";
253 $sql[]="CREATE VIEW rep_site_act_daily_vw AS
255 sum(tracker_opened) AS tracker_opened,
256 sum(tracker_closed) AS tracker_closed,
259 sum(downloads) AS downloads,
260 sum(cvs_commits) AS cvs_commits,
261 sum(tasks_opened) AS tasks_opened,
262 sum(tasks_closed) AS tasks_closed
263 FROM rep_group_act_daily
266 $sql[]="DROP VIEW rep_site_act_weekly_vw";
267 $sql[]="CREATE VIEW rep_site_act_weekly_vw AS
269 sum(tracker_opened) AS tracker_opened,
270 sum(tracker_closed) AS tracker_closed,
273 sum(downloads) AS downloads,
274 sum(cvs_commits) AS cvs_commits,
275 sum(tasks_opened) AS tasks_opened,
276 sum(tasks_closed) AS tasks_closed
277 FROM rep_group_act_weekly
280 $sql[]="DROP VIEW rep_site_act_monthly_vw";
281 $sql[]="CREATE VIEW rep_site_act_monthly_vw AS
283 sum(tracker_opened) AS tracker_opened,
284 sum(tracker_closed) AS tracker_closed,
287 sum(downloads) AS downloads,
288 sum(cvs_commits) AS cvs_commits,
289 sum(tasks_opened) AS tasks_opened,
290 sum(tasks_closed) AS tasks_closed
291 FROM rep_group_act_monthly
294 $sql[]="DROP VIEW rep_site_act_oa_vw";
295 $sql[]="CREATE VIEW rep_site_act_oa_vw AS
297 sum(tracker_opened) AS tracker_opened,
298 sum(tracker_closed) AS tracker_closed,
301 sum(downloads) AS downloads,
302 sum(cvs_commits) AS cvs_commits,
303 sum(tasks_opened) AS tasks_opened,
304 sum(tasks_closed) AS tasks_closed
305 FROM rep_group_act_monthly;";
307 for ($i=0; $i<count($sql); $i++) {
308 db_query_params($sql[$i], array());
313 function initialData() {
314 if (!$this->backfill_users_added_daily()) {
317 if (!$this->backfill_users_added_weekly()) {
320 if (!$this->backfill_users_added_monthly()) {
323 if (!$this->backfill_users_cum_daily()) {
326 if (!$this->backfill_users_cum_weekly()) {
329 if (!$this->backfill_users_cum_monthly()) {
332 if (!$this->backfill_groups_added_daily()) {
335 if (!$this->backfill_groups_added_weekly()) {
338 if (!$this->backfill_groups_added_monthly()) {
341 if (!$this->backfill_groups_cum_daily()) {
344 if (!$this->backfill_groups_cum_weekly()) {
347 if (!$this->backfill_groups_cum_monthly()) {
350 if (!$this->backfill_user_act_daily()) {
353 if (!$this->backfill_user_act_weekly()) {
356 if (!$this->backfill_user_act_monthly()) {
359 if (!$this->backfill_group_act_daily()) {
362 if (!$this->backfill_group_act_weekly()) {
365 if (!$this->backfill_group_act_monthly()) {
372 function dailyData() {
373 if (!$this->backfill_users_added_daily(1)) {
376 if (!$this->backfill_users_added_weekly(1)) {
379 if (!$this->backfill_users_added_monthly(2)) {
382 if (!$this->backfill_users_cum_daily(1)) {
385 if (!$this->backfill_users_cum_weekly(1)) {
388 if (!$this->backfill_users_cum_monthly(2)) {
391 if (!$this->backfill_user_act_daily(1)) {
394 if (!$this->backfill_user_act_weekly(1)) {
397 if (!$this->backfill_user_act_monthly(2)) {
400 if (!$this->backfill_group_act_daily(1)) {
403 if (!$this->backfill_group_act_weekly(1)) {
406 if (!$this->backfill_group_act_monthly(2)) {
412 * Add a row to the users_added_daily report table.
414 * @param int $day Day - the unix time of the beginning of the day.
415 * @return bool Success.
417 function users_added_daily($day) {
418 db_query_params ('DELETE FROM rep_users_added_daily WHERE day=$1',
421 return db_query_params ('INSERT INTO rep_users_added_daily (day,added)
422 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
423 BETWEEN $3 AND $4 ))',
427 ($day + REPORT_DAY_SPAN - 1) ));
431 * Populate the users_added_daily report table.
434 * @return bool Success.
436 function backfill_users_added_daily($count=10000) {
437 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
438 if (!$start_date=$this->getMinDate()) {
439 $this->setError('backfill_users_added_daily: Could Not Get Start Date');
444 $day=($today-($i*REPORT_DAY_SPAN));
445 if (!$this->users_added_daily($day)) {
446 $this->setError('backfill_users_added_daily: Error adding daily row: '.db_error());
449 if ($day < $start_date) {
461 * Add a row to the groups_added_daily report table.
463 * @param int $day Day - the unix time of the beginning of the day.
464 * @return bool Success.
466 function groups_added_daily($day) {
467 db_query_params ('DELETE FROM rep_groups_added_daily WHERE day=$1',
470 return db_query_params ('INSERT INTO rep_groups_added_daily (day,added)
471 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
472 BETWEEN $3 AND $4 ))',
476 ($day + REPORT_DAY_SPAN - 1) ));
480 * Populate the groups_added_daily report table.
483 * @return bool Success.
485 function backfill_groups_added_daily($count=10000) {
486 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
487 if (!$start_date=$this->getMinDate()) {
488 $this->setError('backfill_groups_added_daily: Could Not Get Start Date');
493 $day=($today-($i*REPORT_DAY_SPAN));
494 if (!$this->groups_added_daily($day)) {
495 $this->setError('backfill_groups_added_daily: Error adding daily row: '.db_error());
498 if ($day < $start_date) {
510 * Add a row to the users_added_weekly report table.
512 * @param int $week Week - the unix time of the beginning of the sunday for this week.
513 * @return bool Success.
515 function users_added_weekly($week) {
516 db_query_params ('DELETE FROM rep_users_added_weekly WHERE week=$1',
519 return db_query_params ('INSERT INTO rep_users_added_weekly (week,added)
520 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
521 BETWEEN $3 AND $4 ))',
525 ($week+REPORT_WEEK_SPAN-1) ));
529 * Populate the users_added_weekly report table.
532 * @return bool Success.
534 function backfill_users_added_weekly($count=10000) {
536 $arr = array_slice ($this->getWeekStartArr(), -$count-1);
539 for ($i=0; $i<count($arr); $i++) {
540 if (!$this->users_added_weekly($arr[$i])) {
541 $this->setError('backfill_users_added_weekly: Error adding weekly row: '.db_error());
549 * Add a row to the groups_added_weekly report table.
551 * @param int $week Week - the unix time of the beginning of the sunday for this week.
552 * @return bool Success.
554 function groups_added_weekly($week) {
555 db_query_params ('DELETE FROM rep_groups_added_weekly WHERE week=$1',
558 return db_query_params ('INSERT INTO rep_groups_added_weekly (week,added)
559 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
560 BETWEEN $3 AND $4 ))',
564 ($week+REPORT_WEEK_SPAN-1) ));
568 * Populate the users_added_weekly report table.
571 * @return bool Success.
573 function backfill_groups_added_weekly($count=10000) {
575 $arr = array_slice ($this->getWeekStartArr(), -$count-1);
578 for ($i=0; $i<count($arr); $i++) {
579 if (!$this->groups_added_weekly($arr[$i])) {
580 $this->setError('backfill_groups_added_weekly: Error adding weekly row: '.db_error());
588 * Add a row to the users_added_monthly report table.
590 * @param int $month month_start - the unix time of the beginning of the month.
591 * @param int $end month_end - the unix time of the end of the month.
592 * @return bool Success.
594 function users_added_monthly($month,$end) {
595 db_query_params ('DELETE FROM rep_users_added_monthly WHERE month=$1',
598 return db_query_params ('INSERT INTO rep_users_added_monthly (month,added)
599 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
600 BETWEEN $3 AND $4 ))',
608 * Populate the users_added_monthly report table.
611 * @return bool Success.
613 function backfill_users_added_monthly($count=10000) {
615 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
619 for ($i=1; $i<count($arr); $i++) {
620 if (!$this->users_added_monthly($arr[$i],($arr[$i-1]-1))) {
621 $this->setError('backfill_users_added_monthly: Error adding monthly row: '.db_error());
629 * Add a row to the groups_added_monthly report table.
631 * @param int $month month_start - the unix time of the beginning of the month.
632 * @param int $end month_end - the unix time of the end of the month.
633 * @return bool Success.
635 function groups_added_monthly($month,$end) {
636 db_query_params ('DELETE FROM rep_groups_added_monthly WHERE month=$1',
639 return db_query_params ('INSERT INTO rep_groups_added_monthly (month,added)
640 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
641 BETWEEN $3 AND $4 ))',
649 * Populate the groups_added_monthly report table.
652 * @return bool Success.
654 function backfill_groups_added_monthly($count=10000) {
656 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
660 for ($i=1; $i<count($arr); $i++) {
661 if (!$this->groups_added_monthly($arr[$i],($arr[$i-1]-1))) {
662 $this->setError('backfill_groups_added_monthly: Error adding monthly row: '.db_error());
669 // ******************************
672 * Add a row to the users_cum_daily report table.
674 * @param int $day Day - the unix time of the beginning of the day.
675 * @return bool Success.
677 function users_cum_daily($day) {
678 db_query_params ('DELETE FROM rep_users_cum_daily WHERE day=$1',
681 return db_query_params ('INSERT INTO rep_users_cum_daily (day,total)
682 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
690 * Populate the users_cum_daily report table.
693 * @return bool Success.
695 function backfill_users_cum_daily($count=10000) {
696 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
697 if (!$start_date=$this->getMinDate()) {
698 $this->setError('backfill_users_cum_daily: Could Not Get Start Date');
703 $day=$today-($i*REPORT_DAY_SPAN);
704 if (!$this->users_cum_daily($day)) {
705 $this->setError('backfill_users_cum_daily: Error adding daily row: '.db_error());
708 if ($day < $start_date) {
720 * Add a row to the groups_cum_daily report table.
722 * @param int $day Day - the unix time of the beginning of the day.
723 * @return bool Success.
725 function groups_cum_daily($day) {
726 db_query_params ('DELETE FROM rep_groups_cum_daily WHERE day=$1',
729 return db_query_params ('INSERT INTO rep_groups_cum_daily (day,total)
730 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
738 * Populate the groups_cum_daily report table.
741 * @return bool Success.
743 function backfill_groups_cum_daily($count=10000) {
744 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
745 if (!$start_date=$this->getMinDate()) {
746 $this->setError('backfill_groups_cum_daily: Could Not Get Start Date');
751 $day=$today-($i*REPORT_DAY_SPAN);
752 if (!$this->groups_cum_daily($day)) {
753 $this->setError('backfill_groups_cum_daily: Error adding daily row: '.db_error());
756 if ($day < $start_date) {
768 * Add a row to the users_cum_weekly report table.
770 * @param int $week Week - the unix time of the beginning of the sunday for this week.
771 * @return bool Success.
773 function users_cum_weekly($week) {
774 db_query_params ('DELETE FROM rep_users_cum_weekly WHERE week=$1',
777 return db_query_params ('INSERT INTO rep_users_cum_weekly (week,total)
778 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
779 BETWEEN $3 AND $4))',
783 ($week+REPORT_WEEK_SPAN-1 )));
787 * Populate the users_cum_weekly report table.
790 * @return bool Success.
792 function backfill_users_cum_weekly($count=10000) {
794 $arr = array_slice ($this->getWeekStartArr(), -$count-1);
797 for ($i=0; $i<count($arr); $i++) {
798 if (!$this->groups_cum_weekly($arr[$i])) {
799 $this->setError('backfill_users_cum_weekly: Error adding weekly row: '.db_error());
807 * Add a row to the groups_cum_weekly report table.
809 * @param int $week Week - the unix time of the beginning of the sunday for this week.
810 * @return bool Success.
812 function groups_cum_weekly($week) {
813 db_query_params ('DELETE FROM rep_groups_cum_weekly WHERE week=$1',
816 return db_query_params ('INSERT INTO rep_groups_cum_weekly (week,total)
817 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
818 BETWEEN $3 AND $4))',
822 ($week+REPORT_WEEK_SPAN-1 )));
826 * Populate the groups_cum_weekly report table.
829 * @return bool Success.
831 function backfill_groups_cum_weekly($count=10000) {
833 $arr = array_slice ($this->getWeekStartArr(), -$count-1);
836 for ($i=0; $i<count($arr); $i++) {
837 if (!$this->users_cum_weekly($arr[$i])) {
838 $this->setError('backfill_groups_cum_weekly: Error adding weekly row: '.db_error());
846 * Add a row to the users_cum_monthly report table.
848 * @param int $month month_start - the unix time of the beginning of the month.
849 * @param int $end month_end - the unix time of the end of the month.
850 * @return bool Success.
852 function users_cum_monthly($month,$end) {
853 db_query_params ('DELETE FROM rep_users_cum_monthly WHERE month=$1',
856 return db_query_params ('INSERT INTO rep_users_cum_monthly (month,total)
857 VALUES ($1,(SELECT count(*) FROM users WHERE status=$2 AND add_date
865 * Populate the users_cum_monthly report table.
868 * @return bool Success.
870 function backfill_users_cum_monthly($count=10000) {
872 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
876 for ($i=1; $i<count($arr); $i++) {
877 if (!$this->users_cum_monthly($arr[$i],($arr[$i-1]-1))) {
878 $this->setError('backfill_users_cum_monthly: Error adding monthly row: '.db_error());
886 * Add a row to the groups_cum_monthly report table.
888 * @param int $month month_start - the unix time of the beginning of the month.
889 * @param int $end month_end - the unix time of the end of the month.
890 * @return bool Success.
892 function groups_cum_monthly($month,$end) {
893 db_query_params ('DELETE FROM rep_groups_cum_monthly WHERE month=$1',
896 return db_query_params ('INSERT INTO rep_groups_cum_monthly (month,total)
897 VALUES ($1,(SELECT count(*) FROM groups WHERE status=$2 AND register_time
905 * Populate the groups_cum_monthly report table.
908 * @return bool Success.
910 function backfill_groups_cum_monthly($count=10000) {
912 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
916 for ($i=1; $i<count($arr); $i++) {
917 if (!$this->groups_cum_monthly($arr[$i],($arr[$i-1]-1))) {
918 $this->setError('backfill_groups_cum_monthly: Error adding monthly row: '.db_error());
925 // ************************
928 * Add a row to the user_act_daily report table.
930 * @param int $day Day - the unix time of the beginning of the day.
931 * @return bool Success.
933 function user_act_daily($day) {
934 db_query_params('DELETE FROM rep_user_act_daily WHERE day=$1',
937 $end_day=$day+REPORT_DAY_SPAN-1;
939 return db_query_params ('INSERT INTO rep_user_act_daily
940 SELECT user_id,day,coalesce(tracker_opened,0) AS tracker_opened,
941 coalesce(tracker_closed,0) AS tracker_closed,
942 coalesce(forum,0) AS forum,
943 coalesce(docs,0) AS docs,
944 coalesce(cvs_commits,0) AS cvs_commits,
945 coalesce(tasks_opened,0) AS tasks_opened,
946 coalesce(tasks_closed,0) AS tasks_closed
954 (SELECT submitted_by AS user_id, $1::int AS day, count(*) AS tracker_opened
956 WHERE open_date BETWEEN $1 AND $2
957 GROUP BY user_id,day) aopen
960 (SELECT assigned_to AS user_id, $1::int AS day, count(*) AS tracker_closed
962 WHERE close_date BETWEEN $1 AND $2
963 GROUP BY user_id,day ) aclosed USING (user_id,day)) foo1
966 (SELECT posted_by AS user_id, $1::int AS day, count(*) AS forum
968 WHERE post_date BETWEEN $1 AND $2
969 GROUP BY user_id,day ) forum USING (user_id,day)) foo2
972 (SELECT created_by AS user_id, $1::int AS day, count(*) AS docs
974 WHERE createdate BETWEEN $1 AND $2
975 GROUP BY user_id,day ) docs USING (user_id,day)) foo3
978 (SELECT user_id, $1::int AS day, sum(commits) AS cvs_commits
980 WHERE month=$3 AND day=$2
981 GROUP BY user_id,day ) cvs USING (user_id,day)) foo4
984 (SELECT created_by AS user_id, $1::int AS day, count(*) AS tasks_opened
986 WHERE start_date BETWEEN $1 AND $2
987 GROUP BY user_id,day ) topen USING (user_id,day)) foo5
990 (SELECT mod_by AS user_id, $1::int AS day, count(*) AS tasks_closed
992 WHERE mod_date BETWEEN $1 AND $2
993 AND old_value=$4 AND field_name=$5
994 GROUP BY user_id,day ) tclosed USING (user_id,day)) foo6',
1003 * Populate the user_act_daily report table.
1006 * @return bool Success.
1008 function backfill_user_act_daily($count=10000) {
1009 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1010 if (!$start_date=$this->getMinDate()) {
1011 $this->setError('backfill_user_act_daily: Could Not Get Start Date');
1016 $day=$today-($i*REPORT_DAY_SPAN);
1017 if (!$this->user_act_daily($day)) {
1018 $this->setError('backfill_user_act_daily: Error adding daily row: '.db_error());
1021 if ($day < $start_date) {
1033 * Add a row to the user_act_weekly report table.
1035 * @param int $week Week - the unix time of the beginning of the sunday for this week.
1036 * @return bool Success.
1038 function user_act_weekly($week) {
1039 db_query_params('DELETE FROM rep_user_act_weekly WHERE week=$1',
1042 return db_query_params ('
1043 INSERT INTO rep_user_act_weekly (user_id, week, tracker_opened, tracker_closed,
1044 forum, docs, cvs_commits, tasks_opened, tasks_closed)
1045 SELECT user_id,$1::int AS week,
1046 sum(tracker_opened) AS tracker_opened,
1047 sum(tracker_closed) AS tracker_closed,
1048 sum(forum) AS forum,
1050 sum(cvs_commits) AS cvs_commits,
1051 sum(tasks_opened) AS tasks_opened,
1052 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.
1064 * @return bool Success.
1066 function backfill_user_act_weekly($count=10000) {
1068 $arr = array_slice ($this->getWeekStartArr(), -$count-1);
1071 for ($i=0; $i<count($arr); $i++) {
1072 if (!$this->user_act_weekly($arr[$i])) {
1073 $this->setError('backfill_user_act_weekly: Error adding weekly row: '.db_error());
1081 * Add a row to the user_act_monthly report table.
1083 * @param int $month month_start - the unix time of the beginning of the month.
1084 * @param int $end month_end - the unix time of the end of the month.
1085 * @return bool Success.
1087 function user_act_monthly($month,$end) {
1088 db_query_params('DELETE FROM rep_user_act_monthly WHERE month=$1',
1091 return db_query_params ('
1092 INSERT INTO rep_user_act_monthly (user_id, month, tracker_opened,
1093 tracker_closed, forum, docs, cvs_commits, tasks_opened, tasks_closed)
1094 SELECT user_id, $1::int AS month, sum(tracker_opened) AS tracker_opened,
1095 sum(tracker_closed) AS tracker_closed, sum(forum) AS forum,
1096 sum(docs) AS docs, sum(cvs_commits) AS cvs_commits,
1097 sum(tasks_opened) AS tasks_opened, sum(tasks_closed) AS tasks_closed
1098 FROM rep_user_act_daily
1099 WHERE DAY BETWEEN $1 AND $2
1100 GROUP BY user_id, month',
1101 array ($month, $end));
1105 * Populate the user_act_monthly report table.
1108 * @return bool Success.
1110 function backfill_user_act_monthly($count=10000) {
1112 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
1115 for ($i=1; $i<count($arr); $i++) {
1116 if (!$this->user_act_monthly($arr[$i],($arr[$i-1]-1))) {
1117 $this->setError('backfill_user_act_monthly: Error adding monthly row: '.db_error());
1124 // ************************
1127 * Add a row to the group_act_daily report table.
1129 * @param int $day Day - the unix time of the beginning of the day.
1130 * @return bool Success.
1132 function group_act_daily($day) {
1133 db_query_params('DELETE FROM rep_group_act_daily WHERE day=$1',
1136 $end_day=$day+REPORT_DAY_SPAN-1;
1138 return db_query_params ('INSERT INTO rep_group_act_daily
1139 SELECT group_id,day,coalesce(tracker_opened,0) AS tracker_opened,
1140 coalesce(tracker_closed,0) AS tracker_closed,
1141 coalesce(forum,0) AS forum,
1142 coalesce(docs,0) AS docs,
1143 coalesce(downloads,0) AS downloads,
1144 coalesce(cvs_commits,0) AS cvs_commits,
1145 coalesce(tasks_opened,0) AS tasks_opened,
1146 coalesce(tasks_closed,0) AS tasks_closed
1155 (SELECT agl.group_id, $1::int AS day, count(*) AS tracker_opened
1156 FROM artifact a, artifact_group_list agl
1157 WHERE a.open_date BETWEEN $1 AND $2
1158 AND a.group_artifact_id=agl.group_artifact_id
1159 GROUP BY group_id,day) aopen
1162 (SELECT agl.group_id, $1::int AS day, count(*) AS tracker_closed
1163 FROM artifact a, artifact_group_list agl
1164 WHERE a.close_date BETWEEN $1 AND $2
1165 AND a.group_artifact_id=agl.group_artifact_id
1166 GROUP BY group_id,day ) aclosed USING (group_id,day)) foo1
1169 (SELECT fgl.group_id, $1::int AS day, count(*) AS forum
1170 FROM forum f, forum_group_list fgl
1171 WHERE f.post_date BETWEEN $1 AND $2
1172 AND f.group_forum_id=fgl.group_forum_id
1173 GROUP BY group_id,day ) forum USING (group_id,day)) foo2
1176 (SELECT group_id, $1::int AS day, count(*) AS docs
1178 WHERE createdate BETWEEN $1 AND $2
1179 GROUP BY group_id,day ) docs USING (group_id,day)) foo3
1182 (SELECT fp.group_id, $1::int AS day, count(*) AS downloads
1183 FROM frs_package fp, frs_release fr, frs_file ff, frs_dlstats_file fdf
1184 WHERE fp.package_id=fr.package_id
1185 AND fr.release_id=ff.release_id
1186 AND ff.file_id=fdf.file_id
1187 AND fdf.month = $3 AND fdf.day = $4
1188 GROUP BY fp.group_id,day ) docs USING (group_id,day)) foo4
1191 (SELECT group_id, $1::int AS day, sum(commits) AS cvs_commits
1192 FROM stats_cvs_group
1193 WHERE month=$3 AND day=$4
1194 GROUP BY group_id,day ) cvs USING (group_id,day)) foo5
1197 (SELECT pgl.group_id, $1::int AS day,count(*) AS tasks_opened
1198 FROM project_task pt, project_group_list pgl
1199 WHERE pt.start_date BETWEEN $1 AND $2
1200 AND pt.group_project_id=pgl.group_project_id
1201 GROUP BY group_id,day ) topen USING (group_id,day)) foo6
1204 (SELECT pgl.group_id, $1::int AS day, count(*) AS tasks_closed
1205 FROM project_history ph, project_task pt, project_group_list pgl
1206 WHERE ph.mod_date BETWEEN $1 AND $2
1208 AND ph.field_name=$6
1209 AND ph.project_task_id=pt.project_task_id
1210 AND pt.group_project_id=pgl.group_project_id
1211 GROUP BY group_id,day ) tclosed USING (group_id,day)) foo7',
1222 * Populate the group_act_daily report table.
1225 * @return bool Success.
1227 function backfill_group_act_daily($count=10000) {
1228 $today=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
1229 if (!$start_date=$this->getMinDate()) {
1230 $this->setError('backfill_group_act_daily: Could Not Get Start Date');
1235 $day=$today-($i*REPORT_DAY_SPAN);
1236 if (!$this->group_act_daily($day)) {
1237 $this->setError('backfill_group_act_daily: Error adding daily row: '.db_error());
1240 if ($day < $start_date) {
1252 * Add a row to the group_act_weekly report table.
1254 * @param int $week Week - the unix time of the beginning of the sunday for this week.
1255 * @return bool Success.
1257 function group_act_weekly($week) {
1258 db_query_params ('DELETE FROM rep_group_act_weekly WHERE week=$1',
1261 return db_query_params ('
1262 INSERT INTO rep_group_act_weekly (group_id, week, tracker_opened,
1263 tracker_closed, forum, docs, downloads, cvs_commits, tasks_opened,
1265 SELECT group_id, $1::int AS week, sum(tracker_opened) AS tracker_opened,
1266 sum(tracker_closed) AS tracker_closed,
1267 sum(forum) AS forum,
1269 sum(downloads) AS downloads,
1270 sum(cvs_commits) AS cvs_commits,
1271 sum(tasks_opened) AS tasks_opened,
1272 sum(tasks_closed) AS tasks_closed
1273 FROM rep_group_act_daily
1274 WHERE DAY BETWEEN $1 AND $2
1275 GROUP BY group_id, week',
1277 $week+REPORT_WEEK_SPAN-1));
1281 * Populate the group_act_weekly report table.
1284 * @return bool Success.
1286 function backfill_group_act_weekly($count=10000) {
1288 $arr = array_slice ($this->getWeekStartArr(), -$count-1);
1291 for ($i=0; $i<count($arr); $i++) {
1292 if (!$this->group_act_weekly($arr[$i])) {
1293 $this->setError('backfill_user_act_weekly: Error adding weekly row: '.db_error());
1301 * Add a row to the group_act_monthly report table.
1303 * @param int $month month_start - the unix time of the beginning of the month.
1304 * @param int $end month_end - the unix time of the end of the month.
1305 * @return bool Success.
1307 function group_act_monthly($month,$end) {
1308 db_query_params('DELETE FROM rep_group_act_monthly WHERE month=$1',
1311 return db_query_params('
1312 INSERT INTO rep_group_act_monthly (group_id, month, tracker_opened,
1313 tracker_closed, forum, docs, downloads, cvs_commits, tasks_opened,
1315 SELECT group_id, $1::int AS month, sum(tracker_opened) AS tracker_opened,
1316 sum(tracker_closed) AS tracker_closed,
1317 sum(forum) AS forum,
1319 sum(downloads) AS downloads,
1320 sum(cvs_commits) AS cvs_commits,
1321 sum(tasks_opened) AS tasks_opened,
1322 sum(tasks_closed) AS tasks_closed
1323 FROM rep_group_act_daily
1324 WHERE DAY BETWEEN $1 AND $2
1325 GROUP BY group_id,month',
1326 array ($month, $end));
1330 * Populate the group_act_monthly report table.
1333 * @return bool Success.
1335 function backfill_group_act_monthly($count=10000) {
1337 $arr = array_slice ($this->getMonthStartArr(), -$count-1);
1340 for ($i=1; $i<count($arr); $i++) {
1341 if (!$this->group_act_monthly($arr[$i],($arr[$i-1]-1))) {
1342 $this->setError('backfill_group_act_monthly: Error adding monthly row: '.db_error());
1350 * Add a row to the rep_time_category table.
1352 * @param string $category_name The category name.
1353 * @return bool Success.
1355 function addTimeCode($category_name) {
1356 return db_query_params ('INSERT INTO rep_time_category (category_name) VALUES ($1)',
1357 array($category_name));
1361 * Update the rep_time_category table.
1363 * @param int $time_code
1364 * @param string $category_name The category name.
1365 * @return bool Success.
1367 function updateTimeCode($time_code, $category_name) {
1368 return db_query_params ('UPDATE rep_time_category SET category_name=$1 WHERE time_code=$2',
1369 array($category_name,
1374 * Add a row to the project_status table.
1376 * @param string $status_name The Status name.
1377 * @return bool Success.
1379 function addStatusId($status_name) {
1380 return db_query_params ('INSERT INTO project_status (status_name) VALUES ($1)',
1381 array($status_name));
1385 * Update the project_status table.
1387 * @param int $status_id
1388 * @param string $status_name The category name.
1389 * @return bool Success.
1391 function updateStatusID($status_id, $status_name) {
1392 return db_query_params ('UPDATE project_status SET status_name=$1 WHERE status_id=$2',
1400 // c-file-style: "bsd"