Due and overdue invoices widget

[bear]

Finally completed the upgrade on a very old installation, and there's something missing from the dashboard view I'd like to get back if possible; a due and overdue invoices list. That allowed me to see at a glance what is overdue and potentially in need of reaching out to the client. As it is now, the only reference to overdue invoices is a count all the way at the top of the page, forcing me to hit the link to review With the inherent slowness of the interface these days, I need something I can just see from the dashboard. 

Any hints on how to achieve that useful bit of info (instead of some of the more fluffy and useless ones that exist in that interface)? 

[brian!]

10 hours ago, bear said:

Any hints on how to achieve that useful bit of info (instead of some of the more fluffy and useless ones that exist in that interface)? 

how did you do it in the old installation? if a widget, does that widget still work in v7.6 ?? it might be disabled by default, and so you'd have to go to setup -> staff management -> administrator roles -> *role * and then check the widgets checkboxes for old widgets not using the current code design (e.g v5 and v6)...

at worst, an old widget might blank out the dashboard, but it may work... or will at least give you a widget that needs to be upgraded.

certainly, the old v6 widget that I helped write about forthcoming invoices still works... I must get around to tidying up the layout for v7... and I guess that we now have a good 3-month period where they'll be no new release distractions from WHMCS.

[bear]

The old widget still kind of works (sort of), but it's using mysql calls and methods, rather than PDO or capsule and so on. I'm convinced it won't continue working long, and it's a mess overall in the new interface (uses tables for layout). When I asked about this, I'd forgotten this was even something that wasn't included with WHMCS. It may well be yours. 😉

I need to learn this anyway, since I now no longer have an option not to. I did manage to fiddle with removing/changing things in the secondary sidebar links in the client area without blanking out the page (eventually), so some of it's sinking in, I suspect. 

[brian!]

3 minutes ago, bear said:

I'm convinced it won't continue working long

short-term, the fact it's using mysql won't really matter - all the default reports, when not using API, are still using mysql queries and I can't see that changing before v8 at the earliest... more of an issue will be that it's not using the new widget code.

6 minutes ago, bear said:

When I asked about this, I'd forgotten this was even something that wasn't included with WHMCS. It may well be yours. 😉

oh no!

6 minutes ago, bear said:

I need to learn this anyway, since I now no longer have an option not to. I did manage to fiddle with removing/changing things in the secondary sidebar links in the client area without blanking out the page (eventually), so some of it's sinking in, I suspect.

feel free to post the existing widget code if you like - i've seen a number of variations of invoices widgets, so i'm unsure which one you're seeing.

 

[bear]

Here goes. 

	<?php
	if (!defined("WHMCS"))
    die("This file cannot be accessed directly");
	function widget_open_invoices($vars) {
    global $_ADMINLANG,$currency;
	    $title = $_ADMINLANG['home']['openinvoices'];
	    if (!function_exists("getGatewaysArray")) require(ROOTDIR."/includes/gatewayfunctions.php");
    $gatewaysarray = getGatewaysArray();
	    $content = '<table class="table table-condensed">
<tr style="background-color:#efefef;font-weight:bold;text-align:center"><td>'.$_ADMINLANG['fields']['invoicenum'].'</td><td>'.$_ADMINLANG['fields']['clientname'].'</td><td>'.$_ADMINLANG['fields']['invoicedate'].'</td><td>'.$_ADMINLANG['fields']['duedate'].'</td><td>'.$_ADMINLANG['fields']['totaldue'].'</td><td>'.$_ADMINLANG['fields']['paymentmethod'].'</td><td width="20"></td></tr>
';
	    $id = '';
    $query = "SELECT tblinvoices.*,tblinvoices.total-COALESCE((SELECT SUM(amountin) FROM tblaccounts WHERE tblaccounts.invoiceid=tblinvoices.id),0) AS invoicebalance,tblclients.firstname,tblclients.lastname FROM tblinvoices INNER JOIN tblclients ON tblclients.id=tblinvoices.userid WHERE tblinvoices.status='Unpaid' ORDER BY duedate,date ASC LIMIT 0,5";
    $result = full_query($query);
    while ($data = mysql_fetch_array($result)) {
        $id = $data["id"];
        $invoicenum = $data["invoicenum"];
        $userid = $data["userid"];
        $firstname = $data["firstname"];
        $lastname = $data["lastname"];
        $date = $data["date"];
        $duedate = $data["duedate"];
        $total = $data["total"];
        $invoicebalance = $data["invoicebalance"];
        $paymentmethod = $data["paymentmethod"];
        $paymentmethod = $gatewaysarray[$paymentmethod];
        $date = fromMySQLDate($date);
        $duedate = fromMySQLDate($duedate);
        $currency = getCurrency($userid);
        if (!$invoicenum) $invoicenum = $id;
        $content .= '<tr bgcolor="#ffffff" style="text-align:center;"><td><a href="invoices.php?action=edit&id='.$id.'">'.$invoicenum.'</a></td><td>'.$firstname.' '.$lastname.'</td><td>'.$date.'</td><td>'.$duedate.'</td><td>'.formatCurrency($total).'</td><td>'.$paymentmethod.'</td><td><a href="invoices.php?action=edit&id='.$id.'"><img src="images/edit.gif" border="0" /></a></td></tr>';
    }
    if (!$id) $content .= '<tr bgcolor="#ffffff" style="text-align:center;"><td colspan="7">'.$_ADMINLANG['global']['norecordsfound'].'</td></tr>';
	    $content .= '</table>
<div class="widget-footer">
    <a href="invoices.php?status=Unpaid" class="btn btn-info btn-sm">'.$_ADMINLANG['home']['viewall'].' &raquo;</a>
</div>';
	    return array('title'=>$title,'content'=>$content);
	}
add_hook("AdminHomeWidgets",1,"widget_open_invoices");

[brian!]

pretty sure that's not one mine... might be a default v5 widget.

anyway, the thread below should help... solution is a little more bloated than I think it really needs to be, but it will get the job done for you until I get a chance to tidy it up. 😀

 

[bear]

I actually spotted that one earlier and tried it, and not that first one nor the edited one later in that thread even show up in the list (admin perms), much less work. Not sure why. No errors, just invisible. 

[brian!]

1 minute ago, bear said:

I actually spotted that one earlier and tried it, and not that first one nor the edited one later in that thread even show up in the list (admin perms), much less work. Not sure why. No errors, just invisible. 

that last one in the thread (the one I linked to) worked in my v7.6 dev - I was all set to rewrite your v5 widget when I realised that it looked familiar. 😀

did you try that one ?

[bear]

I had, and it wasn't working. Then instead of trying it on the dev install I tried it on the live one and it's working. This stuff can be pretty twitchy, it seems...
It's working, and I'm ok with it as is. 

[brian!]

12 minutes ago, bear said:

This stuff can be pretty twitchy, it seems...

it's just the usual WHMCS house of cards pyramid... one slightly bent card and the whole edifice comes tumbling down. 🎴

[bear]

Is there a way to show overdue invoices in a different color in this module? 
I know how to do date comparisons, but inserting an if/else in this module isn't working. Maybe it needs to be done in the query by grabbing the status or something?