client credits

WHMCS Andrew · February 22, 2008

Hey yeah, all donations welcome

webresellers · February 22, 2008

Don't think just because you mention "donation" and I would stop posting....

PM me you paypal email address, and I will make sure to buy your pizza and beer.

Thanks again.

mysmallbizu · February 22, 2008

I worked thru some Paypal recurring payment issues with matt thru the help desk system. Basically what was happening was that all the previous Paypal recurring payments were being marked as "invalid" even though they were valid call backs.

When I went back to all of these clients I noticed that they all had $2008 credits! So I'm guessing the two were related. I think the paypal recurring issue is fixed... stripped an extra "/" out of a URL in from the IPN so I'll keep an eye on the credits.

amnesia · February 23, 2008

PERFECT!

Thank you for this report. I am using 3.6 as well and this revealed about 15 clients with wrongful credits on their accounts.

I think this report should be standard in future releases of whmcs, maybe even improved somehow.

WHMCS Andrew · February 23, 2008

Glad it works

How do you think it should be improved?

Santo · February 26, 2008

Glad it works

How do you think it should be improved?

Here are my thoughts on the subject:

1 - Make totals..

2 - Create a sub-report that only lists credits on the system. Your report is good, but if you have 1000's of clients it sure guarantees you a couple of hours of sorting numbers. By making a "Clients With Credits on System" report it will help sort things out. That or add a break down on the report by dividing the clients in 2 groups - With Credit and Without Credit

Those are just a couple of suggestions. Anyway, thank you for the code, it really helped us today and saved us allot of time browsing all our clients hunting for credit!

Cheers

brianoz · February 26, 2008

2 - Create a sub-report that only lists credits on the system.

Try this, it works for me. Basically insert the lines where shown.

*** /home/someaccount/tmp/show_credits.php   2008-02-27 00:57:23.000000000 +1100
--- show_credits.php    2008-02-27 00:55:41.000000000 +1100
***************
*** 27,32 ****
--- 27,36 ----
   $applied = sprintf("%01.2f",$applied);
   $usedinvoices = sprintf("%01.2f",$usedinvoices);
   $unused = sprintf("%01.2f",$unused);
