Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Location
    World Wide Web
    Posts
    1,129

    Free Report - WHMCS- Clients dues report

    I wanted to see what is the total amount due on per client basis in a single report so I put together a report that shows client ID, client name, and total amount due.

    Here's the code. Just save the code as total_due.php and upload it to whmcs installation folder/modules/reports:

    PHP Code:
    <?php

    $time
    =mktime(0,0,0,date("m"),date("d"),date("Y"));
    $date date("Y-m-d",$time);

    $invoicestotal 0;
    $query "SELECT SUM(tblinvoices.total) as invoices_total FROM tblinvoices WHERE tblinvoices.status='Unpaid'";
    $result mysql_query($query);
    $row mysql_fetch_row($result);
    $invoicestotal $row[0];

    $invoicesdue 0;
    $query "SELECT SUM(tblinvoices.total) as invoices_due  FROM tblinvoices WHERE tblinvoices.status='Unpaid' AND tblinvoices.duedate<='".$date."'";
    $result mysql_query($query);
    $row mysql_fetch_row($result);
    $invoicesdue $row[0];

    $reportdata["title"] = "Unpaid Invoice Totals by Client";
    $reportdata["description"] = "This will generate a report of the total invoices outstanding on a per client basis.";

    $headertext1 "Invoices Overdue as at ".$date." is: <a href=\"invoices.php?status=Overdue\"><b><font color='red'>".$invoicesdue."</font></b></a>. ";
    $headertext2 "Invoices Unpaid as at ".$date." is: <a href=\"invoices.php?status=Unpaid\"><b><font color='purple'>".$invoicestotal."</b></a>";
    $reportdata["headertext"] = $headertext1."<br />".$headertext2;
    $reportdata["tableheadings"] = array("ID","Client Name","Dues","Amt Paid","Unpaid");

    // SELECT total,(SELECT SUM(amountin) FROM tblaccounts WHERE tblaccounts.invoiceid=tblinvoices.id) FROM tblinvoices

    $query "SELECT c.id AS id, c.firstname AS firstname, c.lastname AS lastname, c.companyname AS company, b.tot as dues, b.amtin as amountin, b.tot - b.amtin as amt_pending
    FROM tblclients as c
    INNER JOIN (
    SELECT tblinvoices.userid, sum( total ) AS tot, a.amtin FROM tblinvoices inner join (
    SELECT tblaccounts.userid, SUM( amountin ) as amtin
    FROM tblaccounts GROUP BY tblaccounts.userid) as a on a.userid = tblinvoices.userid where status <> 'Cancelled'
    GROUP BY tblinvoices.userid ) AS b ON b.userid = c.id where b.tot-b.amtin <> 0"
    ;

    $result mysql_query($query);

    while (
    $data mysql_fetch_array($result))
    {
        
    $id $data['id'];
        
    $firstname $data['firstname'];
        
    $lastname $data['lastname'];
        
    $company $data['company'];
        
    $client $firstname.' '.$lastname;
        if (
    $company != "")
        {
          
    $client .= ' ('.$company.')';
        }
        
    $dues $data['dues'];
        
    $amountin $data['amountin'];
        
    $amt_pending $data['amt_pending'];
        
    $idlink '<a href="clientssummary.php?userid='.$id.'" target="_blank">'.$id.'</a>';
        
    $clientlink '<a href="clientssummary.php?userid='.$id.'" target="_blank">'.$client.'</a>';

        
    $reportdata["tablevalues"][] = array($idlink$clientlink$dues$amountin$amt_pending);
    }
    $data["footertext"]="";

    ?>
    Screen Shot of the report is attached herewith.

    Free feel to use it and modify as per your requirements. Suggestions and comments are most welcome
    Attached Thumbnails Attached Thumbnails total-dues.gif  
    Real Value Hosting - Every day hosting solutions since 2003
    Shared Hosting Reseller Hosting VPS Dedicated Servers True 24 x 7 x 365 Support

  2. #2
    Join Date
    Jul 2006
    Location
    Clovis, CA
    Posts
    277
    Very Informational.

    I shall try it once i get to work =)

  3. #3
    Join Date
    Feb 2004
    Location
    Southern California
    Posts
    749

  4. #4
    Join Date
    May 2004
    Location
    World Wide Web
    Posts
    1,129
    Thanks Ken.
    Would love to hear your comments and suggestions to improve
    Real Value Hosting - Every day hosting solutions since 2003
    Shared Hosting Reseller Hosting VPS Dedicated Servers True 24 x 7 x 365 Support

  5. #5

    acolop for

    Hi Dear. First of all thanks for the great add on. This was a must addon on WHMCS. Rather whmcs should add it in their new versions.
    Now, there is a slight change that we can do in the php file:
    1) The invoices overdue as on date is showing as the total of the invoice amount. Rather it should be the total of the balance payment in all the overdue invoices
    2) The invoices unpaid as on date is showing as the total of the invoice amount. Rather it should be the total of the balance payment in all the unpaid invoices
    3) The report is showing the users who have paid some amount from the invoice amount and not showing the invoices totally unpaid.
    4) As it has view printable version, it should also have an option as download pdf
    Point 1,2 and 3 is a bug in the php script and poing 4 is just an option.
    If possible, please do the needful and update. Thanks!!

  6. #6
    Join Date
    Apr 2009
    Location
    UK
    Posts
    819
    That is really good - Thank you

  7. #7

    nice.

    hello,
    i find this to be very helpful for a company like us who follow up client on phone for the unpaid invoice. please let us know how we can fetch the customer contact number in this report.

Similar Threads

  1. FREE: Response Time Widget & Report for WHMCS
    By jeremyhaber in forum Software & Scripts Offers
    Replies: 12
    Last Post: 01-31-2011, 01:34 AM
  2. Replies: 8
    Last Post: 09-07-2004, 04:08 PM
  3. Logfile and Report management With Logrotate, Analog and Report Magic
    By rBX2 in forum Hosting Security and Technology
    Replies: 1
    Last Post: 07-26-2004, 04:38 AM
  4. Any PHP Database report like crystal or data report?
    By stephenvs in forum Programming Discussion
    Replies: 3
    Last Post: 07-14-2004, 03:25 AM
  5. Urchin CGI Report. Error Encountered (1006). No Permission to view Report.
    By ExtremeIS in forum Hosting Security and Technology
    Replies: 4
    Last Post: 01-21-2004, 06:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •