CiviCRM Community Forums (archive)

Hello all,

I'm immersed in the murky world of CRM/Core/DAO/; experimenting with a custom Data Access Object that connects to a non-native database table and although I understand reasonably well some of the commonplace things, when I try to bend it a little, it does feel at times like I'm blindfolded poking randomly at the API with a stick to get a reaction.   

Don't get me wrong... I'm really excited about how the DAO allows me to bind my custom data with civi's data but I need some pointers on how I can bend it a little to fit my requirements.

I have defined my custom database table fields within my DAO and can pull them out nicely however, I need to create dynamic fields where the field name and results of the field are defined by the database results. Here's an example of what I mean:

My non-native DB table is a log of contribution transactions.

• When a transaction occurs my table records the CiviCRM Contribution invoice ID with the value of the contribution as well as any tax charges.
• If the transaction is subject to multiple taxes, a row will be inserted for each tax which includes:
  
  • invoice_id,
  • tax_id, (as defined by a custom tax type table)
  • tax_name,
  • tax_rate,
  • pre_tax,
  • tax_charged
  • post_tax
• My DAO is bound to a report template and has all of the $fields set up appropriately
• I can get my pre_tax value simply by declaring it as a field in my report template.
• I can get the total tax charged by using SUM(tax_charged) which according to my usage just involves it being set up in a report template with 'statistics' => array('sum' => ts('Tax Charged')),
• That's just brilliant because my data comes out nice and neatly in my report.

 
What I'd like to do now is dynamically create/define fields for each tax type in my DAO which would be defined by:
SELECT * FROM table_tax_types WHERE civicrm_contribution.invoice_id = $my_table_invoice_id

Then for each transaction assign the value for tax_charged to each of the dynamic fields of the transaction.

Easy peasy right 

I know this isn't typical stuff but hopefully there are CiviCRM developers who have a bit more experience than I have working in the murky world of data access object who can point me in the right direction.

Thanks,
Andrew