Jump to content

Custom monthly income report


Recommended Posts


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!



Link to comment
Share on other sites


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!

Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

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

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated