Jump to content

Invoice Totals

mediademon
 Share

Recommended Posts

mediademon Senior Member

mediademon

Posted
Posted

Hi

 

Here's a simple addition to your admin homepage which adds back the Unpaid Invoice and Overdue Invoice amounts on your admin homepage just like version 3.

 

Just edit your \admin\templates\v4\homepage.tpl (or wherever your admin homepage template resides)

 

And find:

 

<tr><td class="fieldlabel"><a href="invoices.php?status=Unpaid">Unpaid Invoices</a></td><td class="fieldarea"><span class="textred"><b>{$sidebarstats.invoices.unpaid}</b></span></td></tr>
<tr><td class="fieldlabel"><a href="invoices.php?status=Overdue">Overdue Invoices</a></td><td class="fieldarea"><span class="textblack"><b>{$sidebarstats.invoices.overdue}</b></span></td></tr>

Replace with...

 

<!-- START MEDIADEMON INVOICE TOTALS -->
{php}
$sql_query = "select sum(total) as unpaidinvoicetotal from tblinvoices where status='unpaid'";
$sql_result = mysql_query($sql_query);
$sql_array = mysql_fetch_array($sql_result); 
{/php}
<tr><td class="fieldlabel"><a href="invoices.php?status=Unpaid">Unpaid Invoices</a></td><td class="fieldarea"><span 

class="textblack"><b>{$sidebarstats.invoices.unpaid} ({php}echo 

£.$sql_array['unpaidinvoicetotal'];{/php})</b></span></td></tr>

{php}
$sql_query = "SELECT sum(total) as overdueinvoicetotal FROM `tblinvoices` WHERE duedate < now() AND 

status='unpaid'";
$sql_result = mysql_query($sql_query);
$sql_array = mysql_fetch_array($sql_result); 
{/php}
<tr><td class="fieldlabel"><a href="invoices.php?status=Overdue">Overdue Invoices</a></td><td 

class="fieldarea"><span class="textred"><b>{$sidebarstats.invoices.overdue} ({php}echo 

£.$sql_array['overdueinvoicetotal'];{/php})</b></span></td></tr>
<!-- END MEDIADEMON INVOICE TOTALS -->

You will have to change the currency marker to suit your currency as there's no variable to define this that I could find (look for £ in the code above).

 

Other limitations exist if you use multi currencies, which is why it was probably removed in the first place.

 

There is also no permission checking so all your admins will be able to see this addon so be warned!

 

Hope someone finds it as useful as we do...

0
Link to comment
Share on other sites
BAJI26 Senior Member

BAJI26

Posted
Posted
as soon as i change the £ to a $ it stops loading the page ???? as soon as i remove $. it shows everything without a dolor sign but i cant get the actual $ symbol to show up lol

 

Try this:

 

<!-- START MEDIADEMON INVOICE TOTALS -->
{php}
	$sql_query = "select sum(total) as unpaidinvoicetotal from tblinvoices where status='unpaid'";
	$sql_result = mysql_query($sql_query);
	$sql_array = mysql_fetch_array($sql_result); 
{/php}

<tr><td class="fieldlabel"><a href="invoices.php?status=Unpaid">Unpaid Invoices</a></td><td class="fieldarea">
<span class="textblack"><b>{$sidebarstats.invoices.unpaid} ({php}echo "$".$sql_array['unpaidinvoicetotal'];{/php})</b></span></td></tr>
{php}
	$sql_query = "SELECT sum(total) as overdueinvoicetotal FROM `tblinvoices` WHERE duedate < now() AND status='unpaid'";
	$sql_result = mysql_query($sql_query);
	$sql_array = mysql_fetch_array($sql_result); 
{/php}
<tr><td class="fieldlabel"><a href="invoices.php?status=Overdue">Overdue Invoices</a></td><td class="fieldarea">
<span class="textred"><b>{$sidebarstats.invoices.overdue} 
	({php}echo "$".$sql_array['overdueinvoicetotal'];{/php})</b></span></td></tr>
<!-- END MEDIADEMON INVOICE TOTALS -->

0
Link to comment
Share on other sites
ServWise.com Senior Member

ServWise.com

Posted
Posted (edited)

Hi Guys,

 

I wanted to expand on this to properly show the amounts even for multi-currency installs like mine.

 

The following rewrite collects all overdue invoice amounts and calculates their values to the default / base currency based on the current exchange rates set in WHMCS (also no need to manually add the currency prefix/suffix).

 

Here is the code.

 

Replace the following in homepage.tpl

 


<tr><td class="fieldlabel"><a href="invoices.php?status=Unpaid">Unpaid Invoices</a></td><td class="fieldarea"><span class="textred"><b>{$sidebarstats.invoices.unpaid}</b></span></td></tr>
<tr><td class="fieldlabel"><a href="invoices.php?status=Overdue">Overdue Invoices</a></td><td class="fieldarea"><span class="textblack"><b>{$sidebarstats.invoices.overdue}</b></span></td></tr>

 

with

 



{php} 
 		//Get default currency
 		$sql_query = "SELECT  m.prefix, m.suffix FROM tblcurrencies m WHERE m.default=1"; 
       $sql_result = mysql_query($sql_query);     
		$row = mysql_fetch_assoc($sql_result);
	$Prefix = $row["prefix"];
       $Suffix = $row["suffix"];

       //Get overdue invoices, group by currency and calculate default currency amount from exchange rate
       $sql_query = "SELECT sum(i.total) AS curtotal, i.duedate, c.currency, m.prefix, m.suffix, m.rate, m.default FROM tblinvoices i, tblclients c, tblcurrencies m WHERE i.status='unpaid' AND i.userid = c.id AND c.currency = m.id GROUP BY c.currency"; 
       $sql_result = mysql_query($sql_query);         
       while($row = mysql_fetch_array($sql_result)){

           if (strtotime($row["duedate"]) < strtotime("now")){
           	$overduetotal += ($row["curtotal"] / $row["rate"]);
           }
           $overduetotal_final  = $Prefix  . number_format($overduetotal, 2, '.', '') . $Suffix;

      	$dueTotal += ($row["curtotal"] / $row["rate"]);
           $dueTotal_final  = $Prefix  . number_format($dueTotal, 2, '.', '') . $Suffix;
         }
   {/php} 
<tr><td class="fieldlabel"><a href="invoices.php?status=Unpaid">Unpaid Invoices</a></td><td class="fieldarea"> <span class="textblack"><b>{$sidebarstats.invoices.unpaid} [{php}echo $dueTotal_final;{/php}]</b></span></td></tr> 
<tr><td class="fieldlabel"><a href="invoices.php?status=Overdue">Overdue Invoices</a></td><td class="fieldarea"><span class="textblack"><b>{$sidebarstats.invoices.overdue} [{php}echo $overduetotal_final;{/php}]</b></span></td></tr>

Edited by ServWise.com
0
Link to comment
Share on other sites
ServWise.com Senior Member

ServWise.com

Posted
Posted

A few changes to optimise the script removing unneeded fields and calculations moved to SQL.

 


