{"action":"created","issue":{"url":"https://api.github.com/repos/swcarpentry/amy/issues/260","labels_url":"https://api.github.com/repos/swcarpentry/amy/issues/260/labels{/name}","comments_url":"https://api.github.com/repos/swcarpentry/amy/issues/260/comments","events_url":"https://api.github.com/repos/swcarpentry/amy/issues/260/events","html_url":"https://github.com/swcarpentry/amy/issues/260","id":73958609,"number":260,"title":"Generate reports","user":{"login":"gvwilson","id":911566,"avatar_url":"https://avatars.githubusercontent.com/u/911566?v=3","gravatar_id":"","url":"https://api.github.com/users/gvwilson","html_url":"https://github.com/gvwilson","followers_url":"https://api.github.com/users/gvwilson/followers","following_url":"https://api.github.com/users/gvwilson/following{/other_user}","gists_url":"https://api.github.com/users/gvwilson/gists{/gist_id}","starred_url":"https://api.github.com/users/gvwilson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gvwilson/subscriptions","organizations_url":"https://api.github.com/users/gvwilson/orgs","repos_url":"https://api.github.com/users/gvwilson/repos","events_url":"https://api.github.com/users/gvwilson/events{/privacy}","received_events_url":"https://api.github.com/users/gvwilson/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/swcarpentry/amy/labels/enhancement","name":"enhancement","color":"84b6eb"},{"url":"https://api.github.com/repos/swcarpentry/amy/labels/essential","name":"essential","color":"0052cc"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":11,"created_at":"2015-05-07T11:57:09Z","updated_at":"2015-05-07T13:59:57Z","closed_at":null,"body":"We need to generate a multitude of reports, some to include in talks and grant proposals, others to help us sync with the main web site:\r\n\r\n- [ ] people whose training is complete but don't yet have badges\r\n- [ ] people whose training is NULL (neither complete or incomplete)\r\n- [ ] workshops without instructors\r\n- [ ] export workshop payment status\r\n- [ ] workshops for which we don't have Eventbrite\r\n- [ ] workshops for which we don't have enrolment\r\n- [ ] export workshop slugs and URLs for comparison with website\r\n- [ ] export list of who taught/helped where and when\r\n- [ ] cumulative enrolment by date\r\n- [ ] cumulative instructors by date\r\n- [ ] cumulative workshops by date\r\n- [ ] list instructors by frequency of teaching (including 0)\r\n- [ ] list SCF members\r\n\r\nWe also need to update these to match what's currently in the `admin` repo (the format has changed to include names):\r\n\r\n- [ ] export YAML for badges\r\n- [ ] export YAML for airports"},"comment":{"url":"https://api.github.com/repos/swcarpentry/amy/issues/comments/99877713","html_url":"https://github.com/swcarpentry/amy/issues/260#issuecomment-99877713","issue_url":"https://api.github.com/repos/swcarpentry/amy/issues/260","id":99877713,"user":{"login":"gvwilson","id":911566,"avatar_url":"https://avatars.githubusercontent.com/u/911566?v=3","gravatar_id":"","url":"https://api.github.com/users/gvwilson","html_url":"https://github.com/gvwilson","followers_url":"https://api.github.com/users/gvwilson/followers","following_url":"https://api.github.com/users/gvwilson/following{/other_user}","gists_url":"https://api.github.com/users/gvwilson/gists{/gist_id}","starred_url":"https://api.github.com/users/gvwilson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gvwilson/subscriptions","organizations_url":"https://api.github.com/users/gvwilson/orgs","repos_url":"https://api.github.com/users/gvwilson/repos","events_url":"https://api.github.com/users/gvwilson/events{/privacy}","received_events_url":"https://api.github.com/users/gvwilson/received_events","type":"User","site_admin":false},"created_at":"2015-05-07T13:59:57Z","updated_at":"2015-05-07T13:59:57Z","body":"Which instructors' locations aren't recorded?\r\n\r\n~~~\r\nselect person.personal || ' ' || person.family || ' <' || person.email || '>'                                                                         \r\nfrom person join awards                                                                                                                               \r\non person.person=awards.person                                                                                                                        \r\nwhere awards.badge='instructor'                                                                                                                       \r\nand (                                                                                                                                                 \r\n  (person.person not in (select person from facts))                                                                                                   \r\n  or                                                                                                                                                  \r\n  (person.person in (select person from facts where facts.airport is null))                                                                           \r\n);\r\n~~~"}}