metadata.yaml 14.5 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
title: Datasette Data³ Demo
about: Data³
about_url: https://www.mediawiki.org/wiki/Data%C2%B3
description_html: |-
  Try it out at <a href="/-/dashboards">/-/dashboards</a>
plugins:
  datasette-dashboards:
    project-metrics:
      title: Data³ - workflow metrics
      description: Metrics about projects, tasks and workflows
      layout:
      - [ custom-chart-display, custom-chart-display]
      - [project-events, column-metrics]
      - [project-tasks-state, project-tasks-state]
      - [task-states,task-states ]
      filters:
        project:
          name: Project
          type: select
          query: select phid as key, name as label from Project
        task:
          name: Task ID
          type: text
        date_start:
          name: Date Start
          type: date
          default: '2021-01-01'
        date_end:
          name: Date End
          type: date
      charts:
        custom-chart-display:
          title: test custom chart
          db: metrics
          query: projects_and_subprojects
          library: custom
          display: test 1 2 3
        project-events:
          title: Number of tasks in the project weekly
          db: metrics
          query:
            select * from (SELECT
                 case state when 'untagged' then -count(distinct task) else count(distinct task) end AS value,
                 state,
                 metric, p.name as project_name,
                 date(ts, 'unixepoch', 'weekday 1') as week,
                 sum(duration) as duration
            FROM
                task_metrics m
            JOIN
                Project p
            ON m.metric=p.phid
            GROUP BY metric, week, state
            ORDER BY week)
            WHERE
               [[ metric in (:project) ]]
               [[ AND week >= date(:date_start) ]]
               [[ AND week <= date(:date_end) ]];
          library: vega
          display:
            width: 500
            height: 300
            transform: [
              {
                stack: value,
                as: ["s1", "s2"],
                offset: zero,
                groupby: ["week"]
              },
              {
                joinaggregate: [{
                  op: sum,
                  field: value,
                  as: sum1
                }],
                groupby: ["week"]
              }
            ]
            encoding: {
              x: { field: week, type: temporal },
            }
            layer: [
              {
                encoding: {
                  y: {
                    field: value,
                    type: quantitative,
                    axis: {"grid": true }
                    # scale: {
                    #   domainMin: 0
                    # }
                  },
                  color: { field: state, type: ordinal }
                  # y2: {
                  #   field: s2,
                  #   type: quantitative,
                  # },
                  # color: {
                  #   field: state,
                  #   type: nominal,
                  # }
                },
                mark: {
                  type: area,
                  clip: true,
                  interpolate: monotone,
                  tooltip: true,
                  opacity: 0.8,
                  line: {
                    color: "darkblue"
                  },
                  color: {
                    x1: 1,
                    y1: 1,
                    x2: 1,
                    y2: 0,
                    gradient: linear,
                    stops: [
                      { offset: 0, color: "black", opacity: 0.5 },
                      { offset: 1, color: "blue", opacity: 0.4},
                    ]
                  }
                }
              },
              {
                encoding: {
                  y: {
                    field: sum1,
                    type: quantitative,
                    title: "Tagged + untagged"
                    #title: "sum of tagged and untagged"
                  },
                  # color: {
                  #   field: sum1, type: quantitative,
                  # }
                },
                mark: {
                  type: bar,
                  strokeWidth: 1,
                  stroke: "black",
                  # clip: true,
                  tooltip: true,
                  opacity: 0.9,
                  width: {band: 1},
                  binSpacing: 0,
                  fillColor: {
                    x1: 1,
                    y1: 1,
                    x2: 1,
                    y2: 0,
                    gradient: linear,
                    stops: [
                      { offset: 1, color: "white", opacity: 0.4},
                      { offset: 0, color: "darkblue",  opacity: 0.7},
                    ]
                  }
                }
              }
            ]
            resolve: {"scale": {"y": "independent"}}
        column-metrics:
          title: Tasks per column
          db: metrics
          query:
            select
              *
            from
              (
                SELECT
                  printf('T%u', c.task) AS task,
                  c.ts AS ts,
                  date(c.ts, 'unixepoch', 'start of month') as month,
                  date(
                    c.ts,
                    'unixepoch',
                    'weekday 1',
                    'start of day',
                    '-7 days'
                  ) as week,
                  p.column_phid as column_phid,
                  p.column_name AS column_name,
                  p.project_phid as project_phid,
                  p.project_name as project_name,
                  p.project_name||':'||p.column_name AS qualified_name,
                  p.status as column_hidden,
                  c.value as value,
                  sum(c.value) over (partition by column_phid order by ts RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as total_tasks
                FROM
                  column_metrics c
                  join columns p on c.column = p.column_phid
              )
            where
              column_hidden = 0
              [[ AND project_phid=:project ]]
              [[ AND week >= date(:date_start) ]]
              [[ AND week <= date(:date_end) ]]
          library: vega
          display:
            transform: [
              stack: value, as: ["s1", "s2"], offset: zero, groupby: ["project_phid", "column_phid"]
            ]
            # {
            #   window: [{
            #     op: sum,
            #     field: value,
            #     as: running_total
            #   }],
            #   frame: [null, 0],
            #   groupby: ["project_phid", "column_phid", "week"],
            #   sort: [{ field: qualified_name }]
            # }]
            mark: { type: bar, tooltip: true, width: { band: 1 }}
            encoding:
              column: { field: week, type: temporal }
              y: { field: s1, type: quantitative}
              y2: { field: s2, type: quantitative}
              y3: { field: total_tasks, type: quantitative }
              row: { field: project_name, type: ordinal }
              x: { field: column_name, type: ordinal }
              color: { field: column_name, type: nominal, scale: {scheme: "category20b"} }
        task-states:
          title: Task Details
          db: metrics
          query:
            SELECT
                c.project_name,
                c.column_name       AS new_column,
                d.column_name       AS old_column,
                date(ts, 'unixepoch', 'start of month') AS month,
                date(ts, 'unixepoch') AS timestamp,
                e.task,
                e.project,
                e.user,
                e.event,
                e.old,
                e.new,
                count(*) as count
            FROM events e
            left JOIN columns c ON e.new = c.column_phid
            left JOIN columns d ON e.old = d.column_phid
            WHERE task=:task
            group by event, timestamp
            ORDER BY
                timestamp;
          library: vega
          display:
            mark: { type: bar, tooltip: true }
            encoding:
              y: { field: count, type: quantitative }
              x: { field: timestamp, type: temporal }
              color: { field: event, type: ordinal }
        project-tasks-state:
          title: Number of tasks in the project weekly
          db: metrics
          query:
            WITH phidtree as (
              SELECT
                phid
              FROM
                Project
              WHERE
                :project IN (phid, parent)
            )
            SELECT
              task,
              state,
              metric,
              p.name,
              w.date as week,
              count(distinct task) as count
            FROM
              task_metrics t
              left join project p on p.phid=t.metric
              left join weeks w on w.date >= date(t.ts, 'unixepoch')
              and w.date <= date(t.ts2, 'unixepoch')
            WHERE
              metric IN (
                SELECT
                  *
                FROM
                  phidtree
              )
              [[ AND week >= date(:date_start) ]]
              [[ AND week <= date(:date_end) ]]
            GROUP BY
            task,metric, week
            ORDER BY
              week
          library: vega
          display:
            mark: { type: area, tooltip: true }
            encoding:
              x: { field: week, type: ordinal }
              y: { field: count, type: quantitative }
              color: { field: name, scale: { scheme: 'category20' }}
Mhurd's avatar
Mhurd committed
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
    task-metrics:
      title: Data³ - task metrics
      description: Metrics about a task
      layout:
        - [notes]
        - [task-details]
        - [projects]
        - [users]
        - [task-days-in-project]
        - [task-days-in-columns]
      filters:
        task_id:
          name: Task ID
          type: quantitative
        project_name:
          name: Project Name
          type: nominal
      charts:
        notes:
          library: markdown
          display: |-
            # To fix:
            > Layout of these text charts will be beautified once templating bits land.
        task-details:
          title: Details
          db: metrics
          query: "select 'Title: ' || task.name || '<br>URL: <a href=\\\"http://127.0.0.1/T' || id || '\\\">http://127.0.0.1/T' || id || '</a><br>Status: ' || json_extract(task.status, '$.name') as info from task join phobjects on phobjects.phid = task.phid  where id = :task_id;"
          library: metric
          display:
            field: info
            prefix:
            suffix:
        projects:
          title: Projects
          db: metrics
          query: SELECT GROUP_CONCAT(name, '<br>') as project from (SELECT phid, name from task_metrics join phobjects on phobjects.phid = metric where metric like '%PHID-PROJ-%' and task = CAST(:task_id as decimal));
          library: metric
          display:
            field: project
            prefix:
            suffix:
        users:
          title: Users
          db: metrics
          query: "SELECT GROUP_CONCAT(name, '<br>') as names from (select 'Author: ' || phobjects.name as 'name' from Task join phobjects on authorPHID = phobjects.phid where id = :task_id union select 'Owner: ' || phobjects.name as 'name' from Task join phobjects on ownerPHID = phobjects.phid where id = :task_id union select 'Closer: ' || phobjects.name as 'name' from Task join phobjects on closerPHID = phobjects.phid where id = :task_id);"
          library: metric
          display:
            field: names
            prefix:
            suffix:
        task-days-in-project:
          title: Days in project
          db: metrics
          query: select ('T' || :task_id || ' has spent ' || printf('%.1f', (sum(duration) / 86400.0)) || ' days on ' || :project_name) as days_in_project from task_metrics join project on metric = phid where state = 'tagged' and name = :project_name and task = CAST(:task_id as decimal);
          library: metric
          display:
            field: days_in_project
            prefix:
            suffix:
        task-days-in-columns:
          title: Days in columns
          db: metrics
          query: select *, next_ts - ts as duration, printf('%.1f', ((next_ts - ts) / 86400.0)) AS duration_days from (select events.*, columns.column_name, columns.project_name, columns.is_default, ifnull(LEAD(ts) OVER(ORDER BY ts), strftime('%s','now', 'localtime')) next_ts from events join columns on new = column_phid where new like '%PHID-PCOL-%' and task = :task_id and event = 'columns' and project_name = :project_name order by ts);
          library: vega
          display:
            mark: { type: bar, tooltip: true }
            encoding:
              x: { field: duration_days, type: quantitative, title: 'Days in column' }
              y: { field: column_name, type: nominal, title: 'Column'  }
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407

databases:
  metrics:
    queries:
      column_events:
        title: Column movement events.
        sql: |-
          "select c.project_name, c.column_name as new_column, d.column_name as old_column, count(task) as count, date(ts, 'unixepoch', 'start of month') as month, datetime(ts, 'unixepoch') as timestamp, task, project, user, event, old, new from events e join columns c on e.new = c.column_phid join columns d on e.old=d.column_phid where event = 'columns' group by old, new, month order by count desc limit 50"
      project_events:
        title: project events
        sql: |-
            SELECT
            date(ts, 'unixepoch', 'start of month') as month,
            count(distinct task) as tasks,
            count(distinct user) as people,
            count(distinct old) as sources,
            new as column
            FROM events
            WHERE event in ('columns', 'projects') and project=:project
            GROUP BY month,column ORDER BY month
      columns_rollup:
        title: aggregations by column and month
        sql: |-
          SELECT
          date(ts, 'unixepoch', 'start of month') as month,

          count(distinct user) as people,
          count(distinct old) as source_count,
          count(*) as rows,
          group_concat(task, ', ') as tasks,
          group_concat(user, ', ') as users,
          group_concat(old, ', ') as sources,
          group_concat(new, ', ') as destinations,
          group_concat(datetime(ts, 'unixepoch'), ', ') as times
          FROM events

          WHERE event in ('columns', 'projects') and :project in (project, old, new)
          GROUP BY month

          order by ts

extra_css_urls:
  - /static/styles.css