Fossil SCM

fossil-scm / src / statrep.c
Blame History Raw 1183 lines
1
/*
2
** Copyright (c) 2013 Stephan Beal
3
**
4
** This program is free software; you can redistribute it and/or
5
** modify it under the terms of the Simplified BSD License (also
6
** known as the "2-Clause License" or "FreeBSD License".)
7
8
** This program is distributed in the hope that it will be useful,
9
** but without any warranty; without even the implied warranty of
10
** merchantability or fitness for a particular purpose.
11
**
12
** Author contact information:
13
** [email protected]
14
** http://www.hwaci.com/drh/
15
**
16
*******************************************************************************
17
**
18
** This file contains code to implement the /reports web page.
19
**
20
*/
21
#include "config.h"
22
#include <string.h>
23
#include <time.h>
24
#include "statrep.h"
25
26
27
/*
28
** Used by stats_report_xxxxx() to remember which type of events
29
** to show. Populated by stats_report_init_view() and holds the
30
** return value of that function.
31
*/
32
static int statsReportType = 0;
33
34
/*
35
** Set by stats_report_init_view() to one of the y=XXXX values
36
** accepted by /timeline?y=XXXX.
37
*/
38
static const char *statsReportTimelineYFlag = NULL;
39
40
41
/*
42
** Creates a TEMP VIEW named v_reports which is a wrapper around the
43
** EVENT table filtered on event.type. It looks for the request
44
** parameter 'type' (reminder: we "should" use 'y' for consistency
45
** with /timeline, but /reports uses 'y' for the year) and expects it
46
** to contain one of the conventional values from event.type or the
47
** value "all", which is treated as equivalent to "*". By default (if
48
** no 'y' is specified), "*" is assumed (that is also the default for
49
** invalid/unknown filter values). That 'y' filter is the one used for
50
** the event list. Note that a filter of "*" or "all" is equivalent to
51
** querying against the full event table. The view, however, adds an
52
** abstraction level to simplify the implementation code for the
53
** various /reports pages.
54
**
55
** Returns one of: 'c', 'f', 'w', 'g', 't', 'e', representing the type of
56
** filter it applies, or '*' if no filter is applied (i.e. if "all" is
57
** used).
58
*/
59
static int stats_report_init_view(){
60
const char *zType = PD("type","*"); /* analog to /timeline?y=... */
61
const char *zRealType = NULL; /* normalized form of zType */
62
int rc = 0; /* result code */
63
char *zTimeSpan; /* Time span */
64
assert( !statsReportType && "Must not be called more than once." );
65
switch( (zType && *zType) ? *zType : 0 ){
66
case 'c':
67
case 'C':
68
zRealType = "ci";
69
rc = *zRealType;
70
break;
71
case 'e':
72
case 'E':
73
zRealType = "e";
74
rc = *zRealType;
75
break;
76
case 'f':
77
case 'F':
78
zRealType = "f";
79
rc = *zRealType;
80
break;
81
case 'h':
82
case 'H':
83
zRealType = "h";
84
rc = *zRealType;
85
break;
86
case 'g':
87
case 'G':
88
zRealType = "g";
89
rc = *zRealType;
90
break;
91
case 'm':
92
case 'M':
93
zRealType = "m";
94
rc = *zRealType;
95
break;
96
case 'n':
97
case 'N':
98
zRealType = "n";
99
rc = *zRealType;
100
break;
101
case 't':
102
case 'T':
103
zRealType = "t";
104
rc = *zRealType;
105
break;
106
case 'w':
107
case 'W':
108
zRealType = "w";
109
rc = *zRealType;
110
break;
111
default:
112
rc = '*';
113
break;
114
}
115
assert(0 != rc);
116
if( P("from")!=0 && P("to")!=0 ){
117
zTimeSpan = mprintf(
118
" (event.mtime BETWEEN julianday(%Q) AND julianday(%Q))",
119
P("from"), P("to"));
120
}else{
121
zTimeSpan = " 1";
122
}
123
if( zRealType==0 ){
124
statsReportTimelineYFlag = "a";
125
db_multi_exec("CREATE TEMP VIEW v_reports AS "
126
"SELECT * FROM event WHERE %s", zTimeSpan/*safe-for-%s*/);
127
}else if( rc=='h' ){
128
statsReportTimelineYFlag = zRealType;
129
db_multi_exec("CREATE TEMP VIEW v_reports AS "
130
"SELECT * FROM event WHERE (type='f') "
131
" AND (comment GLOB 'Post:*') AND %s",
132
zTimeSpan/*safe-for-%s*/);
133
}else if( rc!='n' && rc!='m' ){
134
statsReportTimelineYFlag = zRealType;
135
db_multi_exec("CREATE TEMP VIEW v_reports AS "
136
"SELECT * FROM event WHERE (type GLOB %Q) AND %s",
137
zRealType, zTimeSpan/*safe-for-%s*/);
138
}else{
139
const char *zNot = rc=='n' ? "NOT" : "";
140
statsReportTimelineYFlag = "ci";
141
db_multi_exec(
142
"CREATE TEMP VIEW v_reports AS "
143
"SELECT * FROM event WHERE type='ci' AND %s"
144
" AND objid %s IN (SELECT cid FROM plink WHERE NOT isprim)",
145
zTimeSpan/*safe-for-%s*/, zNot/*safe-for-%s*/
146
);
147
}
148
return statsReportType = rc;
149
}
150
151
/*
152
** Returns a string suitable (for a given value of suitable) for
153
** use in a label with the header of the /reports pages, dependent
154
** on the 'type' flag. See stats_report_init_view().
155
** The returned bytes are static.
156
*/
157
static const char *stats_report_label_for_type(){
158
assert( statsReportType && "Must call stats_report_init_view() first." );
159
switch( statsReportType ){
160
case 'c':
161
return "check-ins";
162
case 'm':
163
return "merge check-ins";
164
case 'n':
165
return "non-merge check-ins";
166
case 'e':
167
return "technotes";
168
case 'f':
169
return "forum posts";
170
case 'h':
171
return "new forum threads";
172
case 'w':
173
return "wiki changes";
174
case 't':
175
return "ticket changes";
176
case 'g':
177
return "tag changes";
178
default:
179
return "all types";
180
}
181
}
182
183
184
/*
185
** Implements the "byyear" and "bymonth" reports for /reports.
186
** If includeMonth is true then it generates the "bymonth" report,
187
** else the "byyear" report. If zUserName is not NULL then the report is
188
** restricted to events created by the named user account.
189
*/
190
static void stats_report_by_month_year(
191
char includeMonth, /* 0 for stats-by-year. 1 for stats-by-month */
192
const char *zUserName /* Only report events by this user */
193
){
194
Stmt query = empty_Stmt;
195
int nRowNumber = 0; /* current TR number */
196
int nEventTotal = 0; /* Total event count */
197
int rowClass = 0; /* counter for alternating
198
row colors */
199
const char *zTimeLabel = includeMonth ? "Year/Month" : "Year";
200
char zPrevYear[5] = {0}; /* For keeping track of when
201
we change years while looping */
202
int nEventsPerYear = 0; /* Total event count for the
203
current year */
204
char showYearTotal = 0; /* Flag telling us when to show
205
the per-year event totals */
206
int nMaxEvents = 1; /* for calculating length of graph
207
bars. */
208
int iterations = 0; /* number of weeks/months we iterate
209
over */
210
211
char *zCurrentTF; /* The timeframe in which 'now' lives */
212
double rNowFraction; /* Fraction of 'now' timeframe that has
213
passed */
214
int nTFChar; /* Prefix of date() for timeframe */
215
216
nTFChar = includeMonth ? 7 : 4;
217
stats_report_init_view();
218
db_prepare(&query,
219
"SELECT substr(date(mtime),1,%d) AS timeframe,"
220
" count(*) AS eventCount"
221
" FROM v_reports"
222
" WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
223
" GROUP BY timeframe"
224
" ORDER BY timeframe DESC",
225
nTFChar, zUserName);
226
@ <h1>Timeline Events (%s(stats_report_label_for_type()))
227
@ by year%s(includeMonth ? "/month" : "")
228
if( zUserName ){
229
@ for user %h(zUserName)
230
}
231
@ </h1>
232
@ <table border='0' cellpadding='2' cellspacing='0' \
233
zCurrentTF = db_text(0, "SELECT substr(date(),1,%d)", nTFChar);
234
if( !includeMonth ){
235
@ class='statistics-report-table-events sortable' \
236
@ data-column-types='tnx' data-init-sort='0'>
237
style_table_sorter();
238
rNowFraction = db_double(0.5,
239
"SELECT (unixepoch() - unixepoch('now','start of year'))*1.0/"
240
" (unixepoch('now','start of year','+1 year') - "
241
" unixepoch('now','start of year'));");
242
}else{
243
@ class='statistics-report-table-events'>
244
rNowFraction = db_double(0.5,
245
"SELECT (unixepoch() - unixepoch('now','start of month'))*1.0/"
246
" (unixepoch('now','start of month','+1 month') - "
247
" unixepoch('now','start of month'));");
248
}
249
@ <thead>
250
@ <th>%s(zTimeLabel)</th>
251
@ <th>Events</th>
252
@ <th width='90%%'><!-- relative commits graph --></th>
253
@ </thead><tbody>
254
/*
255
Run the query twice. The first time we calculate the maximum
256
number of events for a given row. Maybe someone with better SQL
257
Fu can re-implement this with a single query.
258
*/
259
while( SQLITE_ROW == db_step(&query) ){
260
int nCount = db_column_int(&query, 1);
261
if( strcmp(db_column_text(&query,0),zCurrentTF)==0
262
&& rNowFraction>0.05
263
){
264
nCount = (int)(((double)nCount)/rNowFraction);
265
}
266
if(nCount>nMaxEvents){
267
nMaxEvents = nCount;
268
}
269
++iterations;
270
}
271
db_reset(&query);
272
while( SQLITE_ROW == db_step(&query) ){
273
const char *zTimeframe = db_column_text(&query, 0);
274
const int nCount = db_column_int(&query, 1);
275
int nSize = (nCount>0 && nMaxEvents>0)
276
? (int)(100 * nCount / nMaxEvents)
277
: 1;
278
showYearTotal = 0;
279
if(!nSize) nSize = 1;
280
if(includeMonth){
281
/* For Month/year view, add a separator for each distinct year. */
282
if(!*zPrevYear ||
283
(0!=fossil_strncmp(zPrevYear,zTimeframe,4))){
284
showYearTotal = *zPrevYear;
285
if(showYearTotal){
286
rowClass = ++nRowNumber % 2;
287
@ <tr class='row%d(rowClass)'>
288
@ <td></td>
289
@ <td colspan='2'>Yearly total: %d(nEventsPerYear)</td>
290
@</tr>
291
showYearTotal = 0;
292
}
293
nEventsPerYear = 0;
294
memcpy(zPrevYear,zTimeframe,4);
295
rowClass = ++nRowNumber % 2;
296
@ <tr class='row%d(rowClass)'>
297
@ <th colspan='3' class='statistics-report-row-year'>%s(zPrevYear)</th>
298
@ </tr>
299
}
300
}
301
rowClass = ++nRowNumber % 2;
302
nEventTotal += nCount;
303
nEventsPerYear += nCount;
304
@<tr class='row%d(rowClass)'>
305
@ <td>
306
if(includeMonth){
307
cgi_printf("<a href='%R/timeline?"
308
"ym=%t&y=%s",
309
zTimeframe,
310
statsReportTimelineYFlag );
311
/* Reminder: n=nCount is not actually correct for bymonth unless
312
that was the only user who caused events.
313
*/
314
if( zUserName ){
315
cgi_printf("&u=%t", zUserName);
316
}
317
cgi_printf("' target='_new'>%s</a>",zTimeframe);
318
}else {
319
cgi_printf("<a href='?view=byweek&y=%s&type=%c",
320
zTimeframe, (char)statsReportType);
321
if( zUserName ){
322
cgi_printf("&u=%t", zUserName);
323
}
324
cgi_printf("'>%s</a>", zTimeframe);
325
}
326
@ </td><td>%d(nCount)</td>
327
@ <td style='white-space: nowrap;'>
328
if( strcmp(zTimeframe, zCurrentTF)==0
329
&& rNowFraction>0.05
330
&& nCount>0
331
&& nMaxEvents>0
332
){
333
/* If the timespan covered by this row contains "now", then project
334
** the number of changes until the completion of the timespan and
335
** show a dashed box of that projection. */
336
int nProj = (int)(((double)nCount)/rNowFraction);
337
int nExtra = (int)(((double)nCount)/rNowFraction) - nCount;
338
int nXSize = (100 * nExtra)/nMaxEvents;
339
@ <span class='statistics-report-graph-line' \
340
@ style='display:inline-block;min-width:%d(nSize)%%;'>&nbsp;</span>\
341
@ <span class='statistics-report-graph-extra' title='%d(nProj)' \
342
@ style='display:inline-block;min-width:%d(nXSize)%%;'>&nbsp;</span>\
343
}else{
344
@ <div class='statistics-report-graph-line' \
345
@ style='width:%d(nSize)%%;'>&nbsp;</div> \
346
}
347
@ </td>
348
@ </tr>
349
/*
350
Potential improvement: calculate the min/max event counts and
351
use percent-based graph bars.
352
*/
353
}
354
db_finalize(&query);
355
if(includeMonth && !showYearTotal && *zPrevYear){
356
/* Add final year total separator. */
357
rowClass = ++nRowNumber % 2;
358
@ <tr class='row%d(rowClass)'>
359
@ <td></td>
360
@ <td colspan='2'>Yearly total: %d(nEventsPerYear)</td>
361
@</tr>
362
}
363
@ </tbody></table>
364
if(nEventTotal){
365
const char *zAvgLabel = includeMonth ? "month" : "year";
366
int nAvg = iterations ? (nEventTotal/iterations) : 0;
367
@ <br><div>Total events: %d(nEventTotal)
368
@ <br>Average per active %s(zAvgLabel): %d(nAvg)
369
@ </div>
370
}
371
}
372
373
/*
374
** Implements the "byuser" view for /reports.
375
*/
376
static void stats_report_by_user(){
377
Stmt query = empty_Stmt;
378
int nRowNumber = 0; /* current TR number */
379
int rowClass = 0; /* counter for alternating
380
row colors */
381
int nMaxEvents = 1; /* max number of events for
382
all rows. */
383
stats_report_init_view();
384
@ <h1>Timeline Events
385
@ (%s(stats_report_label_for_type())) by User</h1>
386
db_multi_exec(
387
"CREATE TEMP VIEW piechart(amt,label) AS"
388
" SELECT count(*), ifnull(euser,user) FROM v_reports"
389
" GROUP BY ifnull(euser,user) ORDER BY count(*) DESC;"
390
);
391
if( db_int(0, "SELECT count(*) FROM piechart")>=2 ){
392
@ <center><svg width=700 height=400>
393
piechart_render(700, 400, PIE_OTHER|PIE_PERCENT);
394
@ </svg></centre><hr>
395
}
396
style_table_sorter();
397
@ <table class='statistics-report-table-events sortable' border='0' \
398
@ cellpadding='2' cellspacing='0' data-column-types='tkx' data-init-sort='2'>
399
@ <thead><tr>
400
@ <th>User</th>
401
@ <th>Events</th>
402
@ <th width='90%%'><!-- relative commits graph --></th>
403
@ </tr></thead><tbody>
404
db_prepare(&query,
405
"SELECT ifnull(euser,user), "
406
"COUNT(*) AS eventCount "
407
"FROM v_reports "
408
"GROUP BY ifnull(euser,user) ORDER BY eventCount DESC");
409
while( SQLITE_ROW == db_step(&query) ){
410
const int nCount = db_column_int(&query, 1);
411
if(nCount>nMaxEvents){
412
nMaxEvents = nCount;
413
}
414
}
415
db_reset(&query);
416
while( SQLITE_ROW == db_step(&query) ){
417
const char *zUser = db_column_text(&query, 0);
418
const int nCount = db_column_int(&query, 1);
419
char y = (char)statsReportType;
420
int nSize = nCount
421
? (int)(100 * nCount / nMaxEvents)
422
: 0;
423
if(!nCount) continue /* arguable! Possible? */;
424
else if(!nSize) nSize = 1;
425
rowClass = ++nRowNumber % 2;
426
@ <tr class='row%d(rowClass)'>
427
@ <td>
428
@ <a href="?view=bymonth&user=%h(zUser)&type=%c(y)">%h(zUser)</a>
429
@ </td><td data-sortkey='%08x(-nCount)'>%d(nCount)</td>
430
@ <td>
431
@ <div class='statistics-report-graph-line'
432
@ style='width:%d(nSize)%%;'>&nbsp;</div>
433
@ </td>
434
@</tr>
435
/*
436
Potential improvement: calculate the min/max event counts and
437
use percent-based graph bars.
438
*/
439
}
440
@ </tbody></table>
441
db_finalize(&query);
442
}
443
444
/*
445
** Implements the "byfile" view for /reports. If zUserName is not NULL then the
446
** report is restricted to events created by the named user account.
447
*/
448
static void stats_report_by_file(const char *zUserName){
449
Stmt query;
450
int mxEvent = 1; /* max number of events across all rows */
451
int nRowNumber = 0;
452
453
db_multi_exec(
454
"CREATE TEMP TABLE statrep(filename, cnt);"
455
"INSERT INTO statrep(filename, cnt)"
456
" SELECT filename.name, count(distinct mlink.mid)"
457
" FROM filename, mlink, event"
458
" WHERE filename.fnid=mlink.fnid"
459
" AND mlink.mid=event.objid"
460
" AND ifnull(coalesce(euser,user,'')=%Q,1)"
461
" GROUP BY 1", zUserName
462
);
463
db_prepare(&query,
464
"SELECT filename, cnt FROM statrep ORDER BY cnt DESC, filename /*sort*/"
465
);
466
mxEvent = db_int(1, "SELECT max(cnt) FROM statrep");
467
@ <h1>Check-ins Per File
468
if( zUserName ){
469
@ for user %h(zUserName)
470
}
471
@ </h1>
472
style_table_sorter();
473
@ <table class='statistics-report-table-events sortable' border='0' \
474
@ cellpadding='2' cellspacing='0' data-column-types='tNx' data-init-sort='2'>
475
@ <thead><tr>
476
@ <th>File</th>
477
@ <th>Check-ins</th>
478
@ <th width='90%%'><!-- relative commits graph --></th>
479
@ </tr></thead><tbody>
480
while( SQLITE_ROW == db_step(&query) ){
481
const char *zFile = db_column_text(&query, 0);
482
const int n = db_column_int(&query, 1);
483
int sz;
484
if( n<=0 ) continue;
485
sz = (int)(100*n/mxEvent);
486
if( sz==0 ) sz = 1;
487
@<tr class='row%d(++nRowNumber%2)'>
488
@ <td>%z(href("%R/finfo?name=%T",zFile))%h(zFile)</a></td>
489
@ <td>%d(n)</td>
490
@ <td>
491
@ <div class='statistics-report-graph-line'
492
@ style='width:%d(sz)%%;'>&nbsp;</div>
493
@ </td>
494
@</tr>
495
}
496
@ </tbody></table>
497
db_finalize(&query);
498
499
}
500
501
/*
502
** Implements the "byweekday" view for /reports. If zUserName is not NULL then
503
** the report is restricted to events created by the named user account.
504
*/
505
static void stats_report_day_of_week(const char *zUserName){
506
Stmt query = empty_Stmt;
507
int nRowNumber = 0; /* current TR number */
508
int rowClass = 0; /* counter for alternating
509
row colors */
510
int nMaxEvents = 1; /* max number of events for
511
all rows. */
512
static const char *const daysOfWeek[] = {
513
"Sunday", "Monday", "Tuesday", "Wednesday",
514
"Thursday", "Friday", "Saturday"
515
};
516
517
stats_report_init_view();
518
db_prepare(&query,
519
"SELECT cast(strftime('%%w', mtime) AS INTEGER) dow,"
520
" COUNT(*) AS eventCount"
521
" FROM v_reports"
522
" WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
523
" GROUP BY dow ORDER BY dow", zUserName);
524
@ <h1>Timeline Events (%h(stats_report_label_for_type())) by Day of the Week
525
if( zUserName ){
526
@ for user %h(zUserName)
527
}
528
@ </h1>
529
db_multi_exec(
530
"CREATE TEMP VIEW piechart(amt,label) AS"
531
" SELECT count(*),"
532
" CASE cast(strftime('%%w', mtime) AS INT)"
533
" WHEN 0 THEN 'Sunday'"
534
" WHEN 1 THEN 'Monday'"
535
" WHEN 2 THEN 'Tuesday'"
536
" WHEN 3 THEN 'Wednesday'"
537
" WHEN 4 THEN 'Thursday'"
538
" WHEN 5 THEN 'Friday'"
539
" WHEN 6 THEN 'Saturday'"
540
" ELSE 'ERROR'"
541
" END"
542
" FROM v_reports"
543
" WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
544
" GROUP BY 2 ORDER BY cast(strftime('%%w', mtime) AS INT);"
545
, zUserName
546
);
547
if( db_int(0, "SELECT count(*) FROM piechart")>=2 ){
548
@ <center><svg width=700 height=400>
549
piechart_render(700, 400, PIE_OTHER|PIE_PERCENT);
550
@ </svg></centre><hr>
551
}
552
style_table_sorter();
553
@ <table class='statistics-report-table-events sortable' border='0' \
554
@ cellpadding='2' cellspacing='0' data-column-types='ntnx' data-init-sort='1'>
555
@ <thead><tr>
556
@ <th>DoW</th>
557
@ <th>Day</th>
558
@ <th>Events</th>
559
@ <th width='90%%'><!-- relative commits graph --></th>
560
@ </tr></thead><tbody>
561
while( SQLITE_ROW == db_step(&query) ){
562
const int nCount = db_column_int(&query, 1);
563
if(nCount>nMaxEvents){
564
nMaxEvents = nCount;
565
}
566
}
567
db_reset(&query);
568
while( SQLITE_ROW == db_step(&query) ){
569
const int dayNum =db_column_int(&query, 0);
570
const int nCount = db_column_int(&query, 1);
571
int nSize = nCount
572
? (int)(100 * nCount / nMaxEvents)
573
: 0;
574
if(!nCount) continue /* arguable! Possible? */;
575
else if(!nSize) nSize = 1;
576
rowClass = ++nRowNumber % 2;
577
@<tr class='row%d(rowClass)'>
578
@ <td>%d(dayNum)</td>
579
@ <td>%s(daysOfWeek[dayNum])</td>
580
@ <td>%d(nCount)</td>
581
@ <td>
582
@ <div class='statistics-report-graph-line'
583
@ style='width:%d(nSize)%%;'>&nbsp;</div>
584
@ </td>
585
@</tr>
586
}
587
@ </tbody></table>
588
db_finalize(&query);
589
}
590
591
/*
592
** Implements the "byhour" view for /reports. If zUserName is not NULL
593
** then the report is restricted to events created by the named user
594
** account.
595
*/
596
static void stats_report_hour_of_day(const char *zUserName){
597
Stmt query = empty_Stmt;
598
int nRowNumber = 0; /* current TR number */
599
int rowClass = 0; /* counter for alternating
600
row colors */
601
int nMaxEvents = 1; /* max number of events for
602
all rows. */
603
604
stats_report_init_view();
605
db_prepare(&query,
606
"SELECT cast(strftime('%%H', mtime) AS INTEGER) hod,"
607
" COUNT(*) AS eventCount"
608
" FROM v_reports"
609
" WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
610
" GROUP BY hod ORDER BY hod", zUserName);
611
@ <h1>Timeline Events (%h(stats_report_label_for_type())) by Hour of Day
612
if( zUserName ){
613
@ for user %h(zUserName)
614
}
615
@ </h1>
616
db_multi_exec(
617
"CREATE TEMP VIEW piechart(amt,label) AS"
618
" SELECT count(*), strftime('%%H', mtime) hod"
619
" FROM v_reports"
620
" WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
621
" GROUP BY 2 ORDER BY hod;",
622
zUserName
623
);
624
if( db_int(0, "SELECT count(*) FROM piechart")>=2 ){
625
@ <center><svg width=700 height=400>
626
piechart_render(700, 400, PIE_OTHER|PIE_PERCENT);
627
@ </svg></centre><hr>
628
}
629
style_table_sorter();
630
@ <table class='statistics-report-table-events sortable' border='0' \
631
@ cellpadding='2' cellspacing='0' data-column-types='nnx' data-init-sort='1'>
632
@ <thead><tr>
633
@ <th>Hour</th>
634
@ <th>Events</th>
635
@ <th width='90%%'><!-- relative commits graph --></th>
636
@ </tr></thead><tbody>
637
while( SQLITE_ROW == db_step(&query) ){
638
const int nCount = db_column_int(&query, 1);
639
if(nCount>nMaxEvents){
640
nMaxEvents = nCount;
641
}
642
}
643
db_reset(&query);
644
while( SQLITE_ROW == db_step(&query) ){
645
const int hourNum =db_column_int(&query, 0);
646
const int nCount = db_column_int(&query, 1);
647
int nSize = nCount
648
? (int)(100 * nCount / nMaxEvents)
649
: 0;
650
if(!nCount) continue /* arguable! Possible? */;
651
else if(!nSize) nSize = 1;
652
rowClass = ++nRowNumber % 2;
653
@<tr class='row%d(rowClass)'>
654
@ <td>%d(hourNum)</td>
655
@ <td>%d(nCount)</td>
656
@ <td>
657
@ <div class='statistics-report-graph-line'
658
@ style='width:%d(nSize)%%;'>&nbsp;</div>
659
@ </td>
660
@</tr>
661
}
662
@ </tbody></table>
663
db_finalize(&query);
664
}
665
666
667
/*
668
** Generator for the by-week report (RPT_BYWEEK).
669
**
670
** The "y" query parameter is the year in format YYYY.
671
**
672
** If zUserName is not NULL then the report is restricted to events
673
** created by the named user account.
674
*/
675
static void stats_report_byweek(const char *zUserName){
676
const char *zYear = P("y"); /* Year for which report shown */
677
int bShowAll = PB("sa"); /* Show all weeks if true, active if false */
678
Stmt q; /* Query */
679
int nMaxEvents = 1; /* max number of events in any row */
680
int rowCount = 0; /* Rows of output generated */
681
int total; /* Total number of events */
682
int iCurrentWeek; /* Current week number */
683
double rNowFraction = 0.0; /* Fraction of current week that has passed */
684
double rYearStart; /* Start of year */
685
double rYearEnd; /* End of the year */
686
double rWeekOne; /* Start of first Monday of the year */
687
double rWeekZero; /* If Jan01 is Mon, then rWeekOne, else rWeekOne-7.0 */
688
double rAllEnd; /* End of last week of year, overflow into next year */
689
690
stats_report_init_view();
691
style_submenu_sql("y", "Year:",
692
"WITH RECURSIVE a(b) AS ("
693
" SELECT substr(date('now'),1,4) UNION ALL"
694
" SELECT b-1 FROM a\n"
695
" WHERE b>0+(SELECT substr(date(min(mtime)),1,4) FROM event)"
696
") SELECT b, b FROM a ORDER BY b DESC"
697
);
698
style_submenu_checkbox("sa", "Show-All", 0, 0);
699
if( zYear==0 || strlen(zYear)!=4 ){
700
zYear = db_text("1970","SELECT substr(date('now'),1,4);");
701
}
702
rYearStart = db_double(0.0,"SELECT julianday('%q-01-01')",zYear);
703
rYearEnd = db_double(0.0,"SELECT julianday('%q-01-01','end of year')",zYear);
704
rWeekOne = db_double(0.0,"SELECT julianday('%q-01-01','weekday 1')",zYear);
705
rWeekZero = rWeekOne>rYearStart ? rWeekOne - 7.0 : rWeekOne;
706
rAllEnd = db_double(0.0,
707
"SELECT julianday('%q-01-01','end of year','weekday 0')",
708
zYear);
709
db_multi_exec("CREATE TEMP TABLE wkdata(wk,n);");
710
db_prepare(&q,
711
"WITH RECURSIVE c(wkn) AS (\n"
712
" VALUES(0+strftime('%%W',:YearStart))\n"
713
" UNION ALL\n"
714
" SELECT wkn+1 FROM c\n"
715
" WHERE wkn<0+strftime('%%W',min(julianday(),:YearEnd))\n"
716
")\n"
717
"INSERT INTO wkdata(wk,n)\n"
718
" SELECT c.wkn, coalesce(x.n,0)\n"
719
" FROM c LEFT JOIN (\n"
720
" SELECT 0+strftime('%%W',min(max(mtime,:YearStart),:YearEnd)) AS w,\n"
721
" count(*) AS n\n"
722
" FROM v_reports\n"
723
" WHERE mtime BETWEEN :WeekZero AND :AllEnd\n"
724
" AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
725
" GROUP BY w\n"
726
" ) AS x ON c.wkn=x.w",
727
zUserName
728
);
729
db_bind_double(&q, ":YearStart", rYearStart);
730
db_bind_double(&q, ":YearEnd", rYearEnd);
731
db_bind_double(&q, ":WeekZero", rWeekZero);
732
db_bind_double(&q, ":AllEnd", rAllEnd);
733
db_step(&q);
734
db_finalize(&q);
735
736
cgi_printf("<br>\n");
737
738
#if 0
739
@ :YearStart = %h(db_text("","SELECT datetime(%!.17g)",rYearStart))<br>
740
@ :YearEnd = %h(db_text("","SELECT datetime(%!.17g)",rYearEnd))<br>
741
@ :WeekZero = %h(db_text("","SELECT datetime(%!.17g)",rWeekZero))<br>
742
@ :AllEnd = %h(db_text("","SELECT datetime(%!.17g)",rAllEnd))<br>
743
#endif
744
745
@ <h1>Timeline events (%h(stats_report_label_for_type()))
746
@ for the calendar weeks of %h(zYear)
747
if( zUserName ){
748
@ for user %h(zUserName)
749
}
750
@ </h1>
751
iCurrentWeek = db_int(0,
752
"SELECT strftime('%%W','now') WHERE date() LIKE '%q-%%'", zYear);
753
if( iCurrentWeek>0 ){
754
rNowFraction = db_double(0.5,
755
"SELECT (unixepoch()-unixepoch('now','weekday 0','-7 days'))/604800.0;");
756
}
757
style_table_sorter();
758
cgi_printf("<table class='statistics-report-table-events sortable' "
759
"border='0' cellpadding='2' width='100%%' "
760
"cellspacing='0' data-column-types='tnx' data-init-sort='0'>\n");
761
cgi_printf("<thead><tr>"
762
"<th>Week</th>"
763
"<th>Events</th>"
764
"<th width='90%%'><!-- relative commits graph --></th>"
765
"</tr></thead>\n"
766
"<tbody>\n");
767
nMaxEvents = db_int(0, "SELECT max(n) FROM wkdata");
768
db_prepare(&q, "SELECT wk, n FROM wkdata ORDER BY wk DESC");
769
while( SQLITE_ROW == db_step(&q) ){
770
const int iWeek = db_column_int(&q,0);
771
const int nCount = db_column_int(&q,1);
772
int nSize = (nCount>0 && nMaxEvents>0)
773
? (int)(100 * nCount / nMaxEvents)
774
: 0;
775
if( nCount==0 && !bShowAll ) continue;
776
if(!nSize) nSize = 1;
777
cgi_printf("<tr class='row%d'>", ++rowCount % 2 );
778
cgi_printf("<td><a href='%R/timeline?yw=%t%02d&y=%s",
779
zYear, iWeek,
780
statsReportTimelineYFlag);
781
if( zUserName ){
782
cgi_printf("&u=%t",zUserName);
783
}
784
cgi_printf("'>%02d</a></td>",iWeek);
785
786
cgi_printf("<td>%d</td>",nCount);
787
cgi_printf("<td style='white-space: nowrap;'>");
788
if( nCount ){
789
if( iCurrentWeek==iWeek
790
&& rNowFraction>0.05
791
&& nMaxEvents>0
792
){
793
/* If the timespan covered by this row contains "now", then project
794
** the number of changes until the completion of the week and
795
** show a dashed box of that projection. */
796
int nProj = (int)(((double)nCount)/rNowFraction);
797
int nExtra = (int)(((double)nCount)/rNowFraction) - nCount;
798
int nXSize = (100 * nExtra)/nMaxEvents;
799
@ <span class='statistics-report-graph-line' \
800
@ style='display:inline-block;min-width:%d(nSize)%%;'>&nbsp;</span>\
801
@ <span class='statistics-report-graph-extra' title='%d(nProj)' \
802
@ style='display:inline-block;min-width:%d(nXSize)%%;'>&nbsp;</span>\
803
}else{
804
@ <div class='statistics-report-graph-line' \
805
@ style='width:%d(nSize)%%;'>&nbsp;</div> \
806
}
807
}
808
cgi_printf("</td></tr>\n");
809
}
810
db_finalize(&q);
811
cgi_printf("</tbody></table>\n<br>\n<div\n");
812
total = db_int(0, "SELECT sum(n) FROM wkdata");
813
cgi_printf("Total events: %d<br>\n", total);
814
if( total ){
815
cgi_printf("Weeks covered: %d<br>\n",
816
db_int(0, "SELECT count(*) FROM wkdata"));
817
cgi_printf("Average events per week: %.1f<br>\n",
818
db_double(0.0, "SELECT avg(n) FROM wkdata"));
819
cgi_printf("Median events per week: %.1f<br>\n",
820
db_double(0.0, "SELECT median(n) FROM wkdata"));
821
cgi_printf("Active weeks: %d<br>\n",
822
db_int(0, "SELECT count(*) FROM wkdata WHERE n>0"));
823
cgi_printf("Average events per active week: %.1f<br>\n",
824
db_double(0.0, "SELECT avg(n) FROM wkdata WHERE n>0"));
825
cgi_printf("Median events per active week: %.1f<br>\n",
826
db_double(0.0, "SELECT median(n) FROM wkdata WHERE n>0"));
827
}
828
}
829
830
/*
831
** Generator for the by-day report (RPT_BYDAY).
832
**
833
** The "y" query parameter is the year in format YYYY.
834
**
835
** If zUserName is not NULL then the report is restricted to events
836
** created by the named user account.
837
*/
838
static void stats_report_byday(const char *zUserName){
839
const char *zYear = P("y"); /* Year for which report shown */
840
int bShowAll = PB("sa"); /* Show all days if true, active if false */
841
char *zLimit; /* Date of last day to show */
842
Stmt q;
843
int nMaxEvents = 1; /* max number of events for all rows. */
844
int rowCount = 0;
845
int total;
846
char *zCurrentDay; /* Current week number */
847
double rNowFraction = 0.0; /* Fraction of current day that has
848
** passed */
849
850
stats_report_init_view();
851
style_submenu_sql("y", "Year:",
852
"WITH RECURSIVE a(b) AS ("
853
" SELECT substr(date('now'),1,4) UNION ALL"
854
" SELECT b-1 FROM a\n"
855
" WHERE b>0+(SELECT substr(date(min(mtime)),1,4) FROM event)"
856
") SELECT b, b FROM a ORDER BY b DESC"
857
);
858
style_submenu_checkbox("sa", "Show-All", 0, 0);
859
if( zYear==0 || strlen(zYear)!=4 ){
860
zYear = db_text("1970","SELECT substr(date('now'),1,4);");
861
}
862
zLimit = db_text("1971-01-01",
863
"SELECT min(date('%q-01-01','+1 year','-1 day'),date())",
864
zYear
865
);
866
db_multi_exec(
867
"CREATE TEMP TABLE daydata(dn,isodate,dow,n);\n"
868
"WITH RECURSIVE c(daynum, isodate, dow) AS (\n"
869
" VALUES(0,date('%q-01-01'),0+strftime('%%w','%q-01-01'))\n"
870
" UNION ALL\n"
871
" SELECT daynum+1, date(isodate,'+1 day'), (dow+1)%%7\n"
872
" FROM c\n"
873
" WHERE isodate<%Q\n"
874
")\n"
875
"INSERT INTO daydata(dn,isodate,dow,n)\n"
876
" SELECT c.daynum, c.isodate, c.dow, coalesce(x.n,0)\n"
877
" FROM c LEFT JOIN (\n"
878
" SELECT date(mtime) AS edate, count(*) AS n\n"
879
" FROM v_reports\n"
880
" WHERE mtime BETWEEN julianday('%q-01-01')\n"
881
" AND julianday('%q 23:59:59.999')\n"
882
" AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
883
" GROUP BY edate\n"
884
" ) AS x ON c.isodate=x.edate;\n",
885
zYear, zYear,
886
zLimit,
887
zYear,
888
zLimit,
889
zUserName
890
);
891
cgi_printf("<br>\n");
892
@ <h1>Timeline events (%h(stats_report_label_for_type()))
893
@ for the individual days of year %h(zYear)
894
if( zUserName ){
895
@ for user %h(zUserName)
896
}
897
@ </h1>
898
zCurrentDay = db_text(0, "SELECT date()");
899
if( zCurrentDay ){
900
rNowFraction = db_double(0.5,
901
"SELECT (unixepoch()-unixepoch('now','start of day'))/86400.0;");
902
}
903
style_table_sorter();
904
cgi_printf("<table class='statistics-report-table-events sortable' "
905
"border='0' cellpadding='2' width='100%%' "
906
"cellspacing='0' data-column-types='tnx' data-init-sort='0'>\n");
907
cgi_printf("<thead><tr>"
908
"<th>Day</th>"
909
"<th>Events</th>"
910
"<th width='90%%'><!-- relative commits graph --></th>"
911
"</tr></thead>\n"
912
"<tbody>\n");
913
nMaxEvents = db_int(0, "SELECT max(n) FROM daydata");
914
db_prepare(&q, "SELECT isodate,dow,n FROM daydata ORDER BY dn DESC");
915
while( SQLITE_ROW == db_step(&q) ){
916
static const char *azDayName[] = {
917
"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"
918
};
919
const char *zDate = db_column_text(&q,0);
920
int iDOW = db_column_int(&q,1);
921
const int nCount = db_column_int(&q,2);
922
int nSize = (nCount>0 && nMaxEvents>0)
923
? (int)(100 * nCount / nMaxEvents)
924
: 0;
925
assert( iDOW>=0 && iDOW<=6 );
926
if( nCount==0 && !bShowAll ) continue;
927
if(!nSize) nSize = 1;
928
cgi_printf("<tr class='row%d'>", ++rowCount % 2 );
929
cgi_printf("<td><a href='%R/timeline?ymd=%s&y=%s",
930
zDate, statsReportTimelineYFlag);
931
if( zUserName ){
932
cgi_printf("&u=%t",zUserName);
933
}
934
cgi_printf("'>%s&nbsp;(%s)</a></td>",zDate,azDayName[iDOW]);
935
936
cgi_printf("<td>%d</td>",nCount);
937
cgi_printf("<td style='white-space: nowrap;'>");
938
if( nCount ){
939
if( zCurrentDay!=0
940
&& strcmp(zDate, zCurrentDay)==0
941
&& rNowFraction>0.05
942
&& nMaxEvents>0
943
){
944
/* If the timespan covered by this row contains "now", then project
945
** the number of changes until the completion of the day and
946
** show a dashed box of that projection. */
947
int nProj = (int)(nCount/rNowFraction);
948
int nExtra = (int)(100.0*nCount/rNowFraction - 100*nCount);
949
int nXSize = nExtra/nMaxEvents;
950
@ <span class='statistics-report-graph-line' \
951
@ style='display:inline-block;min-width:%d(nSize)%%;'>&nbsp;</span>\
952
@ <span class='statistics-report-graph-extra' title='%d(nProj)' \
953
@ style='display:inline-block;min-width:%d(nXSize)%%;'>&nbsp;</span>\
954
}else{
955
@ <div class='statistics-report-graph-line' \
956
@ style='width:%d(nSize)%%;'>&nbsp;</div> \
957
}
958
}
959
cgi_printf("</td></tr>\n");
960
}
961
db_finalize(&q);
962
cgi_printf("</tbody></table>\n<br>\n<div\n");
963
total = db_int(0, "SELECT sum(n) FROM daydata");
964
cgi_printf("Total events: %d<br>\n", total);
965
if( total ){
966
cgi_printf("Days covered: %d<br>\n",
967
db_int(0, "SELECT count(*) FROM daydata"));
968
cgi_printf("Average events per day: %.1f<br>\n",
969
db_double(0.0, "SELECT avg(n) FROM daydata"));
970
cgi_printf("Median events per day: %.1f<br>\n",
971
db_double(0.0, "SELECT median(n) FROM daydata"));
972
cgi_printf("Active days: %d<br>\n",
973
db_int(0, "SELECT count(*) FROM daydata WHERE n>0"));
974
cgi_printf("Average events per active day: %.1f<br>\n",
975
db_double(0.0, "SELECT avg(n) FROM daydata WHERE n>0"));
976
cgi_printf("Median events per active day: %.1f<br>\n",
977
db_double(0.0, "SELECT median(n) FROM daydata WHERE n>0"));
978
}
979
}
980
981
982
/*
983
** Generate a report that shows the most recent change for each user.
984
*/
985
static void stats_report_last_change(void){
986
Stmt s;
987
double rNow;
988
char *zBaseUrl;
989
990
stats_report_init_view();
991
style_table_sorter();
992
@ <h1>Event Summary
993
@ (%s(stats_report_label_for_type())) by User</h1>
994
@ <table border=1 class='statistics-report-table-events sortable' \
995
@ cellpadding=2 cellspacing=0 data-column-types='tNK' data-init-sort='3'>
996
@ <thead><tr>
997
@ <th>User<th>Total Changes<th>Last Change</tr></thead>
998
@ <tbody>
999
zBaseUrl = mprintf("%R/timeline?y=%t&u=", PD("type","ci"));
1000
db_prepare(&s,
1001
"SELECT coalesce(euser,user),"
1002
" count(*),"
1003
" max(mtime)"
1004
" FROM v_reports"
1005
" GROUP BY 1"
1006
" ORDER BY 3 DESC"
1007
);
1008
rNow = db_double(0.0, "SELECT julianday('now');");
1009
while( db_step(&s)==SQLITE_ROW ){
1010
const char *zUser = db_column_text(&s, 0);
1011
int cnt = db_column_int(&s, 1);
1012
double rMTime = db_column_double(&s,2);
1013
char *zAge = human_readable_age(rNow - rMTime);
1014
@ <tr>
1015
@ <td><a href='%s(zBaseUrl)%t(zUser)'>%h(zUser)</a>
1016
@ <td>%d(cnt)
1017
@ <td data-sortkey='%f(rMTime)' style='white-space:nowrap'>%s(zAge?zAge:"")
1018
@ </tr>
1019
fossil_free(zAge);
1020
}
1021
@ </tbody></table>
1022
db_finalize(&s);
1023
}
1024
1025
1026
/* Report types
1027
*/
1028
#define RPT_BYFILE 1
1029
#define RPT_BYMONTH 2
1030
#define RPT_BYUSER 3
1031
#define RPT_BYWEEK 4
1032
#define RPT_BYWEEKDAY 5
1033
#define RPT_BYYEAR 6
1034
#define RPT_LASTCHNG 7 /* Last change made for each user */
1035
#define RPT_BYHOUR 8 /* hour-of-day */
1036
#define RPT_BYDAY 9
1037
#define RPT_NONE 0 /* None of the above */
1038
1039
/*
1040
** WEBPAGE: reports
1041
**
1042
** Shows activity reports for the repository.
1043
**
1044
** Query Parameters:
1045
**
1046
** view=REPORT_NAME Valid REPORT_NAME values:
1047
** * byyear
1048
** * bymonth
1049
** * byweek
1050
** * byday
1051
** * byweekday
1052
** * byhour
1053
** * byuser
1054
** * byfile
1055
** * lastchng
1056
** user=NAME Restricts statistics to the given user
1057
** type=TYPE Restricts the report to a specific event type:
1058
** * all (everything),
1059
** * ci (check-in)
1060
** * m (merge check-in),
1061
** * n (non-merge check-in)
1062
** * f (forum post)
1063
** * h (forum thread)
1064
** * w (wiki page change)
1065
** * t (ticket change)
1066
** * g (tag added or removed)
1067
** Defaulting to all event types.
1068
** from=DATETIME Consider only events after this time (requires to)
1069
** to=DATETIME Consider only events before this time (requires from)
1070
**
1071
**
1072
** The view-specific query parameters include:
1073
**
1074
** view=byweek:
1075
**
1076
** y=YYYY The year to report (default is the server's
1077
** current year).
1078
*/
1079
void stats_report_page(){
1080
const char *zView = P("view"); /* Which view/report to show. */
1081
int eType = RPT_NONE; /* Numeric code for view/report to show */
1082
int i; /* Loop counter */
1083
const char *zUserName; /* Name of user */
1084
const char *azView[24]; /* Drop-down menu of view types */
1085
static const struct {
1086
const char *zName; /* Name of view= screen type */
1087
const char *zVal; /* Value of view= query parameter */
1088
int eType; /* Corresponding RPT_* define */
1089
} aViewType[] = {
1090
{ "File Changes","byfile", RPT_BYFILE },
1091
{ "Last Change", "lastchng", RPT_LASTCHNG },
1092
{ "By Month", "bymonth", RPT_BYMONTH },
1093
{ "By User", "byuser", RPT_BYUSER },
1094
{ "By Week", "byweek", RPT_BYWEEK },
1095
{ "By Day", "byday", RPT_BYDAY },
1096
{ "By Weekday", "byweekday", RPT_BYWEEKDAY },
1097
{ "By Year", "byyear", RPT_BYYEAR },
1098
{ "By Hour", "byhour", RPT_BYHOUR },
1099
};
1100
static const char *const azType[] = {
1101
"a", "All Changes",
1102
"ci", "Check-ins",
1103
"f", "Forum Posts",
1104
"h", "Forum Threads",
1105
"m", "Merge check-ins",
1106
"n", "Non-merge check-ins",
1107
"g", "Tags",
1108
"e", "Tech Notes",
1109
"t", "Tickets",
1110
"w", "Wiki"
1111
};
1112
1113
login_check_credentials();
1114
if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
1115
zUserName = P("user");
1116
if( zUserName==0 ) zUserName = P("u");
1117
if( zUserName && zUserName[0]==0 ) zUserName = 0;
1118
if( zView==0 ){
1119
zView = "byuser";
1120
cgi_replace_query_parameter("view","byuser");
1121
}
1122
for(i=0; i<count(aViewType); i++){
1123
if( fossil_strcmp(zView, aViewType[i].zVal)==0 ){
1124
eType = aViewType[i].eType;
1125
break;
1126
}
1127
}
1128
cgi_check_for_malice();
1129
if( eType!=RPT_NONE ){
1130
int nView = 0; /* Slots used in azView[] */
1131
for(i=0; i<count(aViewType); i++){
1132
azView[nView++] = aViewType[i].zVal;
1133
azView[nView++] = aViewType[i].zName;
1134
}
1135
if( eType!=RPT_BYFILE ){
1136
style_submenu_multichoice("type", count(azType)/2, azType, 0);
1137
}
1138
style_submenu_multichoice("view", nView/2, azView, 0);
1139
if( eType!=RPT_BYUSER && eType!=RPT_LASTCHNG ){
1140
style_submenu_sql("user","User:",
1141
"SELECT '', 'All Users' UNION ALL "
1142
"SELECT x, x FROM ("
1143
" SELECT DISTINCT trim(coalesce(euser,user)) AS x FROM event %s"
1144
" ORDER BY 1 COLLATE nocase) WHERE x!=''",
1145
eType==RPT_BYFILE ? "WHERE type='ci'" : ""
1146
);
1147
}
1148
}
1149
style_submenu_element("Stats", "%R/stat");
1150
style_header("Activity Reports");
1151
switch( eType ){
1152
case RPT_BYYEAR:
1153
stats_report_by_month_year(0, zUserName);
1154
break;
1155
case RPT_BYMONTH:
1156
stats_report_by_month_year(1, zUserName);
1157
break;
1158
case RPT_BYWEEK:
1159
stats_report_byweek(zUserName);
1160
break;
1161
case RPT_BYDAY:
1162
stats_report_byday(zUserName);
1163
break;
1164
default:
1165
case RPT_BYUSER:
1166
stats_report_by_user();
1167
break;
1168
case RPT_BYWEEKDAY:
1169
stats_report_day_of_week(zUserName);
1170
break;
1171
case RPT_BYFILE:
1172
stats_report_by_file(zUserName);
1173
break;
1174
case RPT_BYHOUR:
1175
stats_report_hour_of_day(zUserName);
1176
break;
1177
case RPT_LASTCHNG:
1178
stats_report_last_change();
1179
break;
1180
}
1181
style_finish_page();
1182
}
1183

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button