Jump to content

custom field in report


WebHostingPeople

Recommended Posts

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");
}

x4gkbDd.png

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

 

4H8WtDs.png

 

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

 

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. :idea:

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 4 years later...

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?

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.

  • 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