serveria Posted June 6, 2018 Share Posted June 6, 2018 Hi, We need to create a custom monthly income report for our bookkeeping. I checked the standard reports and also the mysql database directly they both use an "amountin" table which seems to contain an entire sum paid by the customer (also prepaid quarterly/annual etc orders). Is there any way to grab just the amount the customer pays for the current month? So, say John Doe has a server subscription worth $99/month and in June he prepays for a year in advance so the "amountin" value is $1188. What we need is to get the amount he pays for June i.e. $99. Also will need to copy this amount to July, August etc till the end of his prepayment term. Is there any way to achieve this using reports? Thanks in advance, guys! BR, Paul 0 Quote Link to comment Share on other sites More sharing options...
WHMCS ChrisD Posted June 7, 2018 Share Posted June 7, 2018 Hey Paul, off the top of my head I don't know an easy way to do this, when your client pays are they paying annually or adding credit to their account to pay over the year? 0 Quote Link to comment Share on other sites More sharing options...
brian! Posted June 7, 2018 Share Posted June 7, 2018 Paul, i'd go along with Chris on this and say it won't be easy... you could be looking at multiple joins to get to the point of being able to calculate monthly totals - e.g tblaccounts will give you the invoice associated with the payment, then tblinvoiceitems will give you the service ID and whether it's a domain or service; then tblhosting or tbldomains will give you the billingcycle or registration length and that will give you a figure of what to divide the amountin by, e.g 12/36/120 etc... the more you look at it, the further from "easy" it seems! 0 Quote Link to comment Share on other sites More sharing options...
serveria Posted June 7, 2018 Author Share Posted June 7, 2018 Thanks guys, I thought so it's not going to be an easy task... Chris, the customers are paying invoices instantly, not adding funds to their accounts. Brian, that was exactly my idea: take the amountin and compare to the monthly server price to calculate the correct amount for any given month. I wonder if there's any example of a whmcs report using joins available? 0 Quote Link to comment Share on other sites More sharing options...
brian! Posted June 8, 2018 Share Posted June 8, 2018 one example would be in the thread below... personally, I might be tempted to do it outside of WHMCS reports, but that depends on how good your SQL writing is and how you need to display the information. 0 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.