{php} 
   //Get default currency
   $sql_query = "SELECT  m.prefix, m.suffix FROM tblcurrencies m WHERE m.default=1"; 
   $sql_result = mysql_query($sql_query);     
   $row = mysql_fetch_assoc($sql_result);
   $Prefix = $row["prefix"];
   $Suffix = $row["suffix"];

   //Get overdue invoices, group by currency and calculate default currency amount from exchange rate
   $sql_query = "SELECT sum(i.total / m.rate) AS curtotal, i.duedate FROM tblinvoices i, tblclients c, tblcurrencies m WHERE i.status='unpaid' AND i.userid = c.id AND c.currency = m.id GROUP BY c.currency"; 
   $sql_result = mysql_query($sql_query);         
   while($row = mysql_fetch_array($sql_result)){
       if (strtotime($row["duedate"]) < strtotime("now")){
           $overduetotal += $row["curtotal"];
       }
   	$dueTotal += $row["curtotal"];
   }
   $overduetotal_final = $Prefix . number_format($overduetotal, 2, '.', '') . $Suffix;
   $dueTotal_final = $Prefix . number_format($dueTotal, 2, '.', '') . $Suffix;
{/php} 
<tr><td class="fieldlabel"><a href="invoices.php?status=Unpaid">Unpaid Invoices</a></td><td class="fieldarea"> <span class="textblue"><b>{$sidebarstats.invoices.unpaid}<br />{php}echo $dueTotal_final;{/php}</b></span></td></tr> 
<tr><td class="fieldlabel"><a href="invoices.php?status=Overdue">Overdue Invoices</a></td><td class="fieldarea"><span class="textred"><b>{$sidebarstats.invoices.overdue}<br />{php}echo $overduetotal_final;{/php}</b></span></td></tr>

0
Link to comment
Share on other sites
ServWise.com Senior Member

ServWise.com

Posted
Posted

Sorry another bug fix in the above script wich wasnt correctly calculating the overdue total.

 


{php} 
   //Get default currency
   $sql_query = "SELECT  m.prefix, m.suffix FROM tblcurrencies m WHERE m.default=1"; 
   $sql_result = mysql_query($sql_query);     
   $row = mysql_fetch_assoc($sql_result);
   $Prefix = $row["prefix"];
   $Suffix = $row["suffix"];

   //Get overdue invoices, group by currency and calculate default currency amount from exchange rate
   $sql_query = "SELECT i.total / m.rate AS curtotal, i.duedate FROM tblinvoices i, tblclients c, tblcurrencies m WHERE i.status='unpaid' AND i.userid = c.id AND c.currency = m.id"; 
   $sql_result = mysql_query($sql_query);         
   while($row = mysql_fetch_array($sql_result)){
       if (strtotime($row["duedate"]) < strtotime("now")){
           $overduetotal += $row["curtotal"];
       }
   	$dueTotal += $row["curtotal"];
   }
   $overduetotal_final = $Prefix . number_format($overduetotal, 2, '.', '') . $Suffix;
   $dueTotal_final = $Prefix . number_format($dueTotal, 2, '.', '') . $Suffix;
{/php} 
<tr><td class="fieldlabel"><a href="invoices.php?status=Unpaid">Unpaid Invoices</a></td><td class="fieldarea"> <span class="textblue"><b>{$sidebarstats.invoices.unpaid}<br />{php}echo $dueTotal_final;{/php}</b></span></td></tr> 
<tr><td class="fieldlabel"><a href="invoices.php?status=Overdue">Overdue Invoices</a></td><td class="fieldarea"><span class="textred"><b>{$sidebarstats.invoices.overdue}<br />{php}echo $overduetotal_final;{/php}</b></span></td></tr>

 

I really should do more testing of these things.

0
Link to comment
Share on other sites
eugenevdm Senior Member

eugenevdm

Posted
Posted (edited)

I have been battling with this query for a very long time:

select sum(total) as unpaidinvoicetotal from tblinvoices where status='unpaid'

 

I'm kinda convinced that credit needs to be subtracted from there and a join made with tblaccounts in order to be a true reflection of the outstanding amount. The problem is I don't know how to do the join.

 

I thought perhaps something like:

SELECT SUM(total - credit + amountout - amountin - fees) AS unpaidinvoicetotal
FROM tblinvoices
LEFT JOIN tblaccounts ON tblaccounts.invoiceid = tblinvoices.id
WHERE status = 'Unpaid'

 

That doesn't take tax into consideration but otherwise seems to work, however I need a sanity check.

Edited by eugenevdm
0
Link to comment
Share on other sites
ServWise.com Senior Member

ServWise.com

Posted
Posted
I have been battling with this query for a very long time:

select sum(total) as unpaidinvoicetotal from tblinvoices where status='unpaid'

 

I'm kinda convinced that credit needs to be subtracted from there and a join made with tblaccounts in order to be a true reflection of the outstanding amount. The problem is I don't know how to do the join.

 

I thought perhaps something like:

SELECT SUM(total - credit + amountin - amountin - fees) AS unpaidinvoicetotal
FROM tblinvoices
LEFT JOIN tblaccounts ON tblaccounts.invoiceid = tblinvoices.id
WHERE status = 'Unpaid'

 

this seems to work but I need a sanity check.

 

If there was credit then it would already have been deducted plus credit used is still income at the end of the day and you wont have any fees as it is unpaid amounts. have you tried my rewritten version?

0
Link to comment
Share on other sites
eugenevdm Senior Member

eugenevdm

Posted
Posted
...have you tried my rewritten version?

 

Are you referring to this?

$sql_query = "SELECT i.total / m.rate AS curtotal, i.duedate FROM tblinvoices i, tblclients c, tblcurrencies m WHERE i.status='unpaid' AND i.userid = c.id AND c.currency = m.id";

 

If so, what about transactions? We have a large invoice, say $ 100 000, that is going to be paid off over 10 months. There are already two $ 10 000 transactions logged against it so they real outstanding amount is $ 80 000 but that information is only available in tblaccounts. I'm still confused on how to get true reflection of the outstanding amount.

0
Link to comment
Share on other sites
ServWise.com Senior Member

ServWise.com

Posted
Posted
Are you referring to this?

$sql_query = "SELECT i.total / m.rate AS curtotal, i.duedate FROM tblinvoices i, tblclients c, tblcurrencies m WHERE i.status='unpaid' AND i.userid = c.id AND c.currency = m.id";

 

If so, what about transactions? We have a large invoice, say $ 100 000, that is going to be paid off over 10 months. There are already two $ 10 000 transactions logged against it so they real outstanding amount is $ 80 000 but that information is only available in tblaccounts. I'm still confused on how to get true reflection of the outstanding amount.

 

Yeah didn't think of that, we need to confirm that partial transactions are not already removed from the invoice total?

 

If they are not then the following should fix it, otherwise use the previous version.

 

The following script is untested as I dont have any partial payments. It should get any transactions (in and out) for each invoice and remove them from the total. Not bad for one SQL statement. :)

 


{php} 
   //Get default currency
   $sql_query = "SELECT  m.prefix, m.suffix FROM tblcurrencies m WHERE m.default=1"; 
   $sql_result = mysql_query($sql_query);     
   $row = mysql_fetch_assoc($sql_result);
   $Prefix = $row["prefix"];
   $Suffix = $row["suffix"];

   //Get overdue invoices,  calculate default currency amount from exchange rate
   $sql_query = "SELECT i.duedate, i.total / m.rate AS curtotal, (SELECT sum(a.amountin-a.amountout) / m.rate AS transtotal FROM tblaccounts a WHERE a.invoiceid = i.id GROUP BY a.invoiceid) AS Trans FROM tblinvoices i, tblclients c, tblcurrencies m WHERE i.status='unpaid' AND i.userid = c.id AND c.currency = m.id"; 
   $sql_result = mysql_query($sql_query);         
   while($row = mysql_fetch_array($sql_result)){
       if (strtotime($row["duedate"]) < strtotime("now")){
           $overduetotal += ($row["curtotal"] - $row["trans"]);
       }
   	$dueTotal += ($row["curtotal"] - $row["trans"]);
   }
   $overduetotal_final = $Prefix . number_format($overduetotal, 2, '.', '') . $Suffix;
   $dueTotal_final = $Prefix . number_format($dueTotal, 2, '.', '') . $Suffix;
{/php} 
<tr><td class="fieldlabel"><a href="invoices.php?status=Unpaid">Unpaid Invoices</a></td><td class="fieldarea"> <span class="textblue"><b>{$sidebarstats.invoices.unpaid}<br />{php}echo $dueTotal_final;{/php}</b></span></td></tr> 
<tr><td class="fieldlabel"><a href="invoices.php?status=Overdue">Overdue Invoices</a></td><td class="fieldarea"><span class="textred"><b>{$sidebarstats.invoices.overdue}<br />{php}echo $overduetotal_final;{/php}</b></span></td></tr>

0
Link to comment
Share on other sites
ServWise.com Senior Member

ServWise.com

Posted
Posted

It is calculating fine for me so I need to understand if your over dues are special in some way.

 

1. Are you over dues in your base currency (Remember it is converting all secondary values to your base currency based on the currency exchange rate so the total will be different from than just adding up the values from secondary currencies)

 

2. Do you have part payments on these over dues.

 

Does the total match the over dues if the part payments had not be made? This would suggest the part payment part is not being calculated.

 

Where could 103 be coming from?

 

If there is anything else you can tell me that would help work out why it does not work for you it would help me understand.

0
Link to comment
Share on other sites
Bartucxp Senior Member

Bartucxp

Posted
Posted

I've found the reason why it didn't work correct.

The date is 14.06.2009 and I have an invoice which due date is 14.06.2009. It is not overdue yet because this is the last day for his payment, so it will be overdue tomorrow. But your code thinks this invoice is overdue, too.

 

There is one day overdue calculate mistake on your code.

0
Link to comment
Share on other sites
Bartucxp Senior Member

Bartucxp

Posted
Posted 

{php} 
   //Get default currency
   $sql_query = "SELECT  m.prefix, m.suffix FROM tblcurrencies m WHERE m.default=1"; 
   $sql_result = mysql_query($sql_query);     
   $row = mysql_fetch_assoc($sql_result);
   $Prefix = $row["prefix"];
   $Suffix = $row["suffix"];

   //Get overdue invoices,  calculate default currency amount from exchange rate
   $sql_query = "SELECT i.duedate, i.total / m.rate AS curtotal, (SELECT sum(a.amountin-a.amountout) / m.rate AS transtotal FROM tblaccounts a WHERE a.invoiceid = i.id GROUP BY a.invoiceid) AS Trans FROM tblinvoices i, tblclients c, tblcurrencies m WHERE i.status='unpaid' AND i.userid = c.id AND c.currency = m.id"; 
   $sql_result = mysql_query($sql_query);         
   while($row = mysql_fetch_array($sql_result)){
       if (strtotime($row["duedate"]) < strtotime("now")-86400){
           $overduetotal += ($row["curtotal"] - $row["trans"]);
       }
       $dueTotal += ($row["curtotal"] - $row["trans"]);
   }
   $overduetotal_final = $Prefix . number_format($overduetotal, 2, '.', '') . $Suffix;
   $dueTotal_final = $Prefix . number_format($dueTotal, 2, '.', '') . $Suffix;
{/php} 
<tr><td class="fieldlabel"><a href="invoices.php?status=Unpaid">Unpaid Invoices</a></td><td class="fieldarea"> <span class="textblue"><b>{$sidebarstats.invoices.unpaid}<br />{php}echo $dueTotal_final;{/php}</b></span></td></tr>
<tr><td class="fieldlabel"><a href="invoices.php?status=Overdue">Overdue Invoices</a></td><td class="fieldarea"><span class="textred"><b>{$sidebarstats.invoices.overdue}<br />{php}echo $overduetotal_final;{/php}</b></span></td></tr>

 

Works great now.

Edited:

strtotime("now")-86400

0
Link to comment
Share on other sites
  • 2 weeks later...
Frankc Senior Member

Frankc

Posted
Posted (edited)

Brilliant thank you. (don't know why Matt removed such very useful piece of information from the homepage. (I can in fact not get it anywhere)

 

I however still prefer the original version and changed the code a bit for that.

 

Just change the following link in admin/templates/original/sidebar.tpl

 

<a href="invoices.php?status=Unpaid">Unpaid Invoices</a>: {$sidebarstats.invoices.unpaid}<br />
<a href="invoices.php?status=Overdue">Overdue Invoices</a>: {$sidebarstats.invoices.overdue}<br />

 

With

 

{php} 
   //Get default currency
   $sql_query = "SELECT  m.prefix, m.suffix FROM tblcurrencies m WHERE m.default=1"; 
   $sql_result = mysql_query($sql_query);     
   $row = mysql_fetch_assoc($sql_result);
   $Prefix = $row["prefix"];
   $Suffix = $row["suffix"];

   //Get overdue invoices, group by currency and calculate default currency amount from exchange rate
   $sql_query = "SELECT i.total / m.rate AS curtotal, i.duedate FROM tblinvoices i, tblclients c, tblcurrencies m WHERE i.status='unpaid' AND i.userid = c.id AND c.currency = m.id"; 
   $sql_result = mysql_query($sql_query);         
   while($row = mysql_fetch_array($sql_result)){
       if (strtotime($row["duedate"]) < strtotime("now")){
           $overduetotal += $row["curtotal"];
       }
       $dueTotal += $row["curtotal"];
   }
   $overduetotal_final = $Prefix . number_format($overduetotal, 2, '.', '');
   $dueTotal_final = $Prefix . number_format($dueTotal, 2, '.', '');
{/php} 
<a href="invoices.php?status=Unpaid">Unpaid Invoices</a>: {$sidebarstats.invoices.unpaid} {php}echo $dueTotal_final;{/php} <br />
<a href="invoices.php?status=Overdue">Overdue Invoices</a>: {$sidebarstats.invoices.overdue} {php}echo $overduetotal_final;{/php}  <br />

 

I prefer not to display the suffix (such as USD) but if you want the suffix just use the following instead.

 

{php} 
   //Get default currency
   $sql_query = "SELECT  m.prefix, m.suffix FROM tblcurrencies m WHERE m.default=1"; 
   $sql_result = mysql_query($sql_query);     
   $row = mysql_fetch_assoc($sql_result);
   $Prefix = $row["prefix"];
   $Suffix = $row["suffix"];

   //Get overdue invoices, group by currency and calculate default currency amount from exchange rate
   $sql_query = "SELECT i.total / m.rate AS curtotal, i.duedate FROM tblinvoices i, tblclients c, tblcurrencies m WHERE i.status='unpaid' AND i.userid = c.id AND c.currency = m.id"; 
   $sql_result = mysql_query($sql_query);         
   while($row = mysql_fetch_array($sql_result)){
       if (strtotime($row["duedate"]) < strtotime("now")){
           $overduetotal += $row["curtotal"];
       }
       $dueTotal += $row["curtotal"];
   }
   $overduetotal_final = $Prefix . number_format($overduetotal, 2, '.', '') . $Suffix;
   $dueTotal_final = $Prefix . number_format($dueTotal, 2, '.', '') . $Suffix;
{/php} 
<a href="invoices.php?status=Unpaid">Unpaid Invoices</a>: {$sidebarstats.invoices.unpaid} {php}echo $dueTotal_final;{/php} <br />
<a href="invoices.php?status=Overdue">Overdue Invoices</a>: {$sidebarstats.invoices.overdue} {php}echo $overduetotal_final;{/php}  <br />

Edited by Frankc
0
Link to comment
Share on other sites
  • 2 months later...
  • 2 weeks later...
tldagent Member

tldagent

Posted
Posted

I have a question related to this and would like to know how to solve it. When I'm looking at paid invoices in an account, the total show 0.00 after it's been paid on some and shows the amount on others. I'd like to see the total of the invoice whether paid or not without having to open each invoice.

 

Does this solution, while not sure which one is the correct one to implement, does it solve this or is there something else I'm missing?

 

I've posted this question quite some time ago here: http://forum.whmcs.com/showthread.php?t=22979

0
Link to comment
Share on other sites
  • 2 years later...

Join the conversation

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

Guest
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.

×
 Share
Go to topic listing

  • 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

  I accept