Jump to content

Where to see total outstanding amount owed to contributors?


SJ77

Recommended Posts

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 🙂

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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):

xUrlOMP.png

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.

Link to comment
Share on other sites

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:

3A32MfB379yioDnzhYhywCzp3HNh8HoFbDNGcVjZ

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...