Where to see total outstanding amount owed to contributors?

[SJ77]

Hi

When contributors are allowed to earn credit by selling things in the downloads app it would be VERY handy for the site owner to be able to have a running total (site wide) balance owed at any given point.

When you have MANY contributors and not all of them immediately withdrawal funds, it's a little worrisome that an incredible balance could be growing.

I would love to see this summarized somewhere. Can someone tell me where to look?  If it's not available this would be a great 3rd party mod for someone to build.

Thank you 🙂

[SJ77]

Is this really not possible?

What was the plan here? How does IPS do it for their contributors?

It's a really a bad feeling to be in the dark about money owed.

@Adriano Faria I know you have several plugins of this nature... is this a feature (or export column) in one of your things?

[Adriano Faria]

Wouldn’t the EXPORT PAYOUTS plugin do the job? You have it, AFAIK. 

[SJ77]

3 minutes ago, Adriano Faria said:

Wouldn’t the EXPORT PAYOUTS plugin do the job? You have it, AFAIK. 

As far as I know that shows what has already been paid out not what is currently sitting in their account as credit.

[Adriano Faria]

You want to see the credit every contributor has on their account?

[Adriano Faria]

Ok, got it. Making a quick plugin. I will send you a PM today later then will submit tomorrow if it is ok.

------------------------------------

@SJ77

Something like that?

 

------------------------------------

PM sent.

[Adriano Faria]

Ehrrrrr.... Just testing here, I can see that when someone requests payout, Commerce set the account credit to 0 (zero) instead of NULL, so it shows 0 in the listing. Worst, when the payout is processed, still shows 0 (zero):

So there is absolutely no sense to query the whole core_members table, json_decode the cm_credits column to see if it is 0 or NULL.

Lei it be... doesn't worth it.

[SJ77]

I’m confused. Can’t you just sum account credit? 

This is exactly what we need to know. How much outstanding account credit remains total site . 

[SJ77]

Zero is fine. End user (me) can Export to excel (csv) and filter to uncheck 0

or in your query use “where account_credit > 0”

[Adriano Faria]

23 minutes ago, SJ77 said:

Can’t you just sum account credit? 

 

17 minutes ago, SJ77 said:

Zero is fine. End user (me) can Export to excel and filter to uncheck 0

or in your query use “where account_credit > 0”

Seriously SJ77, you REALLY think I don’t know know how to do such simple query?!? 😂

core_members.cm_credits isn’t a “number” format column. They store it in a JSON format to store multi currencies in a single column, which means you need to decode it’s value to get its data. You can’t query it directly! Go to your database and take a look in that column.

As I said, I could if IPS set the column to NULL when someone requests payouts but they set it to 0 (zero) instead! So all I can do on my Table/DB is check to see if the column is = ‘’. But setting it to zero will return something like {“USD”:”0”} and that as you might know is different than null or ‘’. 

Here an example of JSON value:

Same happens on members location and that’s why IPS doesn’t make a search by location, for example. 

[Makoto]

If you're using at least MySQL 5.7 or MariaDB 10.2 you can query json fields directly, but does this really matter?

Any client who has withdrawn money at some point or another is a confirmed contributor and it's still likely useful to display them in that last even if their balance is zero.

[SJ77]

Adriano, I know you can do great queries. I was mostly asking if there is a very simple work around. Not in any way suggesting you can't do it.  Sometimes complicated problems have simple solutions. Thank you for trying, that is very kind of you.

8 hours ago, Makoto said:

If you're using at least MySQL 5.7 or MariaDB 10.2 you can query json fields directly, but does this really matter?

Any client who has withdrawn money at some point or another is a confirmed contributor and it's still likely useful to display them in that last even if their balance is zero.

This, I don't mind seeing output for my entire contributor group. I just need export to excel (CSV file) so I can figure out if I have enough reserves to pay money that I owe.

As it is now, I have no idea how much of a buffer I need to have on hand to payout contributors. It's rather stressful if I am being honest.

[Stephen Bruce]

Has this been solved? I could do with this too

[SJ77]

2 hours ago, Stephen Bruce said:

Has this been solved? I could do with this too

No solution that I’m aware of

[Mark]

For a straightforward total of all account credit:

SELECT SUM( CAST( JSON_EXTRACT( cm_credits,'$.USD' ) AS DECIMAL(20,2) ) ) FROM core_members WHERE cm_credits IS NOT NULL

To count only positive amounts (because people can have negative balances):

SELECT SUM( GREATEST( CAST( JSON_EXTRACT( cm_credits,'$.USD' ) AS DECIMAL(20,2) ), 0 ) ) FROM core_members WHERE cm_credits IS NOT NULL;

Requires MySQL 5.7 or higher. Replace "USD" with the currency code you're using.

[SJ77]

Thanks @Mark This is a GREAT start. I think I can get something working here.

[SJ77]

figured it out. Have to use json unquote

SELECT SUM( CAST( JSON_UNQUOTE(JSON_EXTRACT( cm_credits,'$.USD' )) AS DECIMAL(20,2) ) ) FROM core_members WHERE cm_credits IS NOT NULL

 

[SJ77]

TOTAL OUTSTANDING CREDIT
Got two final queries working that shows amount site owner owes to their members. (the amount of total site credit)
It's here if others want to use it in their sql toolbox.

 

Site total credit:

SELECT SUM( GREATEST( CAST( JSON_UNQUOTE(JSON_EXTRACT( cm_credits,'$.USD' )) AS DECIMAL(20,2) ), 0 ) ) FROM core_members WHERE cm_credits IS NOT NULL;

 

Site total credit by member:

SELECT NAME, CAST( JSON_UNQUOTE(JSON_EXTRACT( cm_credits,'$.USD' )) AS DECIMAL(20,2) ) AS OWED FROM core_members WHERE cm_credits IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT( cm_credits,'$.USD' )) > 0 order by OWED DESC