or is the field in the database?

[michael10fr]

good evening,

 

I want to know what is the table that displays the following information:

 

**** summarize a customer

 

Paid 0 (€ 0.00EUR)

Unpaid / Dû22 2 (€ 75.71EUR)

Canceled 0 (€ 0.00EUR)

Reimbursed 0 (€ 0.00EUR)

Collections 0 (€ 0.00EUR)

Revenues € 0.00EUR

Credit Balance € 0.00EUR

 

 

I search all the base but I can not find?

 

thank you for your help

[sentq]

it's not saved inside Database table, but it is a calculation process done with data available inside DB,

if you need to use this information or to customize it you can find the that handle and display this info inside /modules/widgets/income_forecast.php

[michael10fr]

Said thank you for your response to me eclair more how this works. I would like to add the fields made paid / due and returned to the table in order /admin/clients.php, for this view in addition to a look oeuil list any customer avev how much he paid, what it should me and how much they spend on my website. how little is you do that? because whmcs is fully encrypted by ioncube? thank you to help me do that. we are a company that proposont whmcs your application our customers, we install and configurond to over use it for our activitée?

[brian!]

the part of WHMCS that you refer to is created in the clientsummary.tpl template in the admin directory - this template is not encrypted and can be edited.

 

the part of the template that displays the income table is...

 

<table class="clientssummarystats" cellspacing="0" cellpadding="2">
<tr><td width="110">{$_ADMINLANG.status.paid}</td><td>{$stats.numpaidinvoices} ({$stats.paidinvoicesamount})</td></tr>
<tr class="altrow"><td>{$_ADMINLANG.status.unpaid}/{$_ADMINLANG.status.due}</td><td>{$stats.numdueinvoices} ({$stats.dueinvoicesbalance})</td></tr>
<tr><td>{$_ADMINLANG.status.cancelled}</td><td>{$stats.numcancelledinvoices} ({$stats.cancelledinvoicesamount})</td></tr>
<tr class="altrow"><td>{$_ADMINLANG.status.refunded}</td><td>{$stats.numrefundedinvoices} ({$stats.refundedinvoicesamount})</td></tr>
<tr><td>{$_ADMINLANG.status.collections}</td><td>{$stats.numcollectionsinvoices} ({$stats.collectionsinvoicesamount})</td></tr>
<tr class="altrow"><td><strong>{$_ADMINLANG.billing.income}</strong></td><td><strong>{$stats.income}</strong></td></tr>
<tr><td>{$_ADMINLANG.clients.creditbalance}</td><td>{$stats.creditbalance}</td></tr>
</table>

however, the clients.php page doesn't use a template, so it is not as easy to modify and you won't be able to transplant part of one page to another.

 

an alternative option might be to use the Top 10 Clients By Income Report, but modify it to show all clients rather than just the top 10 - that could be done very easily by modifying the SQL used in the report.

 

http://docs.whmcs.com/Reports#Top_10_Clients_By_Income

 

also, it might be worth waiting for WHMCS v6 to be released in the Summer, as this may display the admin area in a more template-based way - I don't know if it will, but if it does, it should be far easier to do what you want to do to the clients table.

[michael10fr]

hello tanks briant, for your reponse

 

I modify query:

 

$query = "SELECT tblclients.id,tblclients.firstname, tblclients.lastname, SUM(tblaccounts.amountin/tblaccounts.rate), SUM(tblaccounts.fees/tblaccounts.rate), SUM(tblaccounts.amountout/tblaccounts.rate), SUM((tblaccounts.amountin/tblaccounts.rate)-(tblaccounts.fees/tblaccounts.rate)-(tblaccounts.amountout/tblaccounts.rate)) AS balance, tblaccounts.rate FROM tblaccounts INNER JOIN tblclients ON tblclients.id = tblaccounts.userid GROUP BY userid ORDER BY balance DESC LIMIT 0,10000";

 

to 10000 and I want to know , how to know field for add to this query ?

 

I search all google and no any documentation for the field name of whmcs ?

 

I want to create query for this information:

 

name, company, email address, telephone number, Unpaid / Due, total revenues

 

how to add fields to it is this request?

 

I'm new to mysql and I do not know or find the name of the fields especially that of Unpaid / Due, total income, I did not find ??

 

thank you for your help ...