Fossil SCM

integrated reports-by-type. Still missing some UI components, but the feature works if the user adds type=XYZ to the URL args.

stephan 2013-10-08 14:01 trunk merge
Commit 358f8e361f60e50e220929cf1c8e312e53a10610
Parent e17e2174a93a8b6…
1 file changed +73 -5
~ src/timeline.c
M src/timeline.c
+73 -5
--- src/timeline.c
+++ src/timeline.c
@@ -1850,10 +1850,75 @@
18501850
 @ <a href="%s(g.zTop)/timeline?p=%S(zUuid)&amp;d=%S(zUuid)">%S(zUuid)</a>
18511851
 }
18521852
 db_finalize(&q);
18531853
 style_footer();
18541854
 }
1855
+
1856
+/*
1857
+** Creates a TEMP VIEW named v_reports which is a wrapper around the
1858
+** EVENT table filtered on event.type. It looks for the request
1859
+** parameter 'type' (reminder: we "should" use 'y' for consistency
1860
+** with /timeline, but /reports uses 'y' for the year) and expects it
1861
+** to contain one of the conventional values from event.type or the
1862
+** value "all", which is treated as equivalent to "*". By default (if
1863
+** no 'y' is specified), "*" is assumed (that is also the default for
1864
+** invalid/unknown filter values). That 'y' filter is the one used for
1865
+** the event list. Note that a filter of "*" or "all" is equivalent to
1866
+** querying against the full event table. The view, however, adds an
1867
+** abstraction level to simplify the implementation code for the
1868
+** various /reports pages.
1869
+**
1870
+** Returns one of: 'c', 'w', 'g', 't', 'e', representing the type of
1871
+** filter it applies, or '*' if no filter is applied (i.e. if "all" is
1872
+** used).
1873
+*/
1874
+static char stats_report_init_view(){
1875
+ char const * zType = PD("type","*"); /* analog to /timeline?y=... */
1876
+ char const * zRealType = NULL; /* normalized form of zType */
1877
+ char rc = 0; /* result code */
1878
+ switch( (zType && *zType) ? *zType : 0 ){
1879
+ case 'c':
1880
+ case 'C':
1881
+ zRealType = "ci";
1882
+ rc = *zRealType;
1883
+ break;
1884
+ case 'e':
1885
+ case 'E':
1886
+ zRealType = "e";
1887
+ rc = *zRealType;
1888
+ break;
1889
+ case 'g':
1890
+ case 'G':
1891
+ zRealType = "g";
1892
+ rc = *zRealType;
1893
+ break;
1894
+ case 't':
1895
+ case 'T':
1896
+ zRealType = "t";
1897
+ rc = *zRealType;
1898
+ break;
1899
+ case 'w':
1900
+ case 'W':
1901
+ zRealType = "w";
1902
+ rc = *zRealType;
1903
+ break;
1904
+ default:
1905
+ rc = '*';
1906
+ break;
1907
+ }
1908
+ assert(0 != rc);
1909
+ if(zRealType){
1910
+ db_multi_exec("CREATE TEMP VIEW v_reports AS "
1911
+ "SELECT * FROM event WHERE type GLOB %Q",
1912
+ zRealType);
1913
+ }else{
1914
+ db_multi_exec("CREATE TEMP VIEW v_reports AS "
1915
+ "SELECT * FROM event");
1916
+ }
1917
+ return rc;
1918
+}
1919
+
18551920
18561921
 /*
18571922
 ** Helper for stats_report_by_month_year(), which generates a list of
18581923
 ** week numbers. zTimeframe should be either a timeframe in the form YYYY
18591924
 ** or YYYY-MM.
@@ -1864,11 +1929,11 @@
18641929
 memcpy(yearPart, zTimeframe, 4);
18651930
 db_prepare(&stWeek,
18661931
 "SELECT DISTINCT strftime('%%W',mtime) AS wk, "
18671932
 "count(*) AS n, "
18681933
 "substr(date(mtime),1,%d) AS ym "
1869
- "FROM event "
1934
+ "FROM v_reports "
18701935
 "WHERE ym=%Q AND mtime < current_timestamp "
18711936
 "GROUP BY wk ORDER BY wk",
18721937
 strlen(zTimeframe),
18731938
 zTimeframe);
18741939
 while( SQLITE_ROW == db_step(&stWeek) ){
@@ -1908,16 +1973,17 @@
19081973
 Blob header = empty_blob; /* Page header text */
19091974
 int nMaxEvents = 1; /* for calculating length of graph
19101975
 bars. */
