CiviCRM Community Forums (archive)

Hello all,

Yup, it's me again and I'm still working on my tax field extension module 

Hopefully this post will attract the attention of someone with a better understanding of the relationship between DAO's and report models than me.

I've been struggling with a custom contribution report for a while now and I've almost got it figured out but I just can't get it. The trouble is that there are a couple of fields that are not showing up in my report and that is because those fields are brought in via sub-queries that are grafted onto the main query select statement.

Still with me? Here's the back story...

Ok, so this report is for an extension that has a couple of database tables of its own that relate to CiviCRM data primarily in a direct relationship with civicrm_contribution.invoice_id. Here's the setup:

My extension contains  /CRM/Report/Form/Contribute/Detail.php which overrides CiviCRM's existing Contribution Detailed Report.

1) I've added my custom fields to the _columns array for the report and bound them to a DAO which is defined in /CRM/Core/DAO/TaxInvoicing.php. The results look like this when inspected and I can set them via check boxes in the report criteria part. So far so good.

[civi_tax_invoicing] => Array
        (
            [dao] => CRM_Core_DAO_TaxInvoicing
            [fields] => Array
                (
                    [pre_tax] => Array
                        (
                            [title] => Pre Tax Amount
                            [default] => 1
                        )

                    [tax_pst] => Array
                        (
                            [title] => PST Tax
                            [default] => 1
                        )

                    [tax_gst] => Array
                        (
                            [title] => GST Tax
                            [default] => 1
                        )

                    [tax_hst] => Array
                        (
                            [title] => HST Tax
                            [default] => 1
                        )

                    [tax_charged] => Array
                        (
                            [title] => Total Tax Charged
                            [default] => 1
                            [statistics] => Array
                                (
                                    [sum] => Total Tax Charged
                                )
                        )
                )
            [grouping] => contact-fields
        )


2) I've added a LEFT JOIN to the from() function in my report model (Detail.php) that joins the custom database table for pre_tax and tax_charged. This works correctly and when I select the "Pre Tax Amount" or the "Total Tax Charged" check boxes in the criteria for the report, it generates the report and includes these fields in the report. Awesome! This is working exactly as I had hoped!

3) The missing part is figuring out how to bind the fields that represent each individual tax amount to the report. The problem is that this information is completely dynamic, meaning that when the extension is installed there are no tax fields defined. The administrator defines the taxes, applies them to the contribution types and then they are a thing.

Tax types are defined in a tax_type table and transactions are stored in a tax_invoicing table so in order to get the info for each transactions specific tax there are sub-queries as follows:

# This is a general example of one of the sub-queries that produces an individual tax field
(SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE contribution_civireport.invoice_id = civi_tax_invoicing.invoice_id  COLLATE utf8_unicode_ci AND civi_tax_invoicing.tax_id = 3) AS civi_tax_invoicing_tax_hst

I've tried to define this in my DAO by pulling the data from the database on the fly and adding them to the fields() function in my custom DAO and that looks like this:


Array
(
    [id] => Array
        (
            [name] => id
            [type] => 1
            [required] => 1
        )

    [invoice_id] => Array
        (
            [name] => invoice_id
            [type] => 2
        )

    [tax_id] => Array
        (
            [name] => tax_id
            [type] => 1
        )

    [tax_name] => Array
        (
            [name] => tax_name
            [type] => 2
        )

    [pre_tax] => Array
        (
            [name] => pre_tax
            [type] => 1024
            [title] => Pre Tax
            [required] =>
            [import] => 1
            [where] => civi_tax_invoicing.pre_tax
            [dataPattern] => /^\d+(\.\d{2})?$/
            [export] => 1
        )

    [tax_rate] => Array
        (
            [name] => tax_rate
            [type] => 2
        )

    [tax_charged] => Array
        (
            [name] => tax_charged
            [type] => 1024
            [title] => Tax Charged
            [required] =>
            [import] => 1
            [where] => civi_tax_invoicing.tax_charged
            [dataPattern] => /^\d+(\.\d{2})?$/
            [export] => 1
        )

    [post_tax] => Array
        (
            [name] => post_tax
            [type] => 2
        )

    [tax_pst] => Array
        (
            [name] => tax_pst
            [type] => 1024
            [title] => PST
            [required] =>
            [import] => 1
            [where] => (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE contribution_civireport.invoice_id = civi_tax_invoicing.invoice_id  COLLATE utf8_unicode_ci AND civi_tax_invoicing.tax_id = 1)
            [dataPattern] => /^\d+(\.\d{2})?$/
            [export] => 1
        )

    [tax_gst] => Array
        (
            [name] => tax_gst
            [type] => 1024
            [title] => GST
            [required] =>
            [import] => 1
            [where] => (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE contribution_civireport.invoice_id = civi_tax_invoicing.invoice_id  COLLATE utf8_unicode_ci AND civi_tax_invoicing.tax_id = 2)
            [dataPattern] => /^\d+(\.\d{2})?$/
            [export] => 1
        )

    [tax_hst] => Array
        (
            [name] => tax_hst
            [type] => 1024
            [title] => HST
            [required] =>
            [import] => 1
            [where] => (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE contribution_civireport.invoice_id = civi_tax_invoicing.invoice_id  COLLATE utf8_unicode_ci AND civi_tax_invoicing.tax_id = 3)
            [dataPattern] => /^\d+(\.\d{2})?$/
            [export] => 1
        )

)


