Subpanels Cases

[J0rg3]

I’m new using SuiteCRM I’m want to create a new sub-panel on cases, What I want to display is all the cases related to the account_name:

This is my approach:

I created a relationship with studio one-2-many to the same module and then I modify the subpanel field (cases_cases_1_Cases.php) as fallow:

$layout_defs["Cases"]["subpanel_setup"]['cases_cases_1cases_ida'] = array (
  'order' => 100,
  'module' => 'Cases',
  'subpanel_name' => 'default',
  //'sort_order' => 'asc',
  //'sort_by' => 'id',
  'title_key' => 'LBL_CASES_CASES_1_FROM_CASES_R_TITLE',
  'generate_select' => true,
  //'get_subtable_data' => 'cases_cases_1cases_ida',
  'get_subpanel_data' => 'function:get_info_list',
  'function_parameters' => array(
        'type' => 'urgent',
        'import_function_file' => 'custom/modules/cases/CasesSubpanel.php',
            //'cases' => $this->_focus->id,
         'return_as_array' => 'true'
        ),
);

then I declare my function under the path:custom/modules/cases/CasesSubpanel.php

function get_info_list($params){
	
		

		$sql  = array('select c.case_number as Num, c.name as Subject, a.name as "Account Name", c.status as Status, c.date_entered as "Date Created", 
                concat(u.first_name,  " ", u.last_name) as "Assigned User"', 
		'from cases as c, users as u, accounts as a', 
		'where u.id = c.assigned_user_id as a.id = c.account_id',
		'and ;)  c.name = "Test Account"');

	

		

		$GLOBALS['log']->debug('candy ' .print_r($sql, true));

        return $sql;
      
		

	 }

but my criteria is not been consider on my query, it seems to be created by sugarBean subquery instead.

any Idea why or better way to do this :dry: ?

[pgr]

Why are you creating a new relationship?

You don’t have to create one just to have a second subpanel for the same module.

See for example

http://urdhva-tech.com/blogs/add-custom-subpanel-in-accounts-module

[J0rg3]

Thank you for the replay ‘pgr’, I created because that way I can see the name of the sub-panel, I try this test with no success. All that I want to display on the subpanel is the records created by the same account name any ideas.

[pgr]

Let me try giving you a copy of a different example. My advice is to first try to get my example working as it is, without changing anything; only after it’s working you try and adapt it to your requirement, ok?

Create this file
custom/Extension/modules/Accounts/Ext/Layoutdefs/Leads.php

with these contents:

<?php

$layout_defs["Accounts"]["subpanel_setup"]['leads'] = array(
    'order' => 80,
    'module' => 'Leads',
    'sort_order' => 'asc',
    'sort_by' => 'first_name, last_name',
    'subpanel_name' => 'default',
    'get_subpanel_data' => 'function:getRelatedLeadsQueryData',
    'function_parameters' => array(
    'import_function_file' => 'custom/modules/Accounts/GetLeadsSubpanelData.php',
    'link' => 'leads'
    ),
    'add_subpanel_data' => 'lead_id',
    'title_key' => 'LBL_LEADS_SUBPANEL_TITLE',
    'top_buttons' => array(
        array('widget_class' => 'SubPanelTopCreateLeadNameButton'),
        array('widget_class' => 'SubPanelTopSelectButton',
            'popup_module' => 'Opportunities',
            'mode' => 'MultiSelect',
        ),
    ),
);

Now let’s create that file that provides the query:
custom/modules/Accounts/GetLeadsSubpanelData.php

<?php
require_once 'custom/modules/Accounts/BuildLeadsSubpanelQueryService.php';

function getRelatedLeadsQueryData($param){
    $service = new BuildLeadsSubpanelQueryService();
    return $service->buildQuery($param);
}

And now let’s create that class in file
custom/modules/Accounts/BuildLeadsSubpanelQueryService.php

<?php

class BuildLeadsSubpanelQueryService
{

    public function __construct()
    {}

    public function buildQuery($param)
    {
        global $app;
        $controller = $app->controller;
        $bean = $controller->bean;
        $query = "
            SELECT *
            FROM leads
            WHERE leads.account_id = '$bean->id'
              AND leads.status not in ('converted')
              AND leads.deleted = 0
               ";
        return $query;
    }
}