19111976
 int iterations = 0; /* number of weeks/months we iterate
19121977
 over */
1978
+ stats_report_init_view();
19131979
 blob_appendf(&header, "Timeline Events by year%s",
19141980
 (includeMonth ? "/month" : ""));
19151981
 blob_appendf(&sql,
19161982
 "SELECT substr(date(mtime),1,%d) AS timeframe, "
19171983
 "count(*) AS eventCount "
1918
- "FROM event ",
1984
+ "FROM v_reports ",
19191985
 includeMonth ? 7 : 4);
19201986
 if(zUserName&&*zUserName){
19211987
 blob_appendf(&sql, " WHERE user=%Q ", zUserName);
19221988
 blob_appendf(&header," for user %q", zUserName);
19231989
 }
@@ -2053,14 +2119,15 @@
20532119
 int rowClass = 0; /* counter for alternating
20542120
 row colors */
20552121
 Blob sql = empty_blob; /* SQL */
20562122
 int nMaxEvents = 1; /* max number of events for
20572123
 all rows. */
2124
+ stats_report_init_view();
20582125
 blob_append(&sql,
20592126
 "SELECT user, "
20602127
 "COUNT(*) AS eventCount "
2061
- "FROM event "
2128
+ "FROM v_reports "
20622129
 "GROUP BY user ORDER BY eventCount DESC",
20632130
 -1);
20642131
 db_prepare(&query, blob_str(&sql));
20652132
 blob_reset(&sql);
20662133
 @ <h1>Timeline Events by User</h1>
@@ -2121,14 +2188,15 @@
21212188
 Blob sql = empty_blob;
21222189
 int nMaxEvents = 1; /* max number of events for
21232190
 all rows. */
21242191
 int iterations = 0; /* # of active time periods. */
21252192
2193
+ stats_report_init_view();
21262194
 cgi_printf("Select year: ");
21272195
 blob_append(&sql,
21282196
 "SELECT DISTINCT substr(date(mtime),1,4) AS y "
2129
- "FROM event WHERE 1 ", -1);
2197
+ "FROM v_reports WHERE 1 ", -1);
21302198
 if(zUserName&&*zUserName){
21312199
 blob_appendf(&sql,"AND user=%Q ", zUserName);
21322200
 }
21332201
 blob_append(&sql,"GROUP BY y ORDER BY y", -1);
21342202
 db_prepare(&qYears, blob_str(&sql));
@@ -2159,11 +2227,11 @@
21592227
 blob_appendf(&header, "Timeline events for the calendar weeks "
21602228
 "of %h", zYear);
21612229
 blob_appendf(&sql,
21622230
 "SELECT DISTINCT strftime('%%%%W',mtime) AS wk, "
21632231
 "count(*) AS n "
2164
- "FROM event "
2232
+ "FROM v_reports "
21652233
 "WHERE %Q=substr(date(mtime),1,4) "
21662234
 "AND mtime < current_timestamp ",
21672235
 zYear);
21682236
 if(zUserName&&*zUserName){
21692237
 blob_appendf(&sql, " AND user=%Q ", zUserName);
21702238
--- src/timeline.c
+++ src/timeline.c
@@ -1850,10 +1850,75 @@
1850 @ <a href="%s(g.zTop)/timeline?p=%S(zUuid)&amp;d=%S(zUuid)">%S(zUuid)</a>
1851 }
1852 db_finalize(&q);
1853 style_footer();
1854 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1855
1856 /*
1857 ** Helper for stats_report_by_month_year(), which generates a list of
1858 ** week numbers. zTimeframe should be either a timeframe in the form YYYY
1859 ** or YYYY-MM.
@@ -1864,11 +1929,11 @@
1864 memcpy(yearPart, zTimeframe, 4);
1865 db_prepare(&stWeek,
1866 "SELECT DISTINCT strftime('%%W',mtime) AS wk, "
1867 "count(*) AS n, "
1868 "substr(date(mtime),1,%d) AS ym "
1869 "FROM event "
1870 "WHERE ym=%Q AND mtime < current_timestamp "
1871 "GROUP BY wk ORDER BY wk",
1872 strlen(zTimeframe),
1873 zTimeframe);
1874 while( SQLITE_ROW == db_step(&stWeek) ){
@@ -1908,16 +1973,17 @@
1908 Blob header = empty_blob; /* Page header text */
1909 int nMaxEvents = 1; /* for calculating length of graph
1910 bars. */
1911 int iterations = 0; /* number of weeks/months we iterate
1912 over */
 
