Grabbing Line Items Of Invoice

[180D]

Hey All,

i'm just trying to generate a custom report.

I've used the report invoices for most of it and just modifying for what I need however, I'm trying to grab line items of the invoice, except, when I select Line Items, I get a blank query with no errors.

My guess is this should of worked, it's been years since I worked on PHP and I'm relatively new to WHMCS.... Trying to refresh my mind on how all this works can be hard sometimes.

Quote

$filters = array();
    foreach ($filterfield as $i => $val) {
        if ($val && array_key_exists($val, $filterfields)) {
            if ($val == 'clientname') {
                $val = "(SELECT CONCAT(firstname,' ',lastname) FROM tblclients WHERE id=tblinvoices.userid)";
            }
            if ($val == 'lineitems') {
                $val = "(SELECT description FROM tblinvoiceitems WHERE invoiceid=tblinvoivces.id) or die(mysqli_error())";
            }
            $filters[] = ($filtertype[$i]=="like")
                ? $val . " LIKE '%" . db_escape_string($filterq[$i]) . "%'"
                : $val . "='" . db_escape_string($filterq[$i]) . "'";
        }
    }

The if $val statement for line items select description from tblinvoiceitems where invoiceid = invoice number i thought should work... but when I select line items i get a blank result all together.

Am i missing something here?

[180D]

Please ignore this, I took it under my wing to try and work out/learn the new format as suggestion on WHMCS Creating new Reports.

My new question is, how does one modify the size of "TableValues" & "TableHeadings" since it suggests to continue to use these.

 

Quote

USE WHMCS\Database\Capsule;

 /** Check For Unpaid Invoices */
 
foreach (Capsule::table('tblinvoices')->where('status', '=', 'Unpaid')->get() as $data) {
    
    /** Now get the Full name and Company Name */ 
    
    foreach (Capsule::table('tblclients')->where('id', '=', $data->userid)->get() as $name) {

                /** Now Check if Company Name Exists and Change the name accordinally */
            
        if (!$name->companyname) { 
            $fullname = "$name->firstname $name->lastname";
        } else { 
            $fullname = "$name->firstname $name->lastname ($name->companyname)";
        }
    
            /* Now Get The Line Items for the invoice */
            
                foreach (Capsule::table('tblinvoiceitems')->where('invoiceid', '=', $data->id)->get() as $invoiceitems) {
    
     /* Display the data, Full Name, Invoice Number, Line Items, Total, Due Date. */
     
        $reportdata ["tablevalues"][] = array("$fullname", "$data->id", "$invoiceitems->description", "$data->total", "$data->duedate");
}
}
}

As $invoiceitems can be quite long, it has squished everything else together and doesn't look great.

I'm still working on a way to change $invoiceitems to one line, as it creates a second row for each one  which i also don't want.

[KamyabIndia]

Were you able to get this report working?

if yes, please share the full file, i'm getting the code when i try to run your file.

Thank you for the contribution.