Fossil SCM

Additional refinements to the byweek algorithm for /report. It appears that the yw= query parameter for /timeline does not work correctly for years that start on Monday. This is the fault of /timeline, not /report and will need to be fixed separately.

drh 2026-07-01 01:44 UTC trunk
Commit a34352a1acb0d9023d3ecdb8e27008aece1457ab587fb1ee0a69fb0ae5b6a920
Parent fbffbe115252be5…
1 file changed +42 -28
~ src/statrep.c
M src/statrep.c
+42 -28
--- src/statrep.c
+++ src/statrep.c
@@ -670,23 +670,25 @@
670670
 ** The "y" query parameter is the year in format YYYY.
671671
 **
672672
 ** If zUserName is not NULL then the report is restricted to events
673673
 ** created by the named user account.
674674
 */
675
-static void stats_report_year_weeks(const char *zUserName){
675
+static void stats_report_byweek(const char *zUserName){
676676
 const char *zYear = P("y"); /* Year for which report shown */
677677
 int bShowAll = PB("sa"); /* Show all weeks if true, active if false */
678
- char *zLimit; /* Date of last week to show */
679678
 Stmt q;
680679
 int nMaxEvents = 1; /* max number of events for all rows. */
681680
 int rowCount = 0;
682681
 int total;
683682
 int iCurrentWeek; /* Current week number */
684683
 double rNowFraction = 0.0; /* Fraction of current week that has
685684
 ** passed */
686
- double rStartOfYear = 0.0; /* Start of year */
687
- double rEndOfYear = 0.0; /* End of the year */
685
+ double rYearStart; /* Start of year */
686
+ double rYearEnd; /* End of the year */
687
+ double rWeekOne; /* Start of first Monday of the year */
688
+ double rWeekZero; /* If Jan01 is Mon, then rWeekOne, else rWeekOne-7.0 */
689
+ double rAllEnd; /* End of last week of year, overflow into next year */
688690
689691
 stats_report_init_view();
690692
 style_submenu_sql("y", "Year:",
691693
 "WITH RECURSIVE a(b) AS ("
692694
 " SELECT substr(date('now'),1,4) UNION ALL"
@@ -696,41 +698,53 @@
696698
 );
697699
 style_submenu_checkbox("sa", "Show-All", 0, 0);
698700
 if( zYear==0 || strlen(zYear)!=4 ){
699701
 zYear = db_text("1970","SELECT substr(date('now'),1,4);");
700702
 }
701
- zLimit = db_text("1971-01-01",
702
- "SELECT min(date('%q-01-01','+1 year','-1 day','weekday 6'),"
703
- "date('now','weekday 6'))",
704
- zYear
705
- );
706
- rStartOfYear = db_double(0.0,"SELECT julianday('%q-01-01')",zYear);
707
- rEndOfYear = db_double(0.0,"SELECT julianday('%q-12-31 23:59:59.999')",zYear);
708
- db_multi_exec(
709
- "CREATE TEMP TABLE wkdata(wk,n);\n"
703
+ rYearStart = db_double(0.0,"SELECT julianday('%q-01-01')",zYear);
704
+ rYearEnd = db_double(0.0,"SELECT julianday('%q-12-31 23:59:59.999')",zYear);
705
+ rWeekOne = db_double(0.0,"SELECT julianday('%q-01-01','weekday 1')",zYear);
706
+ rWeekZero = rWeekOne>rYearStart ? rWeekOne - 7.0 : rWeekOne;
707
+ rAllEnd = db_double(0.0,"SELECT julianday('%q-12-31 23:59:59.999','weekday 0')",zYear);
708
+ db_multi_exec("CREATE TEMP TABLE wkdata(wk,n);");
709
+ db_prepare(&q,
710710
 "WITH RECURSIVE c(wkn) AS (\n"
711
- " VALUES(0)\n"
712
- " UNION ALL\n"
713
- " SELECT wkn+1 FROM c\n"
714
- " WHERE date('%q-01-01',format('%%+d days',wkn*7+7))<=%Q\n"
711
+ " VALUES(if(:WeekZero<:WeekOne,0,1))\n"
712
+ " UNION ALL\n"
713
+ " SELECT wkn+1 FROM c\n"
714
+ " WHERE (:YearStart+wkn*7+7)<=:AllEnd\n"
715715
 ")\n"
716716
 "INSERT INTO wkdata(wk,n)\n"
717717
 " SELECT c.wkn, coalesce(x.n,0)\n"
718718
 " FROM c LEFT JOIN (\n"
719
- " SELECT 0+strftime('%%W',min(max(%!.16g,mtime),%!.16g)) AS w,\n"
720
- " count(*) AS n \n"
719
+ " SELECT 0+strftime('%%W',min(max(:YearStart,mtime),:YearEnd)) AS w,\n"
720
+ " count(*) AS n\n"
721721
 " FROM v_reports\n"
722
- " WHERE mtime BETWEEN julianday('%q-01-01','weekday 6','-6 days')\n"
723
- " AND julianday('%q 23:59:59.999','weekday 6')\n"
722
+ " WHERE mtime BETWEEN :WeekZero AND :AllEnd\n"
724723
 " AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
725724
 " GROUP BY w\n"
726
- " ) AS x ON c.wkn=x.w;\n",
727
- zYear, zLimit,
728
- rStartOfYear, rEndOfYear,
729
- zYear, zLimit, zUserName
725
+ " ) AS x ON c.wkn=x.w",
726
+ zUserName
730727
 );
728
+ db_bind_double(&q, ":YearStart", rYearStart);
729
+ db_bind_double(&q, ":YearEnd", rYearEnd);
730
+ db_bind_double(&q, ":WeekOne", rWeekOne);
731
+ db_bind_double(&q, ":WeekZero", rWeekZero);
732
+ db_bind_double(&q, ":AllEnd", rAllEnd);
733
+ db_step(&q);
734
+ db_finalize(&q);
735
+
731736
 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
+ @ :WeekOne = %h(db_text("","SELECT datetime(%!.17g)",rWeekOne))<br>
743
+ @ :AllEnd = %h(db_text("","SELECT datetime(%!.17g)",rAllEnd))<br>
744
+#endif
745
+
732746
 @ <h1>Timeline events (%h(stats_report_label_for_type()))
733747
 @ for the calendar weeks of %h(zYear)
734748
 if( zUserName ){
735749
 @ for user %h(zUserName)
736750
 }
@@ -820,11 +834,11 @@
820834
 ** The "y" query parameter is the year in format YYYY.
821835
 **
822836
 ** If zUserName is not NULL then the report is restricted to events
823837
 ** created by the named user account.
824838
 */
825
-static void stats_report_year_days(const char *zUserName){
839
+static void stats_report_byday(const char *zUserName){
826840
 const char *zYear = P("y"); /* Year for which report shown */
827841
 int bShowAll = PB("sa"); /* Show all days if true, active if false */
828842
 char *zLimit; /* Date of last day to show */
829843
 Stmt q;
830844
 int nMaxEvents = 1; /* max number of events for all rows. */
@@ -1141,14 +1155,14 @@
11411155
 break;
11421156
 case RPT_BYMONTH:
11431157
 stats_report_by_month_year(1, zUserName);
11441158
 break;
11451159
 case RPT_BYWEEK:
1146
- stats_report_year_weeks(zUserName);
1160
+ stats_report_byweek(zUserName);
11471161
 break;
11481162
 case RPT_BYDAY:
1149
- stats_report_year_days(zUserName);
1163
+ stats_report_byday(zUserName);
11501164
 break;
11511165
 default:
11521166
 case RPT_BYUSER:
11531167
 stats_report_by_user();
11541168
 break;
11551169
--- src/statrep.c
+++ src/statrep.c
@@ -670,23 +670,25 @@
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_year_weeks(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 char *zLimit; /* Date of last week to show */
679 Stmt q;
680 int nMaxEvents = 1; /* max number of events for all rows. */
681 int rowCount = 0;
682 int total;
683 int iCurrentWeek; /* Current week number */
684 double rNowFraction = 0.0; /* Fraction of current week that has
685 ** passed */
686 double rStartOfYear = 0.0; /* Start of year */
687 double rEndOfYear = 0.0; /* End of the year */
 
 
 
688
689 stats_report_init_view();
690 style_submenu_sql("y", "Year:",
691 "WITH RECURSIVE a(b) AS ("
692 " SELECT substr(date('now'),1,4) UNION ALL"
@@ -696,41 +698,53 @@
696 );
697 style_submenu_checkbox("sa", "Show-All", 0, 0);
698 if( zYear==0 || strlen(zYear)!=4 ){
699 zYear = db_text("1970","SELECT substr(date('now'),1,4);");
700 }
701 zLimit = db_text("1971-01-01",
702 "SELECT min(date('%q-01-01','+1 year','-1 day','weekday 6'),"
703 "date('now','weekday 6'))",
704 zYear
705 );
706 rStartOfYear = db_double(0.0,"SELECT julianday('%q-01-01')",zYear);
707 rEndOfYear = db_double(0.0,"SELECT julianday('%q-12-31 23:59:59.999')",zYear);
708 db_multi_exec(
709 "CREATE TEMP TABLE wkdata(wk,n);\n"
710 "WITH RECURSIVE c(wkn) AS (\n"
711 " VALUES(0)\n"
712 " UNION ALL\n"
713 " SELECT wkn+1 FROM c\n"
714 " WHERE date('%q-01-01',format('%%+d days',wkn*7+7))<=%Q\n"
715 ")\n"
716 "INSERT INTO wkdata(wk,n)\n"
717 " SELECT c.wkn, coalesce(x.n,0)\n"
718 " FROM c LEFT JOIN (\n"
719 " SELECT 0+strftime('%%W',min(max(%!.16g,mtime),%!.16g)) AS w,\n"
720 " count(*) AS n \n"
721 " FROM v_reports\n"
722 " WHERE mtime BETWEEN julianday('%q-01-01','weekday 6','-6 days')\n"
723 " AND julianday('%q 23:59:59.999','weekday 6')\n"
724 " AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
725 " GROUP BY w\n"
726 " ) AS x ON c.wkn=x.w;\n",
727 zYear, zLimit,
728 rStartOfYear, rEndOfYear,
729 zYear, zLimit, zUserName
730 );
 
 
 
 
 
 
 
 
731 cgi_printf("<br>\n");
 
 
 
 
 
 
 
 
 
732 @ <h1>Timeline events (%h(stats_report_label_for_type()))
733 @ for the calendar weeks of %h(zYear)
734 if( zUserName ){
735 @ for user %h(zUserName)
736 }
@@ -820,11 +834,11 @@
820 ** The "y" query parameter is the year in format YYYY.
821 **
822 ** If zUserName is not NULL then the report is restricted to events
823 ** created by the named user account.
824 */
825 static void stats_report_year_days(const char *zUserName){
826 const char *zYear = P("y"); /* Year for which report shown */
827 int bShowAll = PB("sa"); /* Show all days if true, active if false */
828 char *zLimit; /* Date of last day to show */
829 Stmt q;
830 int nMaxEvents = 1; /* max number of events for all rows. */
@@ -1141,14 +1155,14 @@
1141 break;
1142 case RPT_BYMONTH:
1143 stats_report_by_month_year(1, zUserName);
1144 break;
1145 case RPT_BYWEEK:
1146 stats_report_year_weeks(zUserName);
1147 break;
1148 case RPT_BYDAY:
1149 stats_report_year_days(zUserName);
1150 break;
1151 default:
1152 case RPT_BYUSER:
1153 stats_report_by_user();
1154 break;
1155
--- src/statrep.c
+++ src/statrep.c
@@ -670,23 +670,25 @@
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;
679 int nMaxEvents = 1; /* max number of events for all rows. */
680 int rowCount = 0;
681 int total;
682 int iCurrentWeek; /* Current week number */
683 double rNowFraction = 0.0; /* Fraction of current week that has
684 ** passed */
685 double rYearStart; /* Start of year */
686 double rYearEnd; /* End of the year */
687 double rWeekOne; /* Start of first Monday of the year */
688 double rWeekZero; /* If Jan01 is Mon, then rWeekOne, else rWeekOne-7.0 */
689 double rAllEnd; /* End of last week of year, overflow into next year */
690
691 stats_report_init_view();
692 style_submenu_sql("y", "Year:",
693 "WITH RECURSIVE a(b) AS ("
694 " SELECT substr(date('now'),1,4) UNION ALL"
@@ -696,41 +698,53 @@
698 );
699 style_submenu_checkbox("sa", "Show-All", 0, 0);
700 if( zYear==0 || strlen(zYear)!=4 ){
701 zYear = db_text("1970","SELECT substr(date('now'),1,4);");
702 }
703 rYearStart = db_double(0.0,"SELECT julianday('%q-01-01')",zYear);
704 rYearEnd = db_double(0.0,"SELECT julianday('%q-12-31 23:59:59.999')",zYear);
705 rWeekOne = db_double(0.0,"SELECT julianday('%q-01-01','weekday 1')",zYear);
706 rWeekZero = rWeekOne>rYearStart ? rWeekOne - 7.0 : rWeekOne;
707 rAllEnd = db_double(0.0,"SELECT julianday('%q-12-31 23:59:59.999','weekday 0')",zYear);
708 db_multi_exec("CREATE TEMP TABLE wkdata(wk,n);");
709 db_prepare(&q,
 
 
710 "WITH RECURSIVE c(wkn) AS (\n"
711 " VALUES(if(:WeekZero<:WeekOne,0,1))\n"
712 " UNION ALL\n"
713 " SELECT wkn+1 FROM c\n"
714 " WHERE (:YearStart+wkn*7+7)<=:AllEnd\n"
715 ")\n"
716 "INSERT INTO wkdata(wk,n)\n"
717 " SELECT c.wkn, coalesce(x.n,0)\n"
718 " FROM c LEFT JOIN (\n"
719 " SELECT 0+strftime('%%W',min(max(:YearStart,mtime),:YearEnd)) AS w,\n"
720 " count(*) AS n\n"
721 " FROM v_reports\n"
722 " WHERE mtime BETWEEN :WeekZero AND :AllEnd\n"
 
723 " AND ifnull(coalesce(euser,user,'')=%Q,1)\n"
724 " GROUP BY w\n"
725 " ) AS x ON c.wkn=x.w",
726 zUserName
 
 
727 );
728 db_bind_double(&q, ":YearStart", rYearStart);
729 db_bind_double(&q, ":YearEnd", rYearEnd);
730 db_bind_double(&q, ":WeekOne", rWeekOne);
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 @ :WeekOne = %h(db_text("","SELECT datetime(%!.17g)",rWeekOne))<br>
743 @ :AllEnd = %h(db_text("","SELECT datetime(%!.17g)",rAllEnd))<br>
744 #endif
745
746 @ <h1>Timeline events (%h(stats_report_label_for_type()))
747 @ for the calendar weeks of %h(zYear)
748 if( zUserName ){
749 @ for user %h(zUserName)
750 }
@@ -820,11 +834,11 @@
834 ** The "y" query parameter is the year in format YYYY.
835 **
836 ** If zUserName is not NULL then the report is restricted to events
837 ** created by the named user account.
838 */
839 static void stats_report_byday(const char *zUserName){
840 const char *zYear = P("y"); /* Year for which report shown */
841 int bShowAll = PB("sa"); /* Show all days if true, active if false */
842 char *zLimit; /* Date of last day to show */
843 Stmt q;
844 int nMaxEvents = 1; /* max number of events for all rows. */
@@ -1141,14 +1155,14 @@
1155 break;
1156 case RPT_BYMONTH:
1157 stats_report_by_month_year(1, zUserName);
1158 break;
1159 case RPT_BYWEEK:
1160 stats_report_byweek(zUserName);
1161 break;
1162 case RPT_BYDAY:
1163 stats_report_byday(zUserName);
1164 break;
1165 default:
1166 case RPT_BYUSER:
1167 stats_report_by_user();
1168 break;
1169

Keyboard Shortcuts

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