client credits

[amnesia]

Is it possible to list the credit amount in every clients account?

 

I want to see the amounts that clients have as credit but going through every single client one by one is a pain. Is there an easier way to check the credit balance of all clients?

[Lethanialist]

SELECT * FROM `tblclients` WHERE `credit` >0

 

That should do the trick. It will list all the accounts that DO have credit from the database.

[amnesia]

I am positive I am not doing this right. How do I create a report showing this data? Here is what I have so far but like I said, Im sure it is wrong as I am not a coder.

 

 

 

<?php

if (!defined("WHMCS"))
die("This file cannot be accessed directly");

$reportdata["title"] = "Client Credit Balance";
$reportdata["description"] = "This report shows the balance of client credits.";

$reportdata["tableheadings"] = array("Client Name","Total Available Credit");

$query = "SELECT tblclients.firstname, tblclients.lastname ORDER BY tblclients.firstname ASC";
$result=mysql_query($query);
while($data = mysql_fetch_array($result)) {

$userid = $data[0];


$query2 = "SELECT * FROM `tblclients` WHERE `credit` >0";
$result2=mysql_query($query2);
while($data2 = mysql_fetch_array($result2)) {

$ccredit = $data2[0];


$reportdata["tablevalues"][] = array("$userid","$ccredit");

}

?>

[PPH]

Is it possible to list the credit amount in every clients account?

 

I want to see the amounts that clients have as credit but going through every single client one by one is a pain. Is there an easier way to check the credit balance of all clients?

 

I need to do this because I have noticed multiple clients with credits of 2,000.00+ (in error) and that should not happen.

 

Glad to see someone else realize the need for some kind of management for the credit system. A report would be great to go over everything, but really need a way to manage the credit system as a whole. Clients and admins also need to be able to see the accounting of the credits both in and out.

 

Currently customers are becoming quite confused as to what is actually going on when they deposit credits since all they see is the total left. With auto payments on some and credits for others. The back and forth of adding and subtracting from the balance is quite confusing to keep track of.

[amnesia]

YES! I agree, something MUST be done to avoid all the confusion this credit system has caused. My books are a mess because of it and I need some way to keep track of who and what.... hence the reason I need to be able to see what credits clients have all from one page.

 

Currently I must look through each clients profile to see the credits of every client, that is a huge PITA

[amnesia]

Matt, is there any way you can help me out with this report? I just need something listing the names of clients and the credit balance they currently hold.

 

I would appreciate any help on this.

[webresellers]

We just found at least 30 customers that have not gotten billed over 3 months, and a ton of bogus credits listed for these customers. When you look at the credits, they all seem to be paypal transactions for other customers and other oddities.

We also noticed that if delete the credits, when you get to the last credit for the customer to delete, it will show they have a $2008.00 credit. (wierd that its that dollar ammount and that the year we are in) Then you have to goto to the profile tab, and delete this $2008.00 credit value.

Yes, something is needed. A report would be real nice!

We are using WHMCS 3.6. Since we have used WHMCS for years now, obviously this is an upgraded database, not that it should really matter since credits didn't exist when we 1st started using WHMCS.

[webresellers]

Just got this email from the forums because I am watching this thread, and the post is not here....

 

RECIEVED EMAIL:

 

Dear webresellers,

 

Andrew has just replied to a thread you have subscribed to entitled - client credits - in the Technical Issues and Questions forum of WHMCS Forums.

 

This thread is located at:

http://forum.whmcs.com/showthread.php?t=9280&goto=newpost

 

Here is the message that has just been posted:

***************

Hi,

 

Just knocked up a quick report, how does this look??

 

 

PHP:

---------

<?php

if (!defined("WHMCS"))

die("This file cannot be accessed directly");

 

$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"); //for ( $counter = 1; $counter <= 31; $counter += 1) { //$counter = str_pad($counter, 2, "0", STR_PAD_LEFT); $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); //$data = mysql_fetch_array($result); 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);

$intakeused = sprintf("%01.2f",$intakeused);

$reportdata["tablevalues"][] = array(

"<a href='./clientssummary.php?userid=" . $cid . "' target='_top'>" . $clientname . "</a>",

$CONFIG["CurrencySymbol"].$applied,

$CONFIG["CurrencySymbol"].$usedinvoices,

$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"; ?>

---------

 

***************

 

 

There may also be other replies, but you will not receive any more notifications until you visit the forum again.

 

All the best,

WHMCS Forums

[WHMCS Andrew]

I removed it as I was ironing out some kinks.. but here you go

 

<?php
if (!defined("WHMCS"))
die("This file cannot be accessed directly");

$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"); //for ( $counter = 1; $counter <= 31; $counter += 1) { //$counter = str_pad($counter, 2, "0", STR_PAD_LEFT); $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); //$data = mysql_fetch_array($result); 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);
$intakeused = sprintf("%01.2f",$intakeused);
$reportdata["tablevalues"][] = array(
"<a href='./clientssummary.php?userid=" . $cid . "' target='_top'>" . $clientname . "</a>",
$CONFIG["CurrencySymbol"].$applied,
$CONFIG["CurrencySymbol"].$usedinvoices,
$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"; ?>

[Matt]

From the sounds of it the issue you're having was reported and fixed on the 9th - http://forum.whmcs.com/showthread.php?t=8906

 

Matt

[amnesia]

I removed it as I was ironing out some kinks.. but here you go

 

<?php
if (!defined("WHMCS"))
die("This file cannot be accessed directly");

$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"); //for ( $counter = 1; $counter <= 31; $counter += 1) { //$counter = str_pad($counter, 2, "0", STR_PAD_LEFT); $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); //$data = mysql_fetch_array($result); 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);
$intakeused = sprintf("%01.2f",$intakeused);
$reportdata["tablevalues"][] = array(
"<a href='./clientssummary.php?userid=" . $cid . "' target='_top'>" . $clientname . "</a>",
$CONFIG["CurrencySymbol"].$applied,
$CONFIG["CurrencySymbol"].$usedinvoices,
$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"; ?>

 

 

 

 

Yea, guess there are still some kinks, its giving me a blank page

[PPH]

Yep, I mentioned a method in another post is needed to manage credits. http://forum.whmcs.com/showthread.php?t=8616. Matt had asked if a report would be helpful which I agreed would be but have yet to see one that works

 

Or has the report in this post worked for anyone?

[WHMCS Andrew]

Well, it worked for me lol

 

amnesia.. what version WHMCS you using?

[PPH]

Well, it worked for me lol

 

amnesia.. what version WHMCS you using?

 

While I am not amnesia, just have amnesia quite frequently, it produces a blank page on a 3.5.1 and a 3.6 install for us.

[webresellers]

Well, it worked for me lol

 

amnesia.. what version WHMCS you using?

 

Blank page for me too... Using 3.6

[WHMCS Andrew]

just waiting on some info for testing this on another system

I need to find out whats working on mine and not on others

[PPH]

just waiting on some info for testing this on another system

I need to find out whats working on mine and not on others

Done via PM sorry for the delay.

[WHMCS Andrew]

Hi,

 

Try this code instead:

 

<?php
if (!defined("WHMCS"))
die("This file cannot be accessed directly");

$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);
 $intakeused = sprintf("%01.2f",$intakeused);
 $reportdata["tablevalues"][] = array(
                  "<a href='./clientssummary.php?userid=" . $cid . "' target='_top'>" . $clientname . "</a>",
                  $CONFIG["CurrencySymbol"].$applied,
                  $CONFIG["CurrencySymbol"].$usedinvoices,
                  $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";
?>

[PPH]

works for me. Thanks for the report! Going to add it to our live copy.

 

Edit: Get

This file cannot be accessed directly

on that install that is on the same server although it is 3.5.1

[webresellers]

Andrew, you are the man.... This latest report it running perfect from 3.6....

OMG, I am about to freak out... We have litterly hundreds and hundreds of accounts that show credit, applied, used, and pending all over the place....

This report will litterly help us reclaim thousands of dollars in services that were never billed, and customers getting credit where they shouldn't of.

Looking at invoices and accounts that have recieved these unjust credits, it looks like this started about 3-4 months ago.... I am guessing it surfaced in 3.5.

None, the less we wont bore everyone with all the details, we just want to say THANKS for this report!!!!!

[WHMCS Andrew]

Hi,

 

Glad you like it, glad it helps,

[webresellers]

Maybe this report should get posted in the CONTRIBUTION section of the site.... Personally I believe it should be included in the future releases of WHMCS....

It would be really nice if the dollar figures were linked to the invoice credit was applied to, to the manage credit section, and the profile for the active credit.

[WHMCS Andrew]

Hi,

 

The problem with that is that the credit can be applied to more than one invoice, so it wouldn't be able to really

[lynnette]

Worked perfectly, thanks Andrew. No problems here though with credits so I wonder what makes the difference with those having problems.

[webresellers]

It looks like it might of started with the upgrade 3.4..... Looking at our notes, we see that we opened a support ticket with WHMCS where we were trying to figure out where a bunch of phantom transactions were showing up. Almost all of the phantom credits we found, all had a PAYPAL TRANSACTION description with the trans#. Most of these trans#, cross-ref'd to totally different accounts.

With some simple data massaging, running this new credit report, and manually editing accounts and their credits, I believe we are back on track.

This cleanup process, produced over 600 invoices where customers have recieved services for free for the past 1 to 4 months.

We sent a formal email to all customers, explaining the issue, and to our surprise, most just thought our credit card processing was down or something, and was wondering when they were going to get billed. (of course nobody stepped up to say anything about getting free services; but so far no pissed off customers) Looks like we are going to have a great month this month....

Thanks again, Andrew!