Subpanel: Purchases with Invoice (AOS_Products)

English Language - Forum
1

HI! Non Developer here.
I´ve been working on a subpanel for AOS_Products that mimics the current panel (For Quotes) but for Invoices, as long as we consider the Purchase not when we send the quote, but when we actually charge for the services (and paid).

So far, I followed this thread about this topic but changing the status and yet, working with quotes.

My approach so far is try and error, I was able to add a custom Invoice Number to the panel and all the other info, but I´m not able to add the ACCOUNT.

This is the Panel:

<?php
$module_name='AOS_Invoices';
$subpanel_layout = array(
    'list_fields' =>array(
        'number'=>array(
            'vname' => 'LBL_LIST_NUM',
        ),
		'FACTURA_02_C' =>array (
		    'vname' => 'LBL_FACTURA_02',
			'label' => 'FACTURA ID',
            'default' => true,
			'widget_class' => 'SubPanelDetailViewLink',
		),
		'NAME' => array (
		    'vname' => 'LBL_NAME',
            'label' => 'OFERTA',
			'widget_class' => 'SubPanelDetailViewLink',
            'default' => true,
            'link' => true,
        ),
		'BILLING_ACCOUNT'=>array(
			'label' => 'LBL_BILLING_ACCOUNT',
            'default' => true,
            'module' => 'Accounts',
            'id' => 'BILLING_ACCOUNT_ID',
            'link' => true,
            'related_fields' =>array (
            0 => 'billing_account_id',
         ),
        ),
		'TOTAL_AMOUNT' =>array (
		'vname' => 'LBL_GRAND_TOTAL',
        'label' => 'TOTAL',
        'default' => true,
        'currency_format' => true,
        ),
		'STATUS'=>array(
            'vname' => 'LBL_STATUS',
			'default' => true,

        ),
		'assigned_user_name'=>array(
		    'link' => 'assigned_user_link',
            'type' => 'relate',
            'vname' => 'LBL_ASSIGNED_USER',
        ),
    ),
);

In which, copying the code from the Invoice List, they use BILLING_ACCOUNT and a sort of related field to add the data. Works in AOS_ INVOICES, but not in AOS_PRODUCTS.

The query for the data is this:

<?php

class BuildInvoiceSubpanelQueryService
{

    public function __construct()
    {}

    public function buildQuery($param)
    {
        $query = "
SELECT
    aos_invoices.number As number1,
    aos_invoices_cstm.factura_02_c,
    accounts.name As name1,
    aos_invoices.*
FROM
    aos_invoices 
INNER JOIN
    aos_invoices_cstm On aos_invoices_cstm.id_c = aos_invoices.id 
INNER JOIN
    accounts On accounts.id = aos_invoices.billing_account_id
WHERE
    aos_invoices.status = 'Paid' And
    aos_invoices.deleted = 0
ORDER BY
    aos_invoices.number

		";
        return $query;
    }
}

Its kinda raw, but thats what I can do with my actual knowledge.

The result is this so far:

image
image1678×1171 79.4 KB

Not sure, If I´m missing a relationship, if my query is failing, or, if the panel is the one that fails.
Any clue to point me in the right direction?

Thanks a lot in advance.

2

@ocruz

Try to change the code
from:

to:

      'BILLING_ACCOUNT' => 
      array (
        'type' => 'relate',
        'link' => true,
        'vname' => 'LBL_BILLING_ACCOUNT',
        'id' => 'BILLING_ACCOUNT_ID',
        'width' => '10%',
        'default' => true,
        'widget_class' => 'SubPanelDetailViewLink',
        'target_module' => 'Accounts',
        'target_record_key' => 'billing_account_id',
      ),
1 Like
3

Hi @p.konetskiy, thanks for your reply.
I did try the code, but still gaves me no info in ACCOUNT column. Not sure if theres another things that is missing here. :S

4

My mistake was in the query, need to select the NAME in ACCOUNTS as the BILLING_ACCOUNT.

<?php

class BuildInvoiceSubpanelQueryService
{

    public function __construct()
    {}

    public function buildQuery($param)
    {
        $query = "
SELECT
    aos_invoices.number As number1,
    aos_invoices_cstm.factura_02_c,
    accounts.name As billing_account,
    aos_invoices.*
FROM
    aos_invoices 
INNER JOIN
    aos_invoices_cstm On aos_invoices_cstm.id_c = aos_invoices.id 
INNER JOIN
    accounts On accounts.id = aos_invoices.billing_account_id
WHERE
    aos_invoices.status = 'Paid' And
    aos_invoices.deleted = 0
ORDER BY
    aos_invoices.number

		";
        return $query;
    }
}

Was not hard, but took me a long of time to find out.
Thanks a lot!