[MOD] Show subscription donations and expenses

[Greebo]

This module was created to show operating expenses and more importantly, how much our subscribers are contributing towards those.

The tricky bit was the query to pull the total subscription value. The goal here is to encourage contributions w/o begging by showing how much it costs vs. how much we pull in to cover costs.

Attached: zip file w/ instructions and a screenshot of the module.

Sorry, no warranties, and very little support if any. :)

Enjoy!

[mholtum]

Nice I will giver a shot

[Greebo]

It needs an update. I forgot to filter for inactivity, I was only filtering on expiry date.

Revised PHP attached.

[caimakale]

Is this something that needs to be updated manually everytime somebody donates or does this work with paypal ipn to automatically update each time somebody donates?

[swantonio]

help error

Database error in vBulletin 3.0.6:

Invalid SQL:

select sum(tally * cost) as total

from (

select count(*) as tally,

s.units,

substring_index(substring_index(cost, '"', 4), '"', -1) /

case s.units

when 'Y' then (length * 12)

when 'D' then (365 / length)

when 'W' then (52 / length)

when 'M' then length

end as cost

FROM vb3_subscription s

JOIN vb3_subscriptionlog l ON s.subscriptionid = l.subscriptionid

WHERE FROM_UNIXTIME(l.expirydate) >= NOW()

AND status = 1

GROUP BY l.subscriptionid

) subtotals


mysql error: You have an error in your SQL syntax near 'select count(*) as tally,

s.units,

substring_index(substring_index(cos' at line 4

mysql error number: 1064

Date: Thursday 26th of May 2005 10:27:51 AM
Script: http://www.losmuertos.biz/index.php
Referer: http://www.losmuertos.biz/forum/index.php?

:confused:

[LEAD_WEIGHT]

When I see 1064 it seem to be whitespace problem but then that is me. :p

[Greebo]

What version of MySQL?

[swantonio]

Sui server Linux MySQL versione 3.23.58 :o

[mfarmerhi]

Sweet hack and great idea. Thanks.

[swantonio]

Sui server Linux MySQL versione 3.23.58 :o


:confused: la risposta al mio problema

[Greebo]

Sui server Linux MySQL versione 3.23.58 :o
It may be that MySQL 3.x doesn't support the case statement being used. My own site is using 4.11.

Sorry. :(

[ConqSoft]

3.23.58 is really outdated. You should at least update to 4.0.x

[swantonio]

It may be that MySQL 3.x doesn't support the case statement being used. My own site is using 4.11.

Sorry. :(

peccato :(

[caimakale]

Is this something that needs to be updated manually everytime somebody donates or does this work with paypal ipn to automatically update each time somebody donates?

Can anybody answer this before I attempt to install????

[Greebo]

Is this something that needs to be updated manually everytime somebody donates or does this work with paypal ipn to automatically update each time somebody donates?
Neither.

This reads the active subscriptions for your forum, looks at the amount and duration, and calculates the monthly amount based on that info.

[Greebo]

There's a flaw in the logic for calculating average monthly based on subscriptions based on days or weeks.

The proper case statement in the SQL should be:
case s.units
when 'Y' then (length * 12)
when 'D' then 1 / (365 / length / 12)
when 'W' then 1 / (52 / length / 12)
when 'M' then length
end as cost

[DGTLMIK]

OS: FreeBSD
PHP: v4.3.10
MySQL: v4.0.18

Invalid SQL:
select sum(tally * cost) as total
from (
select count(*) as tally,
s.units,
substring_index(substring_index(cost, '"', 4), '"', -1) /
case s.units
when 'Y' then (length * 12)
when 'D' then (365 / length)
when 'W' then (52 / length)
when 'M' then length
end as cost
FROM subscription s
JOIN subscriptionlog l ON s.subscriptionid = l.subscriptionid
WHERE FROM_UNIXTIME(l.expirydate) >= NOW()
AND status = 1
GROUP BY l.subscriptionid
) subtotals

mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near
'select count(*) as tally,
s.units,
substring_index(subs

[DGTLMIK]

This script uses Subqueries which is only supported by MySQL v4.1+ :(
There is a workaround HERE (http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html) , anyone know how to do this? :confused:

[Greebo]

I don't know if the work around would work in this case because the query depends upon an aggregation function to calculate the sum of the values from the subquery. The subquery returns as many rows as there are subscription types. The main query returns one row.

As an alternative approach, you could use just the subquery and use PHP to iterate through the resultset and calculate the sum instead. I chose not to do this, however, because well - because subqueries work for me and I liked the scalar approach to the query.

[DGTLMIK]

I don't know if the work around would work in this case because the query depends upon an aggregation function to calculate the sum of the values from the subquery. The subquery returns as many rows as there are subscription types. The main query returns one row.

As an alternative approach, you could use just the subquery and use PHP to iterate through the resultset and calculate the sum instead. I chose not to do this, however, because well - because subqueries work for me and I liked the scalar approach to the query.
Ok, you lost me. :confused:

Pardon my noobness but how would I go about doing this? Because the only way for me to use this MOD, which I desperately need, would be to switch to another Web Host that is running at least MySQL v4.1, as my current Host doesn't plan on upgrading anytime soon. :(

[Greebo]

Sorry mate, I've only posted this modification as an FYI/info share - I'm not offering support for it beyond what I've done already.

Good luck!

[DGTLMIK]

Ok, the below attached script has been re-written to NOT use Sub-Queries that are only supported by MySQL v4.1+ and now works perfectly on MySQL v4.0.18 and should work on other versions older than v4.1 as well, but not tested.

New Feature Added: PayPal Fee Subtraction Calculation

Open the below attached 'subscriptiontotal.php' file and look for the line:
$total = $total - (0.06 * $total); and change the decimal to any value you like, for example: 4% = 0.04, so you would change that line to:
$total = $total - (0.04 * $total);

One other change you will need to make is to the 'adv_portal_subscriptiontotal' Template:

Find:
$total
Replace with:
$$total.00
This will round the total dollar amount to the nearest dollar. There is probably a better way of doing this, but this is what worked for me.

Enjoy :)