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
    Jun 29, 2020 at 15:40
  • Thank you @Demerit, I've added a comment on the issue.
    – Bastien Ho
    Jun 30, 2020 at 8:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.