That didn't work... If I select the individual taxes from my criteria check boxes, I am returned an error.

The next thing I tried was to graft the sub-queries right into the main query by sticking them into a string variable and adding them to the query right at the beginning of the from() function in my report (Detail.php).

I can pull the whole query string before it goes into the postProcess() function and it appears to be intact and if I run it directly against my database via MySQL Workbench, it runs and gives me the results I expect however, CiviCRM realizes I'm trying to pull a fast one and at some point it yanks my added bits out.

I've hit a wall   

Are you still with me?

Sorry it took so long to tell the story but I wanted to get in all the pertinent details. Now if you followed this far, does anyone have some insight as to how I can get past the impasse? I have a feeling it's got to do with the way I've set up my DAO and because there is that whacky sub-query issue, I may have to do an ugly work around to force Civi to take the query but I'm open to anything at the moment.

I have a test site set up and if anyone is interested in helping me with it, I'll give you a login to see where it's at.

Thanks,
Andrew

Oh, the files for reference are here:
The project: https://github.com/awasson/civiTAX
Detail: https://github.com/awasson/civiTAX/blob/civitax_4.4.x/CRM/Report/Form/Contribute/Detail.php
Custom DAO: https://github.com/awasson/civiTAX/blob/civitax_4.4.x/CRM/Core/DAO/TaxInvoicing.php

 

Hi Eileen,

I am so glad my post caught your attention. I've read some of your posts and tips, practices, etc... about reports and figured you might be able to shed some light.

This is great info. One of the problems I've run into is that when I set one of these wonky fields to show via the criteria settings, it throws an error but I can't track it down beyond it doing something that breaks the page. I think now based on the missing parts you've provided about matching $this->columnHeader with $row as well as  'pseudofield' => TRUE, I should be able to set those headers and unset them later in order to inspect what's going on further down the line.

Thanks so much for the reply!

Andrew   

Hmm - I thought it was there - but now I see it's only there on filters - I obviously take advantage of this


            // 1. In many cases we want select clause to be built in slightly different way
            //    for a particular field of a particular type.
            // 2. This method when used should receive params by reference and modify $this->_columnHeaders
            //    as needed.
            $selectClause = $this->selectClause($tableName, 'fields', $fieldName, $field);

& set something there that I either use or ignore later.

I think pseudofield would be useful on select too.

So, there are no docs - feel free to write some :-)

But yes, 'selectClause' basically means passes the field name out to that function & you can check the fieldname & return either the string you want added to a query or something like '1 as my_fieldname' if you intend to swap it out later. and then the parent class will not attempt to write it's own select clause for that field.

Here is an example of using selectClause to override the normal select field addition. Note this is in a base class because I obviously replace the standard phone with a block of phone numbers in multiple reports. So basically my join to civicrm_phone is missing the ' AND is_primary = 1', there is a group by on contact id & I have used the function you are looking at to set it do a group concat into the field rather than the standard select clause


https://github.com/eileenmcnaughton/nz.co.fuzion.extendedreport/blob/master/CRM/Extendedreport/Form/Report/ExtendedReport.php#L2034
 ( the line that says $this->_columnHeaders['civicrm_tag_tag_name']; looks like a mistake)

Ok... This is the first movement I've made in about 2 weeks 

I've looked at your example and modified it for my use by hard coding the 3 tax types I am using for my example site. It will allow me to select one tax at a time and will use my sub queries from the looks of things.

Of course it's not working perfectly and I can only select one tax at a time. If I choose more than one type, I get a database error (DB Error: unknown error) but as you can see it is a generic error. Here's the code I'm using:


function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
 
    if($fieldName == 'tax_pst') {
      $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
      $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
      $this->_columnHeaders['civi_tax_invoicing_tax_pst'];
      return TRUE;
    } elseif ($fieldName == 'tax_gst') {
      $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
      $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
      $this->_columnHeaders['civi_tax_invoicing_tax_gst'];
      return TRUE;
    } elseif ($fieldName == 'tax_hst') {
      $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
      $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
      $this->_columnHeaders['civi_tax_invoicing_tax_hst'];
      return TRUE;
    } 

return FALSE;
   
}



Hi Eileen,

Inside of my selectClause function I've added a couple of lines to print the results of $this->_columnHeaders to see what shows up. This is the results of that for this particular field:


[civi_tax_invoicing_tax_hst] => Array
        (
            [title] => HST Tax
            [type] => 1024
            [pseudofield] => 1
        )


If I add:
$this->_columnHeaders["{$tableName}_{$fieldName}"]['operatorType'] = 'CRM_Report_Form::OP_MULTISELECT';
I get the following but I still can't show more than one field at a time:
[civi_tax_invoicing_tax_hst] => Array
        (
            [title] => HST Tax
            [type] => 1024
            [pseudofield] => 1
            [operatorType] => CRM_Report_Form::OP_MULTISELECT
        )


Oh, Ok. I'm actually not using the fields as filters at all. I'm only using them as columnHeaders that can be selected to be part of the report from the "Criteria" section. Do you think I need to have them as filters as well?

If I choose only one of these special fields to show at a time all is fine but if I want a report to include more than one of these particular fields, I get the error (DB Error: unknown error).