datajoin error calc element

[twistedhardware]

Hi,

I have a calc element with the following code:

$db =& JFactory::getDBO();
$db->setQuery("SELECT SUM(`erp_transaction`.`Amount`) FROM `erp_transaction` LEFT JOIN `erp_account` ON `erp_account`.`id`=`erp_transaction`.`DebitAccount` WHERE `erp_account`.`FullAccountNumber` LIKE '{erp_account___FullAccountNumber}%' AND `erp_transaction`.Staus='Actual' AND `erp_transaction`.Organization= {erp_account___Organization_raw}");


$result_val = $db->loadResult();
return number_format($result_val,2,'.','');

It worked fine in 3.0 but in 3.1 {erp_account___Organization_raw} doesn't return a value. I have tried {erp_account___Organization} and it DOES return the label.

I have even tried $data['erp_account___Organization_raw'] but I'm still geting empty value back from it.

Just to calrify erp_account___Organization is a datajoin.

I have just updated from github.

 

[twistedhardware]

More details:
{erp_account___Organization} in table view works fine and returns the id from the datajoin. In form view it return the label of the datajoin.

{erp_account___Organization_raw} doesn't return a value in both from and list views.

 

[twistedhardware]

OK This is interesting. I have dumped $data and this is the output

array(46) {   
"erp_account___id"]=>   string(4) "1384"   
"erp_account___id_raw"]=>   string(4) "1384"   
"erp_account___AccountName"]=>   string(6) "Assets"   
"erp_account___AccountName_raw"]=>   string(6) "Assets"   
"erp_account___Organization"]=>   string(26) "Test All Accounting Cycles"   
"erp_account___AccountNumber"]=>   string(1) "1"   
"erp_account___AccountNumber_raw"]=>   string(1) "1"   
"erp_account___FullAccountNumber"]=>   string(1) "1"   
"erp_account___FullAccountNumber_raw"]=>   string(1) "1"   
"erp_account___AccountType"]=>   string(5) "Asset"   
"erp_account___AccountType_raw"]=>   string(5) "Asset"   
"erp_account___Level"]=>   string(1) "1"   
"erp_account___Level_raw"]=>   string(1) "1"   
"erp_account___Parent"]=>   NULL   
"erp_account___Active"]=>   string(3) "Yes"   
"erp_account___Active_raw"]=>   string(1) "1"   
"erp_account___AccountCode"]=>   string(16) "AssetsERPAccount"   
"erp_account___AccountCode_raw"]=>   string(16) "AssetsERPAccount"   
"erp_account___TotalCredit"]=>   NULL   
"erp_account___TotalCredit_raw"]=>   NULL   
"erp_account___TotalDebit"]=>   NULL   
"erp_account___TotalDebit_raw"]=>   NULL   
"erp_account___Balance"]=>   NULL   
"erp_account___Balance_raw"]=>   NULL   
"erp_account___DirectDebit"]=>   NULL   
"erp_account___DirectDebit_raw"]=>   NULL   
"erp_account___DirectCredit"]=>   NULL   
"erp_account___DirectCredit_raw"]=>   NULL   
"slug"]=>   string(4) "1384"   
"__pk_val"]=>   string(4) "1384"   
"option"]=>   string(10) "com_fabrik"   
"view"]=>   string(4) "form"   
"Itemid"]=>   string(3) "126"   
"formid"]=>   string(1) "4"   
"rowid"]=>   string(4) "1384"   
"listid"]=>   string(1) "4"   
"join"]=>   array(1) {     
15]=>     array(0) {     }   }   
"security_userorganization___id"]=>   NULL   
"security_userorganization___JoomlaUser"]=>   NULL   
"security_userorganization___Type"]=>   NULL   
"security_userorganization___CreateDate"]=>   NULL   
"security_userorganization___Organization"]=>   NULL   
"security_userorganization___IsActive"]=>   string(2) "No"   
"security_userorganization___EditDate"]=>   NULL   
"security_userorganization___CreatedBy"]=>   NULL   
"security_userorganization___EditBy"]=>   NULL }

It has created _raw for all the elements EXCEPT of type datajoin. I hope this helps.

 

[rob]

hi
Can you post a screen shot of the erp_organisation element's settings? I presume its a database join element?

 

[twistedhardware]

Thank you Rob for your reply.

Yes, the type of erp_organisation is database join. In the attachment you will find the settings for the element's Options and Data tabs. The rest in untouched. The third attachment is a list of all the elements and if you see the dump from $data you will find a _raw element for all the fields except for any element of type datajoins (which I believe is the opposite of what it should be).

I can replicate this behavior easily.

My Settings:
Joomla 3.0.3
The latest Github of Fabrik 3.1
Apache/2.2.22 (Ubuntu)

 

[twistedhardware]

I tried to dump $data using the stable version 3.0 (updated a week ago from github) and Joomla 2.5 and I got this output:

array(50) { 
"erp_account___id"]=> string(4) "1384" 
"erp_account___id_raw"]=> string(4) "1384" 
"erp_account___FullAccountNumber"]=> string(1) "1" 
"erp_account___FullAccountNumber_raw"]=> string(1) "1" 
"erp_account___AccountNumber"]=> string(1) "1" 
"erp_account___AccountNumber_raw"]=> string(1) "1" 
"erp_account___AccountName"]=> string(6) "Assets" 
"erp_account___AccountName_raw"]=> string(6) "Assets" 
"erp_account___Organization_raw"]=> string(2) "63" 
"erp_account___Organization"]=> string(26) "Test All Accounting Cycles" 
"erp_account___AccountType"]=> string(5) "Asset" 
"erp_account___AccountType_raw"]=> string(5) "Asset" 
"erp_account___Level"]=> string(1) "1" 
"erp_account___Level_raw"]=> string(1) "1" 
"erp_account___Parent_raw"]=> NULL 
"erp_account___Parent"]=> NULL 
"erp_account___Active"]=> string(3) "Yes" 
"erp_account___Active_raw"]=> string(1) "1" 
"erp_account___AccountCode"]=> string(16) "AssetsERPAccount" 
"erp_account___AccountCode_raw"]=> string(16) "AssetsERPAccount" 
"erp_account___TotalDebit"]=> NULL 
"erp_account___TotalDebit_raw"]=> NULL 
"erp_account___TotalCredit"]=> NULL 
"erp_account___TotalCredit_raw"]=> NULL 
"erp_account___Balance"]=> NULL 
"erp_account___Balance_raw"]=> NULL 
"erp_account___DirectDebit"]=> NULL 
"erp_account___DirectDebit_raw"]=> NULL 
"erp_account___DirectCredit"]=> NULL 
"erp_account___DirectCredit_raw"]=> NULL 
"slug"]=> string(4) "1384" 
"__pk_val"]=> string(4) "1384" 
"option"]=> string(10) "com_fabrik" 
"view"]=> string(4) "form" 
"Itemid"]=> string(3) "113" 
"formid"]=> string(1) "4" 
"rowid"]=> string(4) "1384" 
"listid"]=> string(1) "4" 
"lang"]=> string(2) "en" 
"language"]=> string(5) "en-GB" 
"join"]=> array(1) { 
17]=> array(0) { } } 
"security_userorganization___id"]=> NULL 
"security_userorganization___JoomlaUser"]=> NULL 
"security_userorganization___Type"]=> NULL 
"security_userorganization___CreateDate"]=> NULL 
"security_userorganization___Organization"]=> NULL 
"security_userorganization___IsActive"]=> string(2) "No" 
"security_userorganization___EditDate"]=> NULL 
"security_userorganization___CreatedBy"]=> NULL 
"security_userorganization___EditBy"]=> NULL }

you can see that it did create _raw for the datajoin field erp_account___Organization_raw.


I did some research in the calc element comparing it line by line to the old version and I couldn't find the source of the problem.

 

[twistedhardware]

OK I have found the source of the problem.

It is in the source code of datajoin element plugin in file databasejoin.php

In the new version you have changed _id in the parent element plugin class to id (without the leading underscore) but you forgot to update that in the databasejoin.php file.

The old version:

    public function setId($id)
    {
        // Set new element ID
        $this->_id = $id;
    }

The new version:

    public function setId($id)
    {
        // Set new element ID
        $this->id = $id; //Notice there is no underscore before id
    }

So to fix it you have to go to /plugins/fabrik_element/databasejoin/databasejoin.php
Line: 97
and change this:

if ($params->get('join_conn_id') == $connection->get('_id') || $element->plugin != 'databasejoin')

to:

if ($params->get('join_conn_id') == $connection->get('id') || $element->plugin != 'databasejoin')

I hope that was helpful and I'm glad to have participated in the development.

I have created a pull request for that on Github.

 

[rob]

ah great thanks -

I'd been bogged down with other join stuff that I thought i had to fix before even looking at this one, so hadn't had a chance to really follow through your report on the issue to find the problem

I've merged that pull request in now +1 !!!!

 

[sales2010]

Rob, can you take a look at the calc element? I think i have the same problem with the _raw, the query added to the calc element will return the label but not the value.

This query (added only for testing purpose) will return the label "Business Talk", but i need to get the value. By adding the "_raw", the placeholder will return nothing. The value should be taken from a cdd element.

$db =& JFactory::getDBO();	
$sql=("SELECT admin_camp5 from contracte_campuri_admin where clasa_admin ='{contracte_campuri___camp_clasa}' ");
echo $sql;

Updated info: the calc element is in a repeat group