Hi! how can I run an AOR report where the master record DOES NOT have linked records? i.e. I want a list of clients from one table, that do not have an address entered in another table? Thanks.
Okay I found what’s going wrong with the way AOR has been built. In modules/aor_reports/AOR_Report.php file, line 980 you have:
$value = “’” . $this->db->quote($condition->value) . “’”;
So when the field test for a master / without detail record should be “ID is NULL” to find all master records that do not have a related record, this code line becomes ID = ‘’
The correct way to do this would be to test whether $this->db->quote($condition->value) is empty, and if so make the WHERE condition IS NULL as opposed to = ‘’
Given the way that AOR needs to operate to be fully compatible with KReports, you should also add conditions that are is empty / is empty or NULL / is NULL / is not empty and then you will have more flexibility in the report conditions.
In order to accomplish this, its a fairly easy hack:
Add the following to: en_us.AdvancedOpenReports.php
$app_list_strings[‘aor_operator_list’][‘Is_Empty’] = ‘Is Empty’;
$app_list_strings[‘aor_operator_list’][‘Is_Null’] = ‘Is NULL’;
$app_list_strings[‘aor_operator_list’][‘Is_Not_Empty’] = ‘Is Not Empty’;
$app_list_strings[‘aor_sql_operator_list’][‘Is_Empty’] = ‘= “”’;
$app_list_strings[‘aor_sql_operator_list’][‘Is_Null’] = ‘IS NULL’;
$app_list_strings[‘aor_sql_operator_list’][‘Is_Not_Empty’] = ‘!= “”’;
Change line 274 in modules/AOR_Reports/controller.php to:
$valid_opp = array(‘Equal_To’, ‘Not_Equal_To’,‘Contains’, ‘Starts_With’, ‘Ends_With’, ‘Is_Empty’, ‘Is_Null’, ‘Is_Not_Empty’);
Change the end of the SWITCH statement that starts in line 984 in modules/AOR_Reports/AOR_Reports.php so it reads:
//handle like conditions
Switch($condition->operator) {
case ‘Contains’:
$value = “CONCAT(’%’, “.$value.” ,’%’)”;
break;
case ‘Starts_With’:
$value = “CONCAT(”.$value." ,’%’)";
break;
case ‘Ends_With’:
$value = “CONCAT(’%’, “.$value.”)”;
break;
case ‘Is_Empty’:
case ‘Is_Null’:
case ‘Is_Not_Empty’:
$value = “”;
break;
}
Mmmm ran into a problem, you need to expose the relationship tables as well as the linked tables for this to work, because of the way you are building the queries using double jointed LEFT JOINs the only way to truly find master records WITHOUT a detail record is to test the (relationship record).id IS NULL. You need to build the table / field list as KReports does, do you have access to the columns, the relationship tables and the audit tables. Then AOR will be truly as flexible.
Okay found a fix, swapped the switch statement for the following which then allows you to check for the existing of the ID field IS NULL in the linking table itself. I added a new “Is_Unlinked” condition as well which then links with the below otherwise the standard functionality does not expose the linking table in the tree and because of the LEFT JOIN queries used it wont detect missing linked records:
//handle like conditions
Switch($condition->operator) {
case 'Contains':
$value = "CONCAT('%', ".$value." ,'%')";
break;
case 'Starts_With':
$value = "CONCAT(".$value." ,'%')";
break;
case 'Ends_With':
$value = "CONCAT('%', ".$value.")";
break;
case 'Is_Empty':
case 'Is_Null':
case 'Is_Not_Empty':
$value = "";
break;
case 'Is_Unlinked':
$value = "";
$field = $this->db->quoteIdentifier($oldAlias ."|". $table_alias) . '.id';
break;
}