HEEELP! unable to retrieve domainstatus from tblhosting using capsule table class

[cgheonea]

Hey guys,

 

When I run this query in mysql it works, however, when I run it using the following, it does not work!

$servicestatus = CAPSULE::table('tblhosting')
          ->select('domainstatus')
          ->where('id', $description->{'relid'})
          ->where('username', $output[1])
          ->get();

$servicestatus = CAPSULE::table('tblhosting')
          ->select('domainstatus')
          ->where('id', $description->{'relid'})
          ->where('username', $output[1])
          ->get();

 

Can anyone tell me what's wrong with the class?

I debugged the procedure and I have relevant values for relid and output 1 so I should get a positive response when I var_dump the servicestatus.

 

Thanks and kind regards,

Cristian

[brian!]

On 11/04/2019 at 13:07, cgheonea said:

Can anyone tell me what's wrong with the class?

I don't like the look of that ->where('id', $description->{'relid'}) line - it would be interesting to know if that's even passing a valid value to the search...

also, if you just want a value and not an array (and you know the search should only return one result)...

$servicestatus = CAPSULE::table('tblhosting')
          ->where('id', $description->{'relid'})
          ->where('username', $output[1])
          ->value('domainstatus');

though I doubt that would work either because of that description line... how are you generating that value?

[cgheonea]

Hi Brian,

 

Your method did not work.

The method i use to get the relid value is:

    $userinvoicedescription = CAPSULE::table('tblinvoiceitems')
            ->select('id', 'invoiceid', 'description', 'relid')
            ->where('type', 'Hosting')
            ->where('userid', $_SESSION['uid'])
            ->get();

 

And it has values.

All I am trying to do is to get for each invoice id the service id and it's status.

 

The whole script is:

<?php

use WHMCS\Database\Capsule;

add_hook('ClientAreaPageInvoices', 1, function($vars) {
    global $smarty;
    $invoicedesc = array();

    $userinvoicedescription = CAPSULE::table('tblinvoiceitems')
            ->select('id', 'invoiceid', 'description', 'relid')
            ->where('type', 'Hosting')
            ->where('userid', $_SESSION['uid'])
            ->get();

    $admin = Capsule::table('tbladmins')
            ->where('roleid', '=', 1)
            ->get();
    $adminUsername = $admin[0]->username;



    foreach ($userinvoicedescription as $description) {

        $servicestatus = CAPSULE::table('tblhosting')
                ->select('domainstatus')
                ->where('id', $description->{'relid'})
                ->get();

        /*
          $command = 'GetClientsProducts';
          $postData = array(
          'clientid' => $_SESSION['uid'],
          'stats' => false,
          'serviceid' => $description->{'relid'}
          );


          $usersservices = localAPI($command, $postData, $adminUsername);
         */
        preg_match('/.*Username:\s(.*)/', $description->{'description'}, $output);
        if (count($output) > 0) {
            $invoicedesc[$description->{'invoiceid'}]['username'] = $output[1];
            $invoicedesc[$description->{'invoiceid'}]['relid'] = $description->{'relid'};
            $invoicedesc[$description->{'invoiceid'}]['svcstatus'] = $servicestatus->{'domainstatus'};
        }
    }
    $smarty->assign('usersinvoice', $invoicedesc);
});
?>

I tried a couple of things but none works.

I used the whmcs documentation of course, no success...

 

 

[brian!]

2 hours ago, cgheonea said:

Your method did not work.

I warned you it wouldn't! 🙂

2 hours ago, cgheonea said:

All I am trying to do is to get for each invoice id the service id and it's status.

I tried a couple of things but none works.

I used the whmcs documentation of course, no success...

that's an extraordinarily kinky way of trying to do this. 😲

i'm assuming that you're just wanting to add extra fields/columns to the used in the output of the My Invoices table ?? if so, then you should only need to use this...

<?php

/**
* Add Fields To Invoices Table Hook
* @author brian!
*/

use WHMCS\Database\Capsule;

function add_fields_to_invoices($vars)
{
	$client = Menu::context('client');
	$invoices = $vars['invoices'];	
	foreach ($invoices as $key => $invoice) {
		$hostingstatus = Capsule::table('tblinvoiceitems')->join('tblhosting','tblinvoiceitems.relid','=','tblhosting.id')->where('type','Hosting')->where('tblinvoiceitems.userid',$client->id)->where('invoiceid',$invoice['id'])->select('relid','username','domainstatus')->first();
		if ($hostingstatus) {
			$invoices[$key]['username'] = $hostingstatus->username;
			$invoices[$key]['hostingstatus'] = $hostingstatus->domainstatus;
			$invoices[$key]['relid'] = $hostingstatus->relid;
		}
	}
	return array("invoices" => $invoices);
}
add_hook("ClientAreaPageInvoices", 1, "add_fields_to_invoices");

for hosting products only, the hook will add upto 3 new fields in each invoice record - username, hostingstatus (domainstatus) and relid - these new values can be accessed using {$invoice.username}, {$invoice.hostingstatus} and {$invoice.relid} within the foreach loop in the clientareainvoices.tpl template.