1913 blob_appendf(&header, "Timeline Events by year%s",
1914 (includeMonth ? "/month" : ""));
1915 blob_appendf(&sql,
1916 "SELECT substr(date(mtime),1,%d) AS timeframe, "
1917 "count(*) AS eventCount "
1918 "FROM event ",
1919 includeMonth ? 7 : 4);
1920 if(zUserName&&*zUserName){
1921 blob_appendf(&sql, " WHERE user=%Q ", zUserName);
1922 blob_appendf(&header," for user %q", zUserName);
1923 }
@@ -2053,14 +2119,15 @@
2053 int rowClass = 0; /* counter for alternating
2054 row colors */
2055 Blob sql = empty_blob; /* SQL */
2056 int nMaxEvents = 1; /* max number of events for
2057 all rows. */
 
2058 blob_append(&sql,
2059 "SELECT user, "
2060 "COUNT(*) AS eventCount "
2061 "FROM event "
2062 "GROUP BY user ORDER BY eventCount DESC",
2063 -1);
2064 db_prepare(&query, blob_str(&sql));
2065 blob_reset(&sql);
2066 @ <h1>Timeline Events by User</h1>
@@ -2121,14 +2188,15 @@
2121 Blob sql = empty_blob;
2122 int nMaxEvents = 1; /* max number of events for
2123 all rows. */
2124 int iterations = 0; /* # of active time periods. */
2125
 
2126 cgi_printf("Select year: ");
2127 blob_append(&sql,
2128 "SELECT DISTINCT substr(date(mtime),1,4) AS y "
2129 "FROM event WHERE 1 ", -1);
2130 if(zUserName&&*zUserName){
2131 blob_appendf(&sql,"AND user=%Q ", zUserName);
2132 }
2133 blob_append(&sql,"GROUP BY y ORDER BY y", -1);
2134 db_prepare(&qYears, blob_str(&sql));
@@ -2159,11 +2227,11 @@
2159 blob_appendf(&header, "Timeline events for the calendar weeks "
2160 "of %h", zYear);
2161 blob_appendf(&sql,
2162 "SELECT DISTINCT strftime('%%%%W',mtime) AS wk, "
2163 "count(*) AS n "
2164 "FROM event "
2165 "WHERE %Q=substr(date(mtime),1,4) "
2166 "AND mtime < current_timestamp ",
2167 zYear);
2168 if(zUserName&&*zUserName){
2169 blob_appendf(&sql, " AND user=%Q ", zUserName);
2170
--- src/timeline.c
+++ src/timeline.c
@@ -1850,10 +1850,75 @@
1850 @ <a href="%s(g.zTop)/timeline?p=%S(zUuid)&amp;d=%S(zUuid)">%S(zUuid)</a>
1851 }
1852 db_finalize(&q);
1853 style_footer();
1854 }
1855
1856 /*
1857 ** Creates a TEMP VIEW named v_reports which is a wrapper around the
1858 ** EVENT table filtered on event.type. It looks for the request
1859 ** parameter 'type' (reminder: we "should" use 'y' for consistency
1860 ** with /timeline, but /reports uses 'y' for the year) and expects it
1861 ** to contain one of the conventional values from event.type or the
1862 ** value "all", which is treated as equivalent to "*". By default (if
1863 ** no 'y' is specified), "*" is assumed (that is also the default for
1864 ** invalid/unknown filter values). That 'y' filter is the one used for
1865 ** the event list. Note that a filter of "*" or "all" is equivalent to
1866 ** querying against the full event table. The view, however, adds an
1867 ** abstraction level to simplify the implementation code for the
1868 ** various /reports pages.
1869 **
1870 ** Returns one of: 'c', 'w', 'g', 't', 'e', representing the type of
1871 ** filter it applies, or '*' if no filter is applied (i.e. if "all" is
1872 ** used).
1873 */
1874 static char stats_report_init_view(){
1875 char const * zType = PD("type","*"); /* analog to /timeline?y=... */
1876 char const * zRealType = NULL; /* normalized form of zType */
1877 char rc = 0; /* result code */
1878 switch( (zType && *zType) ? *zType : 0 ){
1879 case 'c':
1880 case 'C':
1881 zRealType = "ci";
1882 rc = *zRealType;
1883 break;
1884 case 'e':
1885 case 'E':
1886 zRealType = "e";
1887 rc = *zRealType;
1888 break;
1889 case 'g':
1890 case 'G':
1891 zRealType = "g";
1892 rc = *zRealType;
1893 break;
1894 case 't':
1895 case 'T':
1896 zRealType = "t";
1897 rc = *zRealType;
1898 break;
1899 case 'w':
1900 case 'W':
1901 zRealType = "w";
1902 rc = *zRealType;
1903 break;
1904 default:
1905 rc = '*';
1906 break;
1907 }
1908 assert(0 != rc);
1909 if(zRealType){
1910 db_multi_exec("CREATE TEMP VIEW v_reports AS "
1911 "SELECT * FROM event WHERE type GLOB %Q",
1912 zRealType);
1913 }else{
1914 db_multi_exec("CREATE TEMP VIEW v_reports AS "
1915 "SELECT * FROM event");
1916 }
1917 return rc;
1918 }
1919
1920
1921 /*
1922 ** Helper for stats_report_by_month_year(), which generates a list of
1923 ** week numbers. zTimeframe should be either a timeframe in the form YYYY
1924 ** or YYYY-MM.
@@ -1864,11 +1929,11 @@
1929 memcpy(yearPart, zTimeframe, 4);
1930 db_prepare(&stWeek,
1931 "SELECT DISTINCT strftime('%%W',mtime) AS wk, "
1932 "count(*) AS n, "
1933 "substr(date(mtime),1,%d) AS ym "
1934 "FROM v_reports "
1935 "WHERE ym=%Q AND mtime < current_timestamp "
1936 "GROUP BY wk ORDER BY wk",
1937 strlen(zTimeframe),
1938 zTimeframe);
1939 while( SQLITE_ROW == db_step(&stWeek) ){
@@ -1908,16 +1973,17 @@
1973 Blob header = empty_blob; /* Page header text */
1974 int nMaxEvents = 1; /* for calculating length of graph
1975 bars. */
1976 int iterations = 0; /* number of weeks/months we iterate
1977 over */
1978 stats_report_init_view();
1979 blob_appendf(&header, "Timeline Events by year%s",
1980 (includeMonth ? "/month" : ""));
1981 blob_appendf(&sql,
1982 "SELECT substr(date(mtime),1,%d) AS timeframe, "
1983 "count(*) AS eventCount "
1984 "FROM v_reports ",
1985 includeMonth ? 7 : 4);
1986 if(zUserName&&*zUserName){
1987 blob_appendf(&sql, " WHERE user=%Q ", zUserName);
1988 blob_appendf(&header," for user %q", zUserName);
1989 }
@@ -2053,14 +2119,15 @@
2119 int rowClass = 0; /* counter for alternating
2120 row colors */
2121 Blob sql = empty_blob; /* SQL */
2122 int nMaxEvents = 1; /* max number of events for
2123 all rows. */
2124 stats_report_init_view();
2125 blob_append(&sql,
2126 "SELECT user, "
2127 "COUNT(*) AS eventCount "
2128 "FROM v_reports "
2129 "GROUP BY user ORDER BY eventCount DESC",
2130 -1);
2131 db_prepare(&query, blob_str(&sql));
2132 blob_reset(&sql);
2133 @ <h1>Timeline Events by User</h1>
@@ -2121,14 +2188,15 @@
2188 Blob sql = empty_blob;
2189 int nMaxEvents = 1; /* max number of events for
2190 all rows. */
2191 int iterations = 0; /* # of active time periods. */
2192
2193 stats_report_init_view();
2194 cgi_printf("Select year: ");
2195 blob_append(&sql,
2196 "SELECT DISTINCT substr(date(mtime),1,4) AS y "
2197 "FROM v_reports WHERE 1 ", -1);
2198 if(zUserName&&*zUserName){
2199 blob_appendf(&sql,"AND user=%Q ", zUserName);
2200 }
2201 blob_append(&sql,"GROUP BY y ORDER BY y", -1);
2202 db_prepare(&qYears, blob_str(&sql));
@@ -2159,11 +2227,11 @@
2227 blob_appendf(&header, "Timeline events for the calendar weeks "
2228 "of %h", zYear);
2229 blob_appendf(&sql,
2230 "SELECT DISTINCT strftime('%%%%W',mtime) AS wk, "
2231 "count(*) AS n "
2232 "FROM v_reports "
2233 "WHERE %Q=substr(date(mtime),1,4) "
2234 "AND mtime < current_timestamp ",
2235 zYear);
2236 if(zUserName&&*zUserName){
2237 blob_appendf(&sql, " AND user=%Q ", zUserName);
2238

Keyboard Shortcuts

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