CiviCRM Community Forums (archive)

Hello all,

I'm putting the final touches on an extension used to define and apply taxes to contributions (events, memberships, pledges, donations) and the last piece of the puzzle is pulling the tax information into my detailed reports. Whenever a taxable transaction is completed, the tax is charged and the tax details are logged in a table that my extension created called "civi_tax_invoicing". The key that relates this information to the transaction is the invoice number. Now what I have to do is bring my tax information into the reports page but I've been spinning my wheels for a couple of days and can't seem to bring the info over to the reports.

Here's what I need to do:

• Add fields to my Report Criteria panel with pre_tax amount and then fields for the applicable taxes.
• Bind these fields in the report to the rows/columns that are reported.
• Rearrange the columns so that I show before tax, tax amounts, total including tax.

Here's what I've done so far:

• In my extension I have a modified version of Detail.php which usually resides in civicrm/CRM/Report/Form/Contribute/. I've modified this file to add my fields for the transaction (pre_tax, tax_amount). I've set up the array like so:
  
  'civi_tax_invoicing' => array(
        'dao' => 'CRM_Core_DAO_CiviTaxInvoicing',
        'fields' => array(),
        'grouping' => 'contact-fields',
     ),
  The names and number of taxes is dynamic so I'm pushing them into the array via a select statement from the database. The upside is now, I have new checkbox fields in my report criteria.
   
  
• The next thing I need to do is get the related data attached to the report.
  
  As I understand it, my array of fields should now be bound to a data access object that I created called CRM_Core_DAO_CiviTaxInvoicing. This is defined in a file called CiviTaxInvoicing.php that lives in CRM/Core/DAO/. I modeled it after Contact.php which defines the CRM_Contact_DAO_Contact data access object. The problem is, I don't know how to test it and when I try to use it as I expect it should work, I get errors that really don't tell me anything of use. 

So, does anyone have some tips or direction I can follow to bind my info to the transactions so I can produce the detailed transaction report with tax info in it?

Thanks,
Andrew

Thanks Coleman for the reply but you can't name tables in an extension with civicrm_ MySQL will try to run a routine that checks log tables for every table with the prefix "civicrm_" and it will error out. More about that here: http://forum.civicrm.org/index.php/topic,27095.msg115325.html#msg115325

Ok, low and behold, I'm making some progress here 

My DAO is working although I'd be a heck of a lot happier if I could figure out why the alias is _invoicing_civireport instead of something with a little more of the name; ie: civi_tax_invoicing_civireport but never the less I can work with it because I now have $this->_aliases['civi_tax_invoicing'] working in my query.

So the big blockade was that there was a conflict between the character sets that my custom database tables had taken and the ones that CiviCRM created. My tables were utf8_general_ci and CiviCRM tables were utf8_unicode_ci. The fix for now is to amend my LEFT join as follows:


$this->_from .= "
    LEFT JOIN civi_tax_invoicing {$this->_aliases['civi_tax_invoicing']}
    ON {$this->_aliases['civicrm_contribution']}.invoice_id = {$this->_aliases['civi_tax_invoicing']}.invoice_id COLLATE utf8_unicode_ci";
   

   

The real fix will be to make sure that my tables share the same character set as the main tables.

So the beautiful thing about this is that because my custom data access object is indeed working as soon as I bring in one of the fields that I added to the fields array into the report via the checkbox, it pops up in my report. Prior to this breakthrough I was getting blank errors with no descriptions. I am super psyched about this! Woo Hoo!

I still have some tricky bits to work through to get my dynamic taxes to pop into the picture but we're looking good!