custom field in report

[WebHostingPeople]

Hi,

 

I added a custom field with the name of GST ID in client`s profile. I want to this custom field in Sales Tax Liability report.

 

Please guide me how can I get in report.

Edited September 18, 2017 by webhostingpeople

[brian!]

for something like this, you're usually better off contacting a developer directly, but roughly you'd need to modify the 3rd database query to include the customfieldsvalues table...

 

$query = <<<QUERY
SELECT tblinvoices.*, tblclients.firstname, tblclients.lastname, tblcustomfieldsvalues.*
FROM tblinvoices
INNER JOIN tblclients ON tblclients.id = tblinvoices.userid
JOIN tblcustomfieldsvalues ON tblcustomfieldsvalues.relid = tblinvoices.userid
WHERE datepaid >= '{$queryStartDate}'
   AND datepaid <= '{$queryEndDate} 23:59:59'
   AND tblinvoices.status = 'Paid'
   AND currency = {$currencyID}
   AND (SELECT count(tblinvoiceitems.id)
       FROM tblinvoiceitems
       WHERE invoiceid = tblinvoices.id
           AND (type = 'AddFunds' OR type = 'Invoice')
       ) = 0
   AND tblcustomfieldsvalues.fieldid = '6'
ORDER BY date ASC;
QUERY;
$result = full_query($query);
while ($data = mysql_fetch_array($result)) {
   $id = $data["id"];
   $userid = $data["userid"];
   $client = $data["firstname"]." ".$data["lastname"];
   $date = fromMySQLDate($data["date"]);
   $datepaid = fromMySQLDate($data["datepaid"]);
   $currency = getCurrency($userid);
   $subtotal = $data["subtotal"];
   $credit = $data["credit"];
   $tax = $data["tax"]+$data["tax2"];
   $total = $data["total"] + $credit;
   $custom = $data["value"];
   $reportdata["tablevalues"][] = array("$id","$custom","$client","$date","$datepaid","$subtotal","$tax","$credit","$total");
}

the only line you should need to change is the customfieldsvalues.fieldid value of 6...

 

AND tblcustomfieldsvalues.fieldid = '6'

if you're familiar with the database tables, you can find the value from the tblcustomfields table...

 

 

the value in the 'id' column will be the one you want to use in the query.

 

alternatively, go through the order process and when you get to checkout, view the source code of the page in the browser and find the custom field in question...

 

<div class="form-group">

<label for="customfield6">Where did you hear about us?</label>

<select name="customfield[6]" id="customfield6" class="form-control">

... and with regards to adding "GST ID" to the table headers, that's just an Admin Language Override used in the tableheadings array of the report.

 

btw - if you're going to edit a report template as above, you'll be better off creating a new .php file in the reports folder and giving it a custom name - that should prevent the modified report being overwritten during a WHMCS update.

[Basheer]

Hi Brian , Which php files are you mentioned above, I can see all report related pages are encrypted.

 

I am really sorry if this question is not necessary here

[brian!]

/modules/reports/sales_tax_liability.php

 

all the report .php files are not encrypted and can be modified - though as I suggested previously, if you intend to do so, create a new file or rename them to prevent them being overwritten during an update.

[Basheer]

/modules/reports/sales_tax_liability.php

 

all the report .php files are not encrypted and can be modified - though as I suggested previously, if you intend to do so, create a new file or rename them to prevent them being overwritten during an update.

 

Thanks Brian

[WebHostingPeople]

Hi,

 

Brain,

 

Thanks a lot for guiding me. Your sent code is absolutely working fine.

 

I need a little help also with you.

 

I am unable to add "GST ID" to the table headers.

 

I have try lot using Admin Language Override method.

 

Please let me know what should in tableheadings array and english.php

[brian!]

I need a little help also with you.

I am unable to add "GST ID" to the table headers.

I have try lot using Admin Language Override method.

Please let me know what should in tableheadings array and english.php

in the report file, I used the following...

$reportdata["tableheadings"] = array(
   $aInt->lang('fields', 'invoiceid'),
   $aInt->lang('fields', 'gstid'),    
   $aInt->lang('fields', 'clientname'),
   $aInt->lang('fields', 'invoicedate'),
   $aInt->lang('fields', 'datepaid'),
   $aInt->lang('fields', 'subtotal'),
   $aInt->lang('fields', 'tax'),
   $aInt->lang('fields', 'credit'),
   $aInt->lang('fields', 'total'),
);

that code is already in the .php file, apart from the $aInt->lang('fields', 'gstid'), line of code - you only need to add that one line to what's already there.

 

and then in /admin/lang/overrides/english.php, I used...

<?php

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

$_ADMINLANG['fields']['gstid'] = "GST ID";

[WebHostingPeople]

Oh yes, It`s now working.

 

My mistake , I was in module/lang/overrides/english.php

 

:)

[WebHostingPeople]

Hi,

 

With this code, report is showing 12 Invoices Found and below showing 6 rows.

[brian!]

With this code, report is showing 12 Invoices Found and below showing 6 rows.

then run the old & new reports side by side and see what data is missing from the new report.

[WebHostingPeople]

then run the old & new reports side by side and see what data is missing from the new report.

 

I checked. Result is showing only 6 transaction whenever should be 12.

With original script, result is showing 12 transactions.

[brian!]

I checked. Result is showing only 6 transaction whenever should be 12.

With original script, result is showing 12 transactions.

yes, but what are the differences between the two?

[WebHostingPeople]

In a month Transactions are 12 when you will try to get report should be shown all transactions 12 . But result is showing few transactions 6 not all transactions.

[brian!]

In a month Transactions are 12 when you will try to get report should be shown all transactions 12 . But result is showing few transactions 6 not all transactions.

yes, I get all that... but what i'm saying is that I need to know what's missing from the modified report that is present in the default report - e.g is there something special/different about the missing six transactions?

 

if I knew that, then I might be able to figure out what the error is... if I was dev'ing this on your site originally, then I could have ran the queries locally to see what the results were... but working from my own dev data, i'm not seeing what you're seeing... e.g the number of transactions calculated is always the same as the number shown.

 

you can PM me two screenshots if that would help - that at least might give me some sort of clue.

[WebHostingPeople]

yes, I get all that... but what i'm saying is that I need to know what's missing from the modified report that is present in the default report - e.g is there something special/different about the missing six transactions?

 

if I knew that, then I might be able to figure out what the error is... if I was dev'ing this on your site originally, then I could have ran the queries locally to see what the results were... but working from my own dev data, i'm not seeing what you're seeing... e/g the number of transactions calculated is always the same as the number shown.

 

you can PM me two screenshots if that would help - that at least might give me some sort of clue.

 

Number of rows are less. Column data are complete.

Let's example :

 

Invoice 1

Invoice 2

Invoice 3

Invoice 4

Invoice 5

Invoice 6

 

Means sql query is giving only 6 records. That should be 12 or more.

[brian!]

 

I get that you say it isn't returning all data, but without seeing what you're seeing, i've got no chance of debugging it when I can't reproduce the error locally.

[Craig Anderson]

Hello Brian,

 

Thank you for your help on this subject.

I need assistance with something similar.
I want to add a custom field to the default "invoice.php" report. 

I have made a copy of the report so I am not editing the original.
I know the ID of the custom field that I want to add as a filter in the report.

 

Would you be able to provide me some custom code like above for the report I am wanting to edit?