I am using the already-existing label LBL_LEADS_SUBPANEL_TITLE, but for your case you will want to create a new, custom label, check the Developer Guide, Language chapter.

Now you should see a subpanel under Accounts that shows you Leads, but excluding “converted” leads.

You can adapt this to your requirement, it allows you to use any SQL to get the data you want. Good luck.

[J0rg3]

Thank you, I will try this one to, I modify the other example but I got:
PHP Fatal error: Uncaught Error: Call to a member function isCollection() on boolean in C:\Bitnami\wampstack-7.0.28-0\apache2\htdocs\SuiteCRM-7.8.17\data\SugarBean.php:890

Thnaks.

[ocruz]

I just followed your example to add a panel with invoices in AOS_Products, because, in our process the sell isnt done until we send the invoice, the quote is just before the invoice.

As I´m not developer (just and enthusiast) I search for this, and didnt found a solution, so, I think is a good way to share with someone thats looking for some similar to have the other steps.

First, the PANEL.
This is done exactly where @pgr wrote:

custom/Extension/modules/Accounts/Ext/Layoutdefs/ANYNAMEYOUWANT.php

 // created: 2022-12-23 00:45:08
$layout_defs["AOS_Products"]["subpanel_setup"]['aos_products_aos_invoices_1'] = array (
  'order' => 100,
  'module' => 'AOS_Invoices',
  'sort_order' => 'asc',
  'sort_by' => 'number',
  'subpanel_name' => 'default',
  'title_key' => 'ANY NAME YOU WANT TO USE FOR THE PANEL',
    'get_subpanel_data' => 'function:getCustomersInvoicedProductsQuery',
    'function_parameters' => array(
    'import_function_file' => 'custom/modules/AOS_Products/GetInvoiceSubpanelData.php',
    'link' => 'aos_invoices'
    ),
  'top_buttons' => 
  array (
    0 => 
    array (
      'widget_class' => 'SubPanelTopButtonQuickCreate',
    ),
    1 => 
    array (
      'widget_class' => 'SubPanelTopSelectButton',
      'mode' => 'MultiSelect',
    ),
  ),
);

Second, the query. This is the same code that PGR wrote, and this have to be saved in:
custom/modules/Accounts/GetInvoiceSubpanelData.php

Note that I changed the php file name to fit better my needs.

<?php
require_once 'custom/modules/AOS_Products/BuildInvoiceSubpanelQueryService.php';

function getCustomersInvoicedProductsQuery($param){
    $service = new BuildInvoiceSubpanelQueryService();
    return $service->buildQuery($param);
}

Third, the class, that run the query. This was the most dificult part because I´m not a developer, I did easy and its working without much, This is saved in the same place, and I also changes the file name to fit my needs:

custom/modules/Accounts/BuildLeadsSubpanelQueryService.php

<?php

class BuildInvoiceSubpanelQueryService
{

    public function __construct()
    {}

    public function buildQuery($param)
    {
        $query = "
                 SELECT aos_invoices.*
                   FROM aos_invoices
                WHERE aos_invoices.deleted = 0
		      AND aos_invoices.status = 'Paid'

		";
        return $query;
    }
}

In this case, I just needed the Invoice table (AOS_INVOICE) so I just query this whole with SELECT and FROM.

Not sure if that´s the better way, but seems to work.

EDIT: Thanks to PGR for pointing that maybe keep the deleted filter was a good idea. I also added another condition to filter just the STATUS as Paid.

[pgr]

You probably want to keep at least this part at the end of the SQL query:

WHERE deleted = 0

Otherwise you’ll see all deleted invoices in the subpanel.

[ocruz]

True. I found just after minutes of testing more. Is not common that we delete the invoices, because here, legally, you cant. You just “cancel” them (status) or keep them “unpaid” (status too), but you are right. I´ll make the edit to the post.

That was a test on a localhost copy…Now that I went to the online system (production), what Im struggling is to bring with the query the ACCOUNT, isnt that much necessary, but im having a hard time understading how to add that to the query, and also to integrate another number data type, because we have two different numberation systems here (legally, too).

Thanks for your observation @pgr !