Based on this solution, I'm trying to add a custom pan in advanced search in order to filter by Line Item Total Sum.
The context
I successfully manage to filter by Line Item Total with these fields:
public static $lineitem_fields = [
'line_total_min' => [
'name' => 'line_total_min',
'description' => 'Total of a Line Item greater than',
'title' => 'Line Total Min',
'entity' => 'LineItem',
'table_name' => 'civicrm_line_item',
'where' => 'civicrm_line_item.line_total',
'type' => CRM_Utils_Type::T_MONEY,
],
'line_total_max' => [
'name' => 'line_total_max',
'description' => 'Total of a Line Item lower than',
'title' => 'Line Total Max',
'entity' => 'LineItem',
'table_name' => 'civicrm_line_item',
'where' => 'civicrm_line_item.line_total',
'type' => CRM_Utils_Type::T_MONEY,
],
];
And these actions:
public function from($name, $mode, $side) {
if ($name != 'civicrm_line_item') return '';
return "
$side JOIN civicrm_line_item ON civicrm_line_item.contribution_id = civicrm_contribution.id
";
}
public function where(&$query) {
if (empty($query->_params)) return;
$is_queried = 0;
foreach ($query->_params as $id => $param) {
if (empty($param[0])) {
continue;
}
$is_queried += $this->whereClauseSingle($param, $query);
}
if($is_queried){
$query->_whereTables['civicrm_line_item'] = 1;
}
}
public function whereClauseSingle(&$values, &$query) {
list($name, $op, $value, $grouping, $wildcard) = $values;
$fields = $this->getFields();
if (!array_key_exists($name, $fields)) return 0;
if($name == 'line_total_min'){
$op = '>=';
$query->_where[$grouping][] = 'civicrm_line_item.line_total '.$op.' '.$value;
return 1;
}
if($name == 'line_total_max'){
$op = '<=';
$query->_where[$grouping][] = 'civicrm_line_item.line_total '.$op.' '.$value;
return 1;
}
}
Now, the problem
It becomes tricky when I want to add SUM filters
I try it by adding a search field like this:
'line_total_sum_min' => [
'name' => 'line_total_sum_min',
'description' => 'Sum of a Line Item Totals greater than',
'title' => 'Line total sum Min',
'entity' => 'LineItem',
'table_name' => 'civicrm_line_item',
'where' => 'civicrm_line_item.line_total',
'select' => 'SUM(civicrm_line_item.line_total) as line_total_sum_min',
'type' => CRM_Utils_Type::T_MONEY,
],
and, in whereClauseSingle
, the following code:
if($name == 'line_total_sum_min'){
$op = '>=';
$query->_where[$grouping]['line_total_sum_min'] = 'civicrm_line_item.line_total IS NOT NULL';
$query->_having[$grouping][] = 'line_total_sum_min '.$op.' '.$value;
return 1;
}
Actually, it could work, by the alphabetical summary in Contact BAO builds a query with the HAVING
before the GROUP BY
clause, which causes an exception. (In fact, I wonder how it can work, as SQL takes the HAVING
after GROUP BY
)
Here is the tpl content:
<table class="form-layout">
<tr>
<td>
Total
</td>
<td>
<label>{ts}{$form.line_total_min.label}{/ts}</label><br />
{$form.line_total_min.html}
</td>
<td>
<label>{ts}{$form.line_total_max.label}{/ts}</label><br />
{$form.line_total_max.html}
</td>
</tr>
<tr>
<td>
Aggregated Sum
</td>
<td>
<label>{ts}{$form.line_total_sum_min.label}{/ts}</label><br />
{$form.line_total_sum_min.html}
</td>
</tr>
</table>
The question(s)
Is there a way to use clauses only in the main search query?
In other way: is there a way to exclude clauses from alphabetQuery()
?
having
beforegroup
, there's a pending code change that has sort of stalled that might address that. See github.com/civicrm/civicrm-core/pull/…