2

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()?

2
  • 1
    I don't have a direct answer but regarding the having before group, there's a pending code change that has sort of stalled that might address that. See github.com/civicrm/civicrm-core/pull/…
    – Demerit
    Commented Jun 29, 2020 at 15:40
  • Thank you @Demerit, I've added a comment on the issue.
    – Bastien Ho
    Commented Jun 30, 2020 at 8:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.