Fossil SCM

Add a by-hour-of-day report to the /reports page, prompted by a /chat discussion.

drh 2022-10-18 16:17 trunk
Commit bb6f23313ed6e48a322d41b210da413006e8f747d264b11a55ae59f88ba91f06
Parent cf563c721773e65…
2 files changed +80 -9 +1
~ src/statrep.c ~ www/changes.wiki
M src/statrep.c
+80 -9
--- src/statrep.c
+++ src/statrep.c
@@ -197,16 +197,11 @@
197197
 the per-year event totals */
198198
 int nMaxEvents = 1; /* for calculating length of graph
199199
 bars. */
200200
 int iterations = 0; /* number of weeks/months we iterate
201201
 over */
202
- Blob userFilter = empty_blob; /* Optional user=johndoe query string */
203202
 stats_report_init_view();
204
- if( zUserName ){
205
- blob_appendf(&userFilter, "user=%s", zUserName);
206
- }
207
- blob_reset(&userFilter);
208203
 db_prepare(&query,
209204
 "SELECT substr(date(mtime),1,%d) AS timeframe,"
210205
 " count(*) AS eventCount"
211206
 " FROM v_reports"
212207
 " WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
@@ -477,20 +472,16 @@
477472
 int nRowNumber = 0; /* current TR number */
478473
 int rowClass = 0; /* counter for alternating
479474
 row colors */
480475
 int nMaxEvents = 1; /* max number of events for
481476
 all rows. */
482
- Blob userFilter = empty_blob; /* Optional user=johndoe query string */
483477
 static const char *const daysOfWeek[] = {
484478
 "Sunday", "Monday", "Tuesday", "Wednesday",
485479
 "Thursday", "Friday", "Saturday"
486480
 };
487481
488482
 stats_report_init_view();
489
- if( zUserName ){
490
- blob_appendf(&userFilter, "user=%s", zUserName);
491
- }
492483
 db_prepare(&query,
493484
 "SELECT cast(strftime('%%w', mtime) AS INTEGER) dow,"
494485
 " COUNT(*) AS eventCount"
495486
 " FROM v_reports"
496487
 " WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
@@ -550,10 +541,85 @@
550541
 rowClass = ++nRowNumber % 2;
551542
 @<tr class='row%d(rowClass)'>
552543
 @ <td>%d(dayNum)</td>
553544
 @ <td>%s(daysOfWeek[dayNum])</td>
554545
 @ <td>%d(nCount)</td>
546
+ @ <td>
547
+ @ <div class='statistics-report-graph-line'
548
+ @ style='width:%d(nSize)%%;'>&nbsp;</div>
549
+ @ </td>
550
+ @</tr>
551
+ }
552
+ @ </tbody></table>
553
+ db_finalize(&query);
554
+}
555
+
556
+/*
557
+** Implements the "byhour" view for /reports. If zUserName is not NULL
558
+** then the report is restricted to events created by the named user
559
+** account.
560
+*/
561
+static void stats_report_hour_of_day(const char *zUserName){
562
+ Stmt query = empty_Stmt;
563
+ int nRowNumber = 0; /* current TR number */
564
+ int rowClass = 0; /* counter for alternating
565
+ row colors */
566
+ int nMaxEvents = 1; /* max number of events for
567
+ all rows. */
568
+
569
+ stats_report_init_view();
570
+ db_prepare(&query,
571
+ "SELECT cast(strftime('%%H', mtime) AS INTEGER) hod,"
572
+ " COUNT(*) AS eventCount"
573
+ " FROM v_reports"
574
+ " WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
575
+ " GROUP BY hod ORDER BY hod", zUserName);
576
+ @ <h1>Timeline Events (%h(stats_report_label_for_type())) by Hour of Day
577
+ if( zUserName ){
578
+ @ for user %h(zUserName)
579
+ }
580
+ @ </h1>
581
+ db_multi_exec(
582
+ "CREATE TEMP VIEW piechart(amt,label) AS"
583
+ " SELECT count(*), strftime('%%H', mtime) hod"
584
+ " FROM v_reports"
585
+ " WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
586
+ " GROUP BY 2 ORDER BY hod;",
587
+ zUserName
588
+ );
589
+ if( db_int(0, "SELECT count(*) FROM piechart")>=2 ){
590
+ @ <center><svg width=700 height=400>
591
+ piechart_render(700, 400, PIE_OTHER|PIE_PERCENT);
592
+ @ </svg></centre><hr />
593
+ }
594
+ style_table_sorter();
595
+ @ <table class='statistics-report-table-events sortable' border='0' \
596
+ @ cellpadding='2' cellspacing='0' data-column-types='nnx' data-init-sort='1'>
597
+ @ <thead><tr>
598
+ @ <th>Hour</th>
599
+ @ <th>Events</th>
600
+ @ <th width='90%%'><!-- relative commits graph --></th>
601
+ @ </tr></thead><tbody>
602
+ while( SQLITE_ROW == db_step(&query) ){
603
+ const int nCount = db_column_int(&query, 1);
604
+ if(nCount>nMaxEvents){
605
+ nMaxEvents = nCount;
606
+ }
607
+ }
608
+ db_reset(&query);
609
+ while( SQLITE_ROW == db_step(&query) ){
610
+ const int hourNum =db_column_int(&query, 0);
611
+ const int nCount = db_column_int(&query, 1);
612
+ int nSize = nCount
613
+ ? (int)(100 * nCount / nMaxEvents)
614
+ : 0;
615
+ if(!nCount) continue /* arguable! Possible? */;
616
+ else if(!nSize) nSize = 1;
617
+ rowClass = ++nRowNumber % 2;
618
+ @<tr class='row%d(rowClass)'>
619
+ @ <td>%d(hourNum)</td>
620
+ @ <td>%d(nCount)</td>
555621
 @ <td>
556622
 @ <div class='statistics-report-graph-line'
557623
 @ style='width:%d(nSize)%%;'>&nbsp;</div>
558624
 @ </td>
559625
 @</tr>
@@ -709,10 +775,11 @@
709775
 #define RPT_BYUSER 3
710776
 #define RPT_BYWEEK 4
711777
 #define RPT_BYWEEKDAY 5
712778
 #define RPT_BYYEAR 6
713779
 #define RPT_LASTCHNG 7 /* Last change made for each user */
780
+#define RPT_BYHOUR 8 /* hour-of-day */
714781
 #define RPT_NONE 0 /* None of the above */
715782
716783
 /*
717784
 ** WEBPAGE: reports
718785
 **
@@ -749,10 +816,11 @@
749816
 { "By Month", "bymonth", RPT_BYMONTH },
750817
 { "By User", "byuser", RPT_BYUSER },
751818
 { "By Week", "byweek", RPT_BYWEEK },
752819
 { "By Weekday", "byweekday", RPT_BYWEEKDAY },
753820
 { "By Year", "byyear", RPT_BYYEAR },
821
+ { "By Hour", "byhour", RPT_BYHOUR },
754822
 };
755823
 static const char *const azType[] = {
756824
 "a", "All Changes",
757825
 "ci", "Check-ins",
758826
 "f", "Forum Posts",
@@ -817,11 +885,14 @@
817885
 stats_report_day_of_week(zUserName);
818886
 break;
819887
 case RPT_BYFILE:
820888
 stats_report_by_file(zUserName);
821889
 break;
890
+ case RPT_BYHOUR:
891
+ stats_report_hour_of_day(zUserName);
892
+ break;
822893
 case RPT_LASTCHNG:
823894
 stats_report_last_change();
824895
 break;
825896
 }
826897
 style_finish_page();
827898
 }
828899
--- src/statrep.c
+++ src/statrep.c
@@ -197,16 +197,11 @@
197 the per-year event totals */
198 int nMaxEvents = 1; /* for calculating length of graph
199 bars. */
200 int iterations = 0; /* number of weeks/months we iterate
201 over */
202 Blob userFilter = empty_blob; /* Optional user=johndoe query string */
203 stats_report_init_view();
204 if( zUserName ){
205 blob_appendf(&userFilter, "user=%s", zUserName);
206 }
207 blob_reset(&userFilter);
208 db_prepare(&query,
209 "SELECT substr(date(mtime),1,%d) AS timeframe,"
210 " count(*) AS eventCount"
211 " FROM v_reports"
212 " WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
@@ -477,20 +472,16 @@
477 int nRowNumber = 0; /* current TR number */
478 int rowClass = 0; /* counter for alternating
479 row colors */
480 int nMaxEvents = 1; /* max number of events for
481 all rows. */
482 Blob userFilter = empty_blob; /* Optional user=johndoe query string */
483 static const char *const daysOfWeek[] = {
484 "Sunday", "Monday", "Tuesday", "Wednesday",
485 "Thursday", "Friday", "Saturday"
486 };
487
488 stats_report_init_view();
489 if( zUserName ){
490 blob_appendf(&userFilter, "user=%s", zUserName);
491 }
492 db_prepare(&query,
493 "SELECT cast(strftime('%%w', mtime) AS INTEGER) dow,"
494 " COUNT(*) AS eventCount"
495 " FROM v_reports"
496 " WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
@@ -550,10 +541,85 @@
550 rowClass = ++nRowNumber % 2;
551 @<tr class='row%d(rowClass)'>
552 @ <td>%d(dayNum)</td>
553 @ <td>%s(daysOfWeek[dayNum])</td>
554 @ <td>%d(nCount)</td>
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
555 @ <td>
556 @ <div class='statistics-report-graph-line'
557 @ style='width:%d(nSize)%%;'>&nbsp;</div>
558 @ </td>
559 @</tr>
@@ -709,10 +775,11 @@
709 #define RPT_BYUSER 3
710 #define RPT_BYWEEK 4
711 #define RPT_BYWEEKDAY 5
712 #define RPT_BYYEAR 6
713 #define RPT_LASTCHNG 7 /* Last change made for each user */
 
714 #define RPT_NONE 0 /* None of the above */
715
716 /*
717 ** WEBPAGE: reports
718 **
@@ -749,10 +816,11 @@
749 { "By Month", "bymonth", RPT_BYMONTH },
750 { "By User", "byuser", RPT_BYUSER },
751 { "By Week", "byweek", RPT_BYWEEK },
752 { "By Weekday", "byweekday", RPT_BYWEEKDAY },
753 { "By Year", "byyear", RPT_BYYEAR },
 
754 };
755 static const char *const azType[] = {
756 "a", "All Changes",
757 "ci", "Check-ins",
758 "f", "Forum Posts",
@@ -817,11 +885,14 @@
817 stats_report_day_of_week(zUserName);
818 break;
819 case RPT_BYFILE:
820 stats_report_by_file(zUserName);
821 break;
 
 
 
822 case RPT_LASTCHNG:
823 stats_report_last_change();
824 break;
825 }
826 style_finish_page();
827 }
828
--- src/statrep.c
+++ src/statrep.c
@@ -197,16 +197,11 @@
197 the per-year event totals */
198 int nMaxEvents = 1; /* for calculating length of graph
199 bars. */
200 int iterations = 0; /* number of weeks/months we iterate
201 over */
 
202 stats_report_init_view();
 
 
 
 
203 db_prepare(&query,
204 "SELECT substr(date(mtime),1,%d) AS timeframe,"
205 " count(*) AS eventCount"
206 " FROM v_reports"
207 " WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
@@ -477,20 +472,16 @@
472 int nRowNumber = 0; /* current TR number */
473 int rowClass = 0; /* counter for alternating
474 row colors */
475 int nMaxEvents = 1; /* max number of events for
476 all rows. */
 
477 static const char *const daysOfWeek[] = {
478 "Sunday", "Monday", "Tuesday", "Wednesday",
479 "Thursday", "Friday", "Saturday"
480 };
481
482 stats_report_init_view();
 
 
 
483 db_prepare(&query,
484 "SELECT cast(strftime('%%w', mtime) AS INTEGER) dow,"
485 " COUNT(*) AS eventCount"
486 " FROM v_reports"
487 " WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
@@ -550,10 +541,85 @@
541 rowClass = ++nRowNumber % 2;
542 @<tr class='row%d(rowClass)'>
543 @ <td>%d(dayNum)</td>
544 @ <td>%s(daysOfWeek[dayNum])</td>
545 @ <td>%d(nCount)</td>
546 @ <td>
547 @ <div class='statistics-report-graph-line'
548 @ style='width:%d(nSize)%%;'>&nbsp;</div>
549 @ </td>
550 @</tr>
551 }
552 @ </tbody></table>
553 db_finalize(&query);
554 }
555
556 /*
557 ** Implements the "byhour" view for /reports. If zUserName is not NULL
558 ** then the report is restricted to events created by the named user
559 ** account.
560 */
561 static void stats_report_hour_of_day(const char *zUserName){
562 Stmt query = empty_Stmt;
563 int nRowNumber = 0; /* current TR number */
564 int rowClass = 0; /* counter for alternating
565 row colors */
566 int nMaxEvents = 1; /* max number of events for
567 all rows. */
568
569 stats_report_init_view();
570 db_prepare(&query,
571 "SELECT cast(strftime('%%H', mtime) AS INTEGER) hod,"
572 " COUNT(*) AS eventCount"
573 " FROM v_reports"
574 " WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
575 " GROUP BY hod ORDER BY hod", zUserName);
576 @ <h1>Timeline Events (%h(stats_report_label_for_type())) by Hour of Day
577 if( zUserName ){
578 @ for user %h(zUserName)
579 }
580 @ </h1>
581 db_multi_exec(
582 "CREATE TEMP VIEW piechart(amt,label) AS"
583 " SELECT count(*), strftime('%%H', mtime) hod"
584 " FROM v_reports"
585 " WHERE ifnull(coalesce(euser,user,'')=%Q,1)"
586 " GROUP BY 2 ORDER BY hod;",
587 zUserName
588 );
589 if( db_int(0, "SELECT count(*) FROM piechart")>=2 ){
590 @ <center><svg width=700 height=400>
591 piechart_render(700, 400, PIE_OTHER|PIE_PERCENT);
592 @ </svg></centre><hr />
593 }
594 style_table_sorter();
595 @ <table class='statistics-report-table-events sortable' border='0' \
596 @ cellpadding='2' cellspacing='0' data-column-types='nnx' data-init-sort='1'>
597 @ <thead><tr>
598 @ <th>Hour</th>
599 @ <th>Events</th>
600 @ <th width='90%%'><!-- relative commits graph --></th>
601 @ </tr></thead><tbody>
602 while( SQLITE_ROW == db_step(&query) ){
603 const int nCount = db_column_int(&query, 1);
604 if(nCount>nMaxEvents){
605 nMaxEvents = nCount;
606 }
607 }
608 db_reset(&query);
609 while( SQLITE_ROW == db_step(&query) ){
610 const int hourNum =db_column_int(&query, 0);
611 const int nCount = db_column_int(&query, 1);
612 int nSize = nCount
613 ? (int)(100 * nCount / nMaxEvents)
614 : 0;
615 if(!nCount) continue /* arguable! Possible? */;
616 else if(!nSize) nSize = 1;
617 rowClass = ++nRowNumber % 2;
618 @<tr class='row%d(rowClass)'>
619 @ <td>%d(hourNum)</td>
620 @ <td>%d(nCount)</td>
621 @ <td>
622 @ <div class='statistics-report-graph-line'
623 @ style='width:%d(nSize)%%;'>&nbsp;</div>
624 @ </td>
625 @</tr>
@@ -709,10 +775,11 @@
775 #define RPT_BYUSER 3
776 #define RPT_BYWEEK 4
777 #define RPT_BYWEEKDAY 5
778 #define RPT_BYYEAR 6
779 #define RPT_LASTCHNG 7 /* Last change made for each user */
780 #define RPT_BYHOUR 8 /* hour-of-day */
781 #define RPT_NONE 0 /* None of the above */
782
783 /*
784 ** WEBPAGE: reports
785 **
@@ -749,10 +816,11 @@
816 { "By Month", "bymonth", RPT_BYMONTH },
817 { "By User", "byuser", RPT_BYUSER },
818 { "By Week", "byweek", RPT_BYWEEK },
819 { "By Weekday", "byweekday", RPT_BYWEEKDAY },
820 { "By Year", "byyear", RPT_BYYEAR },
821 { "By Hour", "byhour", RPT_BYHOUR },
822 };
823 static const char *const azType[] = {
824 "a", "All Changes",
825 "ci", "Check-ins",
826 "f", "Forum Posts",
@@ -817,11 +885,14 @@
885 stats_report_day_of_week(zUserName);
886 break;
887 case RPT_BYFILE:
888 stats_report_by_file(zUserName);
889 break;
890 case RPT_BYHOUR:
891 stats_report_hour_of_day(zUserName);
892 break;
893 case RPT_LASTCHNG:
894 stats_report_last_change();
895 break;
896 }
897 style_finish_page();
898 }
899
M www/changes.wiki
+1
--- www/changes.wiki
+++ www/changes.wiki
@@ -11,10 +11,11 @@
1111
 commands which still used the former name, for consistency.
1212
 * Rebuilt [/file/Dockerfile | the stock Dockerfile] to create a "from scratch"
1313
 Busybox based container image via an Alpine Linux intermediary
1414
 * Added [/doc/trunk/www/containers.md | a new document] describing how to
1515
 customize, use, and run that container.
16
+ * Added "by hour of day" report to [/reports?view=byhour|the /reports page].
1617
1718
 <h2 id='v2_19'>Changes for version 2.19 (2022-07-21)</h2>
1819
 * On file listing pages, sort filenames using the "uintnocase" collating
1920
 sequence, so that filenames that contains embedded integers sort in
2021
 numeric order even if they contain a different number of digits.
2122
--- www/changes.wiki
+++ www/changes.wiki
@@ -11,10 +11,11 @@
11 commands which still used the former name, for consistency.
12 * Rebuilt [/file/Dockerfile | the stock Dockerfile] to create a "from scratch"
13 Busybox based container image via an Alpine Linux intermediary
14 * Added [/doc/trunk/www/containers.md | a new document] describing how to
15 customize, use, and run that container.
 
16
17 <h2 id='v2_19'>Changes for version 2.19 (2022-07-21)</h2>
18 * On file listing pages, sort filenames using the "uintnocase" collating
19 sequence, so that filenames that contains embedded integers sort in
20 numeric order even if they contain a different number of digits.
21
--- www/changes.wiki
+++ www/changes.wiki
@@ -11,10 +11,11 @@
11 commands which still used the former name, for consistency.
12 * Rebuilt [/file/Dockerfile | the stock Dockerfile] to create a "from scratch"
13 Busybox based container image via an Alpine Linux intermediary
14 * Added [/doc/trunk/www/containers.md | a new document] describing how to
15 customize, use, and run that container.
16 * Added "by hour of day" report to [/reports?view=byhour|the /reports page].
17
18 <h2 id='v2_19'>Changes for version 2.19 (2022-07-21)</h2>
19 * On file listing pages, sort filenames using the "uintnocase" collating
20 sequence, so that filenames that contains embedded integers sort in
21 numeric order even if they contain a different number of digits.
22

Keyboard Shortcuts

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