{"action":"created","issue":{"url":"https://api.github.com/repos/gratipay/finances/issues/21","repository_url":"https://api.github.com/repos/gratipay/finances","labels_url":"https://api.github.com/repos/gratipay/finances/issues/21/labels{/name}","comments_url":"https://api.github.com/repos/gratipay/finances/issues/21/comments","events_url":"https://api.github.com/repos/gratipay/finances/issues/21/events","html_url":"https://github.com/gratipay/finances/issues/21","id":140020470,"number":21,"title":"Restructure Reconciliation Report in Dashboard","user":{"login":"kaguillera","id":1266505,"avatar_url":"https://avatars.githubusercontent.com/u/1266505?v=3","gravatar_id":"","url":"https://api.github.com/users/kaguillera","html_url":"https://github.com/kaguillera","followers_url":"https://api.github.com/users/kaguillera/followers","following_url":"https://api.github.com/users/kaguillera/following{/other_user}","gists_url":"https://api.github.com/users/kaguillera/gists{/gist_id}","starred_url":"https://api.github.com/users/kaguillera/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kaguillera/subscriptions","organizations_url":"https://api.github.com/users/kaguillera/orgs","repos_url":"https://api.github.com/users/kaguillera/repos","events_url":"https://api.github.com/users/kaguillera/events{/privacy}","received_events_url":"https://api.github.com/users/kaguillera/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":5,"created_at":"2016-03-10T21:54:15Z","updated_at":"2016-03-16T15:52:55Z","closed_at":null,"body":"So we are doing this in order to be able to quickly and easily visually check if the information that we are producing in the Financial reports reconcile with the entries in the Gratipay database. Please note that this is a work in progess. I am not submitting a PR as yet because I am getting ~~some~~ trouble with the SQL. It has been a while. \r\nTo the point of this post since I digress...I have come up with the following SQL query and would like verification as to if it is correct as well as give some starting point for some one who would like to try. Please note that it has been a while since I have done (years) SQL and it is not my first language. But again I digress. \r\n\r\n```\r\nSELECT  t1.month,\r\n        balanced_payins,\r\n        balanced_payouts,\r\n        balanced_income,\r\n        paypal_payins,\r\n        paypal_payouts,\r\n        paypal_income,\r\n        balanced_payins + paypal_payins AS Payins,\r\n        balanced_payouts + paypal_payouts AS Payouts,\r\n        balanced_income + paypal_income AS Income\r\nFROM (  SELECT date_trunc('month', \"timestamp\") AS month\r\n            , sum(case when amount > 0 then amount end) as balanced_payins\r\n            , -(sum(case when amount < 0 then amount end)) as balanced_payouts\r\n            , sum(fee) as balanced_income\r\n            , network\r\n       FROM exchanges, exchange_routes\r\n       WHERE exchanges.route = exchange_routes.id\r\n       AND exchange_routes.network = 'balanced-cc'\r\n       GROUP BY month, exchange_routes.network\r\n       ORDER BY month ASC\r\n     ) AS t1,\r\n    (  SELECT date_trunc('month', \"timestamp\") AS month\r\n            , sum(case when amount > 0 then amount end) as paypal_payins\r\n            , -(sum(case when amount < 0 then amount end)) as paypal_payouts\r\n            , sum(fee) as paypal_income\r\n            , network\r\n       FROM exchanges, exchange_routes\r\n       WHERE exchanges.route = exchange_routes.id\r\n       AND exchange_routes.network = 'paypal'\r\n       GROUP BY month, exchange_routes.network\r\n       ORDER BY month ASC\r\n    ) AS t2\r\nWHERE t1.month = t2.month;\r\n```\r\n\r\nThe query above should produce a table of Payins, Payouts, and income broken down by months and network (i.e. routes/payment vendors). It work...up to a point. It does what it is supposed to but the routes are hard coded and I personally don't like this because it makes the Dashboard Reconciliation Report inflexible. If you have any advice as to how to make it more dynamic please share. I will continue to try and work on it"},"comment":{"url":"https://api.github.com/repos/gratipay/finances/issues/comments/197393093","html_url":"https://github.com/gratipay/finances/issues/21#issuecomment-197393093","issue_url":"https://api.github.com/repos/gratipay/finances/issues/21","id":197393093,"user":{"login":"whit537","id":134455,"avatar_url":"https://avatars.githubusercontent.com/u/134455?v=3","gravatar_id":"","url":"https://api.github.com/users/whit537","html_url":"https://github.com/whit537","followers_url":"https://api.github.com/users/whit537/followers","following_url":"https://api.github.com/users/whit537/following{/other_user}","gists_url":"https://api.github.com/users/whit537/gists{/gist_id}","starred_url":"https://api.github.com/users/whit537/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/whit537/subscriptions","organizations_url":"https://api.github.com/users/whit537/orgs","repos_url":"https://api.github.com/users/whit537/repos","events_url":"https://api.github.com/users/whit537/events{/privacy}","received_events_url":"https://api.github.com/users/whit537/received_events","type":"User","site_admin":false},"created_at":"2016-03-16T15:52:55Z","updated_at":"2016-03-16T15:52:55Z","body":"Actually, we want to diff this against our books programmatically ..."}}