+   if ($applied == 0 && $usedinvoices == 0 && $unused == 0 && $intakeused == 0)
+   {
+       continue;
+   }
   $intakeused = sprintf("%01.2f",$intakeused);
   $reportdata["tablevalues"][] = array(
                    "<a href='./clientssummary.php?userid=" . $cid . "' target='_top'>" . $clientname . "</a>",

WHMCS Andrew · February 26, 2008

OK, I have "stolen" brianoz's little bit of code, and added a bit of my own too.

Now you can click on the credit amount and it will list the invoices for that client that have been used for credit payments.

For 3.6:

<?php
if (!defined("WHMCS"))
die("This file cannot be accessed directly");
if (!$mode||($mode=="load"))
{ 
$reportdata["title"] = "Credit Matchup Report";
$reportdata["description"] = "This report shows a credit summary for your clients.";

$reportdata["tableheadings"] = array("Client Name","Credit Applied","Credit Used","Unused Credit", "Applied-Used");

 $query = "SELECT DISTINCT (tblclients.id) as ID, firstname, lastname , sum( amount ) as applied, 
(SELECT sum( credit ) FROM tblinvoices WHERE userid = tblclients.id) as usedinvoices
,sum( tblclients.credit ) as unused
FROM tblclients
LEFT JOIN tblcredit ON tblclients.id = tblcredit.clientid
GROUP BY id, firstname, lastname";
 $result=mysql_query($query);
 while ($data = mysql_fetch_assoc($result)) {
  $cid = $data['ID'];
  $clientname = $data['firstname'] . " " . $data['lastname'];
  $applied = $data['applied'];
  $usedinvoices = $data['usedinvoices'];
  $unused = $data['unused'];
  $intakeused = $applied-$usedinvoices;
  $applied = sprintf("%01.2f",$applied);
  $usedinvoices = sprintf("%01.2f",$usedinvoices);
  $unused = sprintf("%01.2f",$unused);
    if ($applied == 0 && $usedinvoices == 0 && $unused == 0 && $intakeused == 0)
    {
        continue;
    }
  $intakeused = sprintf("%01.2f",$intakeused);
  $reportdata["tablevalues"][] = array(
                   "<a href='./clientssummary.php?userid=" . $cid . "' target='_top'>" . $clientname . "</a>",
                   $CONFIG["CurrencySymbol"].$applied,
                   "<a href='./reports.php?report=credit_matchup&mode=invoicedetails&client=" . $cid . "' target='_top'>" . $CONFIG["CurrencySymbol"].$usedinvoices . "</a>",
                   $CONFIG["CurrencySymbol"].$unused,
                   $CONFIG["CurrencySymbol"].$intakeused
                  );
 }

 $data["footertext"] = "If the Unused Credit (the amount of credit on the client profile) does not equal the 'Applied-Used' (Credit Applied through the Manage Credit screen) minus the credit used on invoices, there is a mismatch somewhere";
}
elseif ($mode=="invoicedetails")
{
$reportdata["title"] = "Credit Matchup Report - Invoice Details - Client: <a href='./clientsummary.php?userid=" . $client . "'>" . $client . "</a>";
$reportdata["description"] = "This report shows a invoice breakdown summary for the client you have selected.";

$reportdata["tableheadings"] = array("Invoice ID","Invoice Date","Invoice Amount","Credit Used","Balance","Invoice Status");

$query = "select id, date, subtotal, credit, total, status from tblinvoices where userid = " . $client . " and credit != 0;";

$result=mysql_query($query);

while ($data= mysql_fetch_assoc($result))
{
 $invoiceid = $data['id'];
 $invoicedate = $data['date'];
 $subtotal = $data['subtotal'];
 $credit = $data['credit'];
 $total = $data['total'];
 $status = $data['status'];
 $subtotal = sprintf("%01.2f",$subtotal);
 $credit = sprintf("%01.2f",$credit);
 $total = sprintf("%01.2f",$total);
 $reportdata["tablevalues"][] = array(
                   "<a onClick=\"window.open('../viewinvoice.php?id= " . $invoiceid . "','windowfrm','menubar=yes,toolbar=yes,width=750,height=600');\" nowrap>" . $invoiceid . "</a>",
                   $invoicedate,
                   $CONFIG['CurrencySymbol'].$subtotal,
                   $CONFIG['CurrencySymbol'].$credit,
                   $CONFIG['CurrencySymbol'].$total,
                   $status
                  );
} 
}
?>

For 3.5.1:

<?php
if (!$mode||($mode=="load"))
{ 
$reportdata["title"] = "Credit Matchup Report";
$reportdata["description"] = "This report shows a credit summary for your clients.";

$reportdata["tableheadings"] = array("Client Name","Credit Applied","Credit Used","Unused Credit", "Applied-Used");

 $query = "SELECT DISTINCT (tblclients.id) as ID, firstname, lastname , sum( amount ) as applied, 
(SELECT sum( credit ) FROM tblinvoices WHERE userid = tblclients.id) as usedinvoices
,sum( tblclients.credit ) as unused
FROM tblclients
LEFT JOIN tblcredit ON tblclients.id = tblcredit.clientid
GROUP BY id, firstname, lastname";
 $result=mysql_query($query);
 while ($data = mysql_fetch_assoc($result)) {
  $cid = $data['ID'];
  $clientname = $data['firstname'] . " " . $data['lastname'];
  $applied = $data['applied'];
  $usedinvoices = $data['usedinvoices'];
  $unused = $data['unused'];
  $intakeused = $applied-$usedinvoices;
  $applied = sprintf("%01.2f",$applied);
  $usedinvoices = sprintf("%01.2f",$usedinvoices);
  $unused = sprintf("%01.2f",$unused);
    if ($applied == 0 && $usedinvoices == 0 && $unused == 0 && $intakeused == 0)
    {
        continue;
    }
  $intakeused = sprintf("%01.2f",$intakeused);
  $reportdata["tablevalues"][] = array(
                   "<a href='./clientssummary.php?userid=" . $cid . "' target='_top'>" . $clientname . "</a>",
                   $CONFIG["CurrencySymbol"].$applied,
                   "<a href='./reports.php?report=credit_matchup&mode=invoicedetails&client=" . $cid . "' target='_top'>" . $CONFIG["CurrencySymbol"].$usedinvoices . "</a>",
                   $CONFIG["CurrencySymbol"].$unused,
                   $CONFIG["CurrencySymbol"].$intakeused
                  );
 }

 $data["footertext"] = "If the Unused Credit (the amount of credit on the client profile) does not equal the 'Applied-Used' (Credit Applied through the Manage Credit screen) minus the credit used on invoices, there is a mismatch somewhere";
}
elseif ($mode=="invoicedetails")
{
$reportdata["title"] = "Credit Matchup Report - Invoice Details - Client: <a href='./clientsummary.php?userid=" . $client . "'>" . $client . "</a>";
$reportdata["description"] = "This report shows a invoice breakdown summary for the client you have selected.";

$reportdata["tableheadings"] = array("Invoice ID","Invoice Date","Invoice Amount","Credit Used","Balance","Invoice Status");

$query = "select id, date, subtotal, credit, total, status from tblinvoices where userid = " . $client . " and credit != 0;";

$result=mysql_query($query);

while ($data= mysql_fetch_assoc($result))
{
 $invoiceid = $data['id'];
 $invoicedate = $data['date'];
 $subtotal = $data['subtotal'];
 $credit = $data['credit'];
 $total = $data['total'];
 $status = $data['status'];
 $subtotal = sprintf("%01.2f",$subtotal);
 $credit = sprintf("%01.2f",$credit);
 $total = sprintf("%01.2f",$total);
 $reportdata["tablevalues"][] = array(
                   "<a onClick=\"window.open('../viewinvoice.php?id= " . $invoiceid . "','windowfrm','menubar=yes,toolbar=yes,width=750,height=600');\" nowrap>" . $invoiceid . "</a>",
                   $invoicedate,
                   $CONFIG['CurrencySymbol'].$subtotal,
                   $CONFIG['CurrencySymbol'].$credit,
                   $CONFIG['CurrencySymbol'].$total,
                   $status
                  );
} 
}
?>

PPH · February 26, 2008

Nice addition, but when I click on the link for "Credit Used" I get empty reports that state:

No Data for the ReportReport

Generated on 02/26/2008 09:41

View Printable Version

WHMCS Andrew · February 26, 2008

The 2nd report will only show if there are any invoices for that client where the "credit" amount is not 0..

so, if there are no invoices... no invoices have been paid using credit..

If this is wrong, let me know.

PPH · February 26, 2008

The 2nd report will only show if there are any invoices for that client where the "credit" amount is not 0..
so, if there are no invoices... no invoices have been paid using credit..

If this is wrong, let me know.

Sorry Andrew, guess I should have said that is what is occurring for ALL links including the ones that have been applied to invoices. (partial paid and full paid invoices)

WHMCS Andrew · February 26, 2008

Sorry Andrew, guess I should have said that is what is occurring for ALL links including the ones that have been applied to invoices. (partial paid and full paid invoices)

Can you send me some details /screenshots etc for this?

PPH · February 26, 2008

Can you send me some details /screenshots etc for this?

Better than that...you can login to our testing install that I PM'd the details before to. I have uploaded the report to that install with the same results. the report is called Credits_Better.

brianoz · February 27, 2008

"Steal" away Andrew, that's a rather nice idea ...

WHMCS Andrew · February 27, 2008

OK!

Last one I think

<?php
if (!defined("WHMCS"))
die("This file cannot be accessed directly");
if (!$mode||($mode=="load"))
{ 
$reportdata["title"] = "Credit Matchup Report";
$reportdata["description"] = "This report shows a credit summary for your clients.";

$reportdata["tableheadings"] = array("Client Name","Credit Applied","Credit Used","Unused Credit", "Applied-Used");

 $query = "SELECT DISTINCT (tblclients.id) as ID, firstname, lastname , sum( amount ) as applied, 
(SELECT sum( credit ) FROM tblinvoices WHERE userid = tblclients.id) as usedinvoices
,tblclients.credit as unused
FROM tblclients
LEFT JOIN tblcredit ON tblclients.id = tblcredit.clientid
GROUP BY id, firstname, lastname";
 $result=mysql_query($query);
 while ($data = mysql_fetch_assoc($result)) {
  $cid = $data['ID'];
  $clientname = $data['firstname'] . " " . $data['lastname'];
  $applied = $data['applied'];
  $usedinvoices = $data['usedinvoices'];
  $unused = $data['unused'];
  $intakeused = $applied-$usedinvoices;
  $applied = sprintf("%01.2f",$applied);
  $usedinvoices = sprintf("%01.2f",$usedinvoices);
  $unused = sprintf("%01.2f",$unused);
    if ($applied == 0 && $usedinvoices == 0 && $unused == 0 && $intakeused == 0)
    {
        continue;
    }
  $intakeused = sprintf("%01.2f",$intakeused);
  $reportdata["tablevalues"][] = array(
                   "<a href='./clientssummary.php?userid=" . $cid . "' target='_top'>" . $clientname . "</a>",
                   $CONFIG["CurrencySymbol"].$applied,
                   "<a href='" . $PHP_SELF . "?report=" . $report . "&mode=invoicedetails&client=" . $cid . "' target='_top'>" . $CONFIG["CurrencySymbol"].$usedinvoices . "</a>",
                   $CONFIG["CurrencySymbol"].$unused,
                   $CONFIG["CurrencySymbol"].$intakeused
                  );
 }

 $data["footertext"] = "If the Unused Credit (the amount of credit on the client profile) does not equal the 'Applied-Used' (Credit Applied through the Manage Credit screen) minus the credit used on invoices, there is a mismatch somewhere";
}
elseif ($mode=="invoicedetails")
{ 
$reportdata["title"] = "Credit Matchup Report - Invoice Details - Client: <a href='./clientssummary.php?userid=" . $client . "'>" . $client . "</a>";
$reportdata["description"] = "This report shows a invoice breakdown summary for the client you have selected.";

$reportdata["tableheadings"] = array("Invoice ID","Invoice Date","Invoice Amount","Credit Used","Balance","Invoice Status");

$query = "select id, date, subtotal, credit, total, status from tblinvoices where userid = " . $client . " and credit != 0;";

$result=mysql_query($query);

while ($data= mysql_fetch_assoc($result))
{
 $invoiceid = $data['id'];
 $invoicedate = $data['date'];
 $subtotal = $data['subtotal'];
 $credit = $data['credit'];
 $total = $data['total'];
 $status = $data['status'];
 $subtotal = sprintf("%01.2f",$subtotal);
 $credit = sprintf("%01.2f",$credit);
 $total = sprintf("%01.2f",$total);
 $reportdata["tablevalues"][] = array(
                   "<a onClick=\"window.open('../viewinvoice.php?id= " . $invoiceid . "','windowfrm','menubar=yes,toolbar=yes,width=750,height=600');\" nowrap>" . $invoiceid . "</a>",
                   $invoicedate,
                   $CONFIG['CurrencySymbol'].$subtotal,
                   $CONFIG['CurrencySymbol'].$credit,
                   $CONFIG['CurrencySymbol'].$total,
                   $status
                  );
} 
}
?>

PPH · February 27, 2008

Works great Andrew. We appreciate the extra effort. I have noticed the mouse cursor doesn't change to indicate a web link when hovering over the invoice id #'s although it opens the appropriate invoice when clicking one.

Extra feature idea:

The ability to pull up a sub report for all credits added records also to help diagnose where all the credits came from.

Once again, thanks for the added functions

Zorro67 · February 29, 2008

Great report add-on. Works perfectly in 3.6

Thanks for putting in the effort to solve this.

Dan

eugenevdm · May 15, 2008

1. I get a blank screen on version 3.6.1. Can anyone point point me in a direction where I can look at why I might be getting a blank screen?

2. On our system the query takes 55 seconds to complete. We have about 1500 customers. Perhaps this is to do with the select within the select or the join? Anyone have any idea how we could optimize the query for speed, perhaps using a stored procedure or indexing?

EDIT:

The reason for the blank screen I had copied this into the Modules folder instead of the Reports folder. Please ignore point 1.

redrat · July 18, 2008

Works great and many thanks Andrew. I named my file credit_analysis.php. Works great.

Volt.Networks · September 9, 2008

How do I use that file?

webresellers · September 9, 2008

save file, as something like credit_analysis.php and upload it into your reports folder.

Volt.Networks · September 9, 2008

Thanks. I've been putting it in the modules folder instead, which gave me a blank screen. Does the script accurately report information if clients overpaid on an invoice instead of using the Add Funds feature?

Also, how do you tell if there is a mismatch? From the directions in the script, I notice that anyone with credit have mismatches. I might just be interpreting the directions incorrectly.

Patty · October 7, 2009

Thank you. Working fine for me on v4.0.2.

aXeR · October 7, 2009

Working fine on 4.1.1 too, thanks Andrew

client credits

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Recently Browsing 0 members

Important Information