Restructure Reconciliation Report in Dashboard

[kaguillera]

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.
To 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.

SELECT  t1.month,
        balanced_payins,
        balanced_payouts,
        balanced_income,
        paypal_payins,
        paypal_payouts,
        paypal_income,
        balanced_payins + paypal_payins AS Payins,
        balanced_payouts + paypal_payouts AS Payouts,
        balanced_income + paypal_income AS Income
FROM (  SELECT date_trunc('month', "timestamp") AS month
            , sum(case when amount > 0 then amount end) as balanced_payins
            , -(sum(case when amount < 0 then amount end)) as balanced_payouts
            , sum(fee) as balanced_income
            , network
       FROM exchanges, exchange_routes
       WHERE exchanges.route = exchange_routes.id
       AND exchange_routes.network = 'balanced-cc'
       GROUP BY month, exchange_routes.network
       ORDER BY month ASC
     ) AS t1,
    (  SELECT date_trunc('month', "timestamp") AS month
            , sum(case when amount > 0 then amount end) as paypal_payins
            , -(sum(case when amount < 0 then amount end)) as paypal_payouts
            , sum(fee) as paypal_income
            , network
       FROM exchanges, exchange_routes
       WHERE exchanges.route = exchange_routes.id
       AND exchange_routes.network = 'paypal'
       GROUP BY month, exchange_routes.network
       ORDER BY month ASC
    ) AS t2
WHERE t1.month = t2.month;

The 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

[kaguillera]

SELECT month,
(CASE WHEN network = 'balanced-cc' THEN payins ELSE NULL END) AS "Balanced Payins",
(CASE WHEN network = 'paypal' THEN payins ELSE NULL END) AS "Balanced Payins",
(CASE WHEN network = 'balanced-cc' THEN payouts ELSE NULL END) AS "Balanced Payouts", 
(CASE WHEN network = 'paypal' THEN payouts ELSE NULL END) AS "Balanced Payouts",                                                                                          network
FROM (  SELECT date_trunc('month', "timestamp") AS month
, sum(case when amount > 0 then amount end) as payins 
, -(sum(case when amount < 0 then amount end)) as payouts 
, sum(fee) as income
, network 
FROM exchanges, exchange_routes 
WHERE exchanges.route = exchange_routes.id  
GROUP BY month, exchange_routes.network  
ORDER BY month ASC  
) 
AS totals;

[chadwhitacre]

Per IRL convo, here's a hack to get the list of payment networks dynamically:

select enumlabel from pg_enum where enumtypid = 
 (select typelem from pg_type where typname='_payment_net');

[kaguillera]

So I was wondering about the structure of the table that we want to get. It would narrow how to get the data a little. Below are the options that I was considering.

Month	Network1 payins	Network1 payouts	Network2 payins	Network2 payouts	Total payins	Total Payouts
2016-02-02	$5.00	$4.000	$1.00	$5.00	$4.000	$1.00

OR

Month	Network	Payins	Payouts	Income
2016-02-02	Network1	$5.00	$4.000	$1.00
2016-02-02	Network2	$6.00	$7.000	$1.50

[chadwhitacre]

@kaguillera Let's go for:

2012-06

Network	Succeeded	Failed	Pending	Total	Fee Income
balanced	250.00	150.00	0.00	400.00	23.00
paypal	150.00	50.00	5.00	205.00	15.00

[chadwhitacre]

Actually, we want to diff this against our books programmatically ...

[chadwhitacre]

Per IRL convo, let's aim for CSVs at endpoints like this:

https://gratipay.com/dashboard/reconciliation/2012-06.csv

With columns as above: network, succeeded, failed, pending, total, fee_income.

[chadwhitacre]

"What about the payout part?"—@kaguillera

[chadwhitacre]

Yeah, we'll need that. Let's focus on what we need for 2012-06 and 2012-07. We can evolve the export as we work through the rest of the months.

[kaguillera]

SELECT  suc_table.month,
        suc_payins,
        suc_payouts,
        suc_income,
        pend_payins,
        pend_payouts,
        pend_income,
        fail_payins,
        fail_payouts,
        fail_income,
        suc_payins + pend_payins AS Total_Payins,
        suc_payouts + pend_payouts AS Total_Payouts,
        suc_income + pend_income AS Total_Income
FROM (  SELECT date_trunc('month', "timestamp") AS month
        , COALESCE( sum(case when amount > 0 then amount end), 0) as suc_payins
        , COALESCE( -(sum(case when amount < 0 then amount end)), 0) as suc_payouts
        , COALESCE( sum(fee), 0 )  as suc_income
        , status
        , network
    FROM exchanges, exchange_routes
    WHERE exchanges.route = exchange_routes.id
    AND status = 'succeeded'
    GROUP BY month, exchange_routes.network, status 
    ORDER BY month ASC
    ) AS suc_table,
       (  SELECT date_trunc('month', "timestamp") AS month
        , COALESCE( sum(case when amount > 0 then amount end), 0) as pend_payins
        , COALESCE( -(sum(case when amount < 0 then amount end)), 0) as pend_payouts
        , COALESCE( sum(fee), 0 )  as pend_income
        , status
        , network
    FROM exchanges, exchange_routes
    WHERE exchanges.route = exchange_routes.id
    AND status = 'pending'
    GROUP BY month, exchange_routes.network, status
    ORDER BY month ASC
    ) AS pend_table,
       (  SELECT date_trunc('month', "timestamp") AS month
        , COALESCE( sum(case when amount > 0 then amount end), 0) as fail_payins
        , COALESCE( -(sum(case when amount < 0 then amount end)), 0) as fail_payouts
        , COALESCE( sum(fee), 0 )  as fail_income
        , status
        , network
    FROM exchanges, exchange_routes
    WHERE exchanges.route = exchange_routes.id
    AND status = 'failed'
    GROUP BY month, exchange_routes.network, status
    ORDER BY month ASC
    ) AS fail_table
WHERE suc_table.month = fail_table.month and suc_table.month = pend_table.month;

[kaguillera]

produces:

Month	suc_payins	suc_payouts	suc_income	pend_payins	pend_payouts	pend_income	fail_payins	fail_payouts	fail_income	total_payins	total_payouts	total_income
2015-06-01	841.47	9789.95	212.64	7542.84	0	150.87	522.92	0	10.46	8384.31	9789.95	363.51
2015-06-01	841.47	9789.95	212.64	4090.21	0	81.81	522.92	0	10.46	4931.68	9789.95	294.45
2015-06-01	2873.20	7510.88	207.68	7542.84	0	150.87	522.92	0	10.46	10416.04	7510.88	358.55
2015-06-01	2873.20	7510.88	207.68	4090.21	0	81.81	522.92	0	10.46	6963.41	7510.88	289.49

Not exactly what we are looking for?!

[kaguillera]

Since that approach seems to be beyond me at this time I am going to use following query

SELECT date_trunc('month', "timestamp") AS month
                 , COALESCE(sum(case when amount > 0 then amount end), 0) as payins
                 , COALESCE(-(sum(case when amount < 0 then amount end)), 0) as payouts
                 , sum(fee) as income
                 , status
                 , network
FROM exchanges, exchange_routes
WHERE exchanges.route = exchange_routes.id
GROUP BY month, network, status
ORDER BY month ASC;

which produces the following results

month	payins	payouts	income	status	network
2015-03-01	0	-2129.94	42.60	succeeded	balanced-cc
2015-03-01	647.53	0	12.95	failed	balanced-cc
2015-04-01	3494.54	0	69.90	failed	balanced-cc
2015-04-01	0	-2244.59	44.89	succeeded	paypal
2015-04-01	0	-3041.29	60.82	succeeded	balanced-cc
2015-04-01	2847.20	0	56.95	failed	paypal
2015-05-01	0	-3379.97	67.60	succeeded	paypal
2015-05-01	5045.79	0	100.92	failed	paypal

The programmatically produce the table show in (#21 (comment))

so here we go...

[chadwhitacre]

@kaguillera Does gratipay/gratipay.com#3975 close this?

[kaguillera]

Yes. !m @whit537

[chadwhitacre]

!m @kaguillera