Jump to content

All Clients total due Report

xuser

By xuser
in Third Party Add-ons

 Share

Recommended Posts

xuser Member

xuser

Posted
Posted

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

 

Report looks like this:

 

report.gif

 

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

 


<?php

$res = mysql_query("select sum(tblinvoices.total) as total_dues from tblinvoices where tblinvoices.status='Unpaid'");
$row = mysql_fetch_row($res);
$total_dues = $row[0];

$reportdata["title"] = "Total Payments Dues";
$reportdata["description"] = "This will generate a report of the total amount due till date on per client basis.";

$query = "Select tblclients.id as id, tblclients.firstname as firstname, tblclients.lastname as lastname, sum(tblinvoices.total) as dues from tblclients, tblinvoices where (tblclients.id = tblinvoices.userid) and tblinvoices.status='Unpaid' group by tblinvoices.userid order by tblclients.firstname, tblclients.lastname";
$result = mysql_query($query); 

$reportdata["headertext"] = "Payments due as on today is <b><font color='red'>" . $total_dues ."</font></b>";
$reportdata["tableheadings"] = array("ID","Client Name","Total Amount Due");

while ($data = mysql_fetch_array($result)) {
   $id = $data["id"];
   $clientname = "<a href=\"clientssummary.php?userid=". $data['id'] ."\" target=\"_blank\"\">" . $data["firstname"]." ".$data["lastname"] . "</a>"; 
   $dues = $data["dues"];

   $reportdata["tablevalues"][] = array($id,$clientname, $dues);
}
$data["footertext"]="";

?>

Hope you will find this useful.

 

If you have any suggestions or need any enhancements, do let me know.

0
Link to comment
Share on other sites
othellotech Senior Member

othellotech

Posted
Posted

I made a couple of tweaks for you - clarifying the difference between *unpaid* and *overdue*, adding the company name, alternate sort orders ..

 

<?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","Total Amount Unpaid");

$query = "SELECT c.id AS id, c.firstname AS firstname, c.lastname AS lastname, c.companyname AS company, sum(i.total) AS dues ";
$query .= "FROM tblclients AS c, tblinvoices AS i WHERE (c.id = i.userid) AND i.status='Unpaid' GROUP BY i.userid ";
// if wanted in clent name order use
// $query .= "ORDER BY c.firstname, c.lastname, c.companyname";

// if wanted in client id order use
// $query .= "ORDER BY c.id";

$query .= "ORDER BY dues DESC";

$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'];

   $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);
}
$data["footertext"]="";

?>

0
Link to comment
Share on other sites
  • 3 months later...
  • 3 months later...
xuser Member

xuser

Posted
  • Author
Posted

Here is the updated version of the Report.

Updated queries produce accurate results.

 

I think this is one of the most useful reports which displays Total Dues/per client in single report. This should be included within the WHMCS inbuilt reporting system.

 

<?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"]="";

?>

0
Link to comment
Share on other sites
  • 6 years later...
  • 3 years later...
AcidburnsMK Junior Member

AcidburnsMK

Posted
Posted
On 4/9/2011 at 3:15 PM, xuser said:

Here is the updated version of the Report.

Updated queries produce accurate results.

 

I think this is one of the most useful reports which displays Total Dues/per client in single report. This should be included within the WHMCS inbuilt reporting system.

 

  


<?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"]="";

?>
 

 


Tnx , still working this.
Even tho its not sortable , but i can copy/paste the report into Word (table) and sort it out there + add total sums.

All i need if anyone can help is to show number of unpaid invoice`s like it is on client summary tab > http://prntscr.com/vf9xcc

For example after (right from) the "Unpaid" amount on the report , to be included the number of unpaid invoices, (1) (2) (3) etc...

 

0
Link to comment
Share on other sites
Acidburns Junior Member

Acidburns

Posted
Posted
On 4/9/2011 at 3:15 PM, xuser said:

Here is the updated version of the Report.

Updated queries produce accurate results.

 

I think this is one of the most useful reports which displays Total Dues/per client in single report. This should be included within the WHMCS inbuilt reporting system.

 

  


<?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"]="";

?>
 

 

Tnx , this code still working.
Even tho its not sort-able , but i can copy/paste the report into Word (table) and sort it out there + add total sum`s.

All i need if anyone can help is to show number of unpaid invoice`s like it is on client summary tab > http://prntscr.com/vf9xcc

For example after (right from) the "Unpaid" amount on the report , to be included the number of unpaid invoices, (1) (2) (3) etc...

0
Link to comment
Share on other sites
  • 3 years later...
ManagedCloud-Hosting Senior Member

ManagedCloud-Hosting

Posted
Posted

 

Hi is this still working? Thanks 

 

On 4/9/2011 at 6:45 PM, xuser said:

Here is the updated version of the Report.

Updated queries produce accurate results.

 

I think this is one of the most useful reports which displays Total Dues/per client in single report. This should be included within the WHMCS inbuilt reporting system.

 

  

<?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"]="";

?>
 

 

 

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

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