Posted by awasson on October 12, 2011 at 3:02am
I would like to create a working group with the goal of creating a standard and flexible method of adding a tax field to civiCRM for adding tax or taxes to Memberships or Events. We have successfully modified civiCRM 3.x to achieve this but it isn't a clean cut process and I would like to improve on it. I'll be posting my experiences shortly to get the ball rolling.
Note: This is not a “How to” posting for adding a tax to your civiCRM installation. If you stick with it and do a bunch of grunt work, we will come up with a cohesive solution but it isn’t a how to or recipe thread. Maybe, we can add one later.
Comments
Getting the ball rolling
I’ll start things rolling by contributing our experiences and code examples that have been applied and are in a working civiCRM/Drupal setting. In this case it is a Drupal 6.x civiCRM 3.x site.
(Obligatory Disclaimer)
CAUTION: This code comes with absolutely no warranty or troubleshooting whatsoever. If you choose to use it in your website you are entirely responsible for any adverse effects that may occur.
We modified an installation of civiCRM about 18 months ago to include a tax field so that we could charge a tax on memberships and events as required by the Provincial Revenue Agency (or whoever it is that dictates our taxes). We started out with civiCRM 3.1 and now have updated to civiCRM 3.4 and it runs quite nicely. There are some wrinkles here and there but for the most part it has been successful.
We made the following changes:
Added custom templates for civiMember and civiEvent to indicate that the prices were subject to tax.
Created a custom data field that represented the tax rate so that we could go into the admin screens and adjust the rate as required.
Modified civiCRM core code to create a db table to track the tax collected and insert a record for tax collected in each transaction.
Modified civiCRM core reporting code to access the tax collected table and apply the data to the contribution reports
The following sections describe how we achieved the items required to add a tax field and apply it to each Membership or Event transaction and then report each transaction in the admin reporting pages.
Add a statement about tax to the informational page.
This requires a custom template to be created to add the notation about the tax. In the custom installation that we performed we made copies from the CRM part of the civiCRM module of the following and placed them in the /sites/all/modules/civicrm/templates/custom folder:
Contribute -> MembershipBlock.tpl
Event -> EventInfo.tpl
Event -> Register.tpl
We added a string of text that indicated that the event or membership was subject to HST.
in MembershipBlock.tpl we modified the markup where the “Fee” is found in the template (line 80 in civiCRM 3.5.4)
<td style="width: auto;"><span class="bold">{$row.name}
{if ($membershipBlock.display_min_fee AND $context EQ "makeContribution") AND $row.minimum_fee GT 0 }
{if $is_separate_payment OR ! $form.amount.label}
- {$row.minimum_fee|crmMoney}
{else}
{ts 1=$row.minimum_fee|crmMoney}(contribute at least %1 to be eligible for this membership){/ts}
{/if}
{/if}
</span> Plus Harmonized Sales Tax (HST)<br /> <!-- Added to indicate tax -->
{$row.description}
</td>
In EventInfo.tpl we did the edit at line 133 civiCRM 3.4.5
<td class="fee_amount-value right">{$feeBlock.value.$idx|crmMoney} Plus Harmonized Sales Tax (HST)</td>In Register.tpl we did the edit at or around line 83 civiCRM 3.4.5
<div class="crm-section paid_event-section"><div class="label">{$event.fee_label} <span class="marker">*</span></div>
<div class="content">{$form.amount.html} Plus Harmonized Sales Tax (HST)</div> <!-- Added to indicate tax -->
<div class="clear"></div>
</div>
Adding a custom data field for tax percentage
Just as it sounds, we’ll need to add a custom field to civiCRM through Admininister -> Customize -> Custom Data.
We added a Field Set called “Taxes” and set it to be “Used For” “Memberships” “Any”
It doesn’t really matter what we set for “Used For” because we are grabbing it and applying it in code behind civiCRM’s back but that’s what we did.
Inside this field set we added a field called HST which is the type of tax we needed to apply to our memberships and events. It is just a simple text field and we gave it a default value of 12 which is the tax rate (12%).
After we set it up and created the HST tax field, we disabled the fieldset so that none of the admin members could accidentally adjust it without jumping through a few hoops first.
Modify civiCRM core to apply and track tax collected
In order to apply and track tax, we adjusted Confirm.php in both Event and Member to collect the tax field that we set in the previous step and apply it against the value of the transaction. The results were stored in the database with the transaction ID so that they could be called upon at a later date for reporting.
The key code used is in the public function preProcess() and as follows in /sites/all/modules/civicrm/CRM/Contribute/Form/Contribution/Confirm.php (around line 134 civiCRM 3.4.5):
// GET THE HST TAX FROM CUSTOM FIELD
$sql = "SELECT * FROM civicrm_custom_field WHERE label = 'HST'";
$dao = CRM_Core_DAO::executeQuery($sql);
$dao->fetch( );
// Now check to see that we have our record table
$sql = "CREATE TABLE IF NOT EXISTS
tax_invoicing(idint(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,invoiceIDvarchar(255) NOT NULL DEFAULT 0,pre_taxfloat NOT NULL DEFAULT 0,taxfloat NOT NULL DEFAULT 0,tax_chargefloat NOT NULL DEFAULT 0,post_taxfloat NOT NULL DEFAULT 0)ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;";
CRM_Core_DAO::executeQuery($sql);
$sql = "ALTER TABLE
tax_invoicingCHANGEinvoiceIDinvoiceIDVARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '0'";CRM_Core_DAO::executeQuery($sql);
$oldAmount = $this->get( 'amount' );
$invoiceID = $this->get( 'invoiceID' );
$taxCharge = $this->get( 'amount' ) * ($dao->default_value / 100);
$newAmount = $this->get( 'amount' ) + $taxCharge;
// Do we already have a row like this?
$sql = "SELECT * FROM tax_invoicing WHERE invoiceID = '".$invoiceID."'";
$doit = CRM_Core_DAO::executeQuery($sql);
$doit->fetch();
if (!isset($doit->invoiceID) OR empty($doit->invoiceID)) {
// Insert into tracking table
$sql = "INSERT INTO
tax_invoicing(invoiceID,pre_tax,tax,post_tax,tax_charge) VALUES('".$invoiceID."', '".$oldAmount."', '".$dao->default_value."', '".$newAmount."', '".$taxCharge."');";
CRM_Core_DAO::executeQuery($sql);
}
$this->_params['amount' ] = $newAmount;
For Event it is a little different. We made the following edits to sites/all/modules/civicrm/CRM/Event/Form/Registration/Confirm.php (around line 175 civiCRM 3.4.5):
if (!isset($_SESSION['tax_added']) OR (isset($_SESSION['tax_added']) AND $_SESSION['tax_added'] != true)) {
// GET THE HST TAX FROM CUSTOM FIELD
$sql = "SELECT * FROM civicrm_custom_field WHERE label = 'HST'";
$dao = CRM_Core_DAO::executeQuery($sql);
$dao->fetch( );
// Now check to see that we have our record table
$sql = "CREATE TABLE IF NOT EXISTS
tax_invoicing(idint(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,invoiceIDvarchar(255) NOT NULL DEFAULT 0,pre_taxfloat NOT NULL DEFAULT 0,taxfloat NOT NULL DEFAULT 0,tax_chargefloat NOT NULL DEFAULT 0,post_taxfloat NOT NULL DEFAULT 0)ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;";
CRM_Core_DAO::executeQuery($sql);
$sql = "ALTER TABLE
tax_invoicingCHANGEinvoiceIDinvoiceIDVARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '0'";CRM_Core_DAO::executeQuery($sql);
$oldAmount = $this->_params[0]['amount'];
$invoiceID = $this->_params[0]['invoiceID'];
$taxCharge = $this->_params[0]['amount'] * ($dao->default_value / 100);
$newAmount = $this->_params[0]['amount'] + $taxCharge;
// Do we already have a row like this?
$sql = "SELECT * FROM tax_invoicing WHERE invoiceID = '".$invoiceID."'";
$doit = CRM_Core_DAO::executeQuery($sql);
$doit->fetch();
if (!isset($doit->invoiceID) OR empty($doit->invoiceID)) {
// Insert into tracking table
$sql = "INSERT INTO
tax_invoicing(invoiceID,pre_tax,tax,post_tax,tax_charge) VALUES('".$invoiceID."', '".$oldAmount."', '".$dao->default_value."', '".$newAmount."', '".$taxCharge."');";
CRM_Core_DAO::executeQuery($sql);
}
$this->_params[0]['amount'] = $newAmount;
$_SESSION['tax_added'] = true;
} else if (isset($_SESSION['tax_added']) AND $_SESSION['tax_added'] == true) {
unset($_SESSION['tax_added']);
}
Changes to provide reporting of pre-tax, tax amount and total amount
In order to include the tax amount in the detailed contribution report we modified the Detail.php file at /sites/all/modules/civicrm/report/Form/Contribute/Detail.php
We added our fields to the headers in the report and then added our column for pre-tax, tax-amount and total.
I am just reviewing the changes to this file and will update it as soon as possible. Hopefully in the next couple of days.
NEXT STEPS
Hopefully this has inspired some like minded developers to throw their virtual hat in the ring so that we can improve upon our effort and come up with an unencumbered method or module to add a tax field to civiCRM. Personally, I would like to see this become a module so that we can activate and administrate it as needed with tools to adjust the tax rate with flexible reporting.
I look forward to seeing if we can get some momentum going.
Cheers,
Andrew Wasson
Including Tax in the Reporting pages
We provided reporting for tax collected in the Contributions Detailed Report page. We added a couple of columns so that we could show the pre-tax cost, tax collected, transaction fee and the total charge. In order to accomplish this we had to adjust the Detailed.php file and we also had to create a CRM_Core_DAO_TaxInvoicing object which we describe in a file called TaxInvoicing.php
First here is a copy of our Detailed.php file which is found at: /sites/all/modules/civicrm/CRM/Report/Form/Contribute/Detail.php
NOTE: I've commented our changes with LUNA so that I can easily find and review the changes we made.
<?php
/*
+--------------------------------------------------------------------+
| CiviCRM version 3.4 |
+--------------------------------------------------------------------+
| Copyright CiviCRM LLC (c) 2004-2011 |
+--------------------------------------------------------------------+
| This file is a part of CiviCRM. |
| |
| CiviCRM is free software; you can copy, modify, and distribute it |
| under the terms of the GNU Affero General Public License |
| Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
| |
| CiviCRM is distributed in the hope that it will be useful, but |
| WITHOUT ANY WARRANTY; without even the implied warranty of |
| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
| See the GNU Affero General Public License for more details. |
| |
| You should have received a copy of the GNU Affero General Public |
| License and the CiviCRM Licensing Exception along |
| with this program; if not, contact CiviCRM LLC |
| at info[AT]civicrm[DOT]org. If you have questions about the |
| GNU Affero General Public License or the licensing of CiviCRM, |
| see the CiviCRM license FAQ at http://civicrm.org/licensing |
+--------------------------------------------------------------------+
*/
/**
*
* @package CRM
* @copyright CiviCRM LLC (c) 2004-2011
* $Id$
*
*/
require_once 'CRM/Report/Form.php';
require_once 'CRM/Contribute/PseudoConstant.php';
class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form {
protected $_addressField = false;
protected $_emailField = false;
protected $_summary = null;
protected $_customGroupExtends = array( 'Contribution' );
function __construct( ) {
$this->_columns =
array( 'civicrm_contact' =>
array( 'dao' => 'CRM_Contact_DAO_Contact',
'fields' =>
array( 'sort_name' =>
array( 'title' => ts( 'Contact Name' ),
'required' => true,
'no_repeat' => true ),
'id' =>
array( 'no_display' => true,
'required' => true, ), ),
'filters' =>
array('sort_name' =>
array( 'title' => ts( 'Contact Name' ),
'operator' => 'like' ),
'id' =>
array( 'title' => ts( 'Contact ID' ),
'no_display' => true ), ),
'grouping'=> 'contact-fields',
),
'civicrm_email' =>
array( 'dao' => 'CRM_Core_DAO_Email',
'fields' =>
array( 'email' =>
array( 'title' => ts( 'Email' ),
'default' => true,
'no_repeat' => true
), ),
'grouping' => 'contact-fields',
),
'civicrm_phone' =>
array( 'dao' => 'CRM_Core_DAO_Phone',
'fields' =>
array( 'phone' =>
array( 'title' => ts( 'Phone' ),
'default' => true,
'no_repeat' => true
), ),
'grouping' => 'contact-fields',
),
// LUNA Added For TAX Reporting
'tax_invoicing' =>
array( 'dao' => 'CRM_Core_DAO_TaxInvoicing',
'fields' =>
array(
'pre_tax' => array('title' => ts('Pre Tax'),
'default' => true),
'tax_charge' => array('title' => ts('Tax Charged'),
'default' => true),
),
'grouping' => 'contact-fields',
),
// LUNA END Added For TAX Reporting
'civicrm_contribution' =>
array( 'dao' => 'CRM_Contribute_DAO_Contribution',
'fields' =>
array(
'contribution_id' => array(
'name' => 'id',
'no_display' => true,
'required' => true,
),
'contribution_type_id' => array( 'title' => ts('Contribution Type'),
'default' => true,
),
'payment_instrument_id' => array( 'title' => ts('Payment Type'),
),
'trxn_id' => null,
'receive_date' => array( 'default' => true ),
'receipt_date' => null,
'fee_amount' => null,
'net_amount' => null,
'total_amount' => array( 'title' => ts( 'Amount' ),
'required' => true,
'statistics' =>
array('sum' => ts( 'Amount' )),
),
),
'filters' =>
array( 'receive_date' =>
array( 'operatorType' => CRM_Report_Form::OP_DATE ),
'contribution_type_id' =>
array( 'title' => ts( 'Contribution Type' ),
'operatorType' => CRM_Report_Form::OP_MULTISELECT,
'options' => CRM_Contribute_PseudoConstant::contributionType( )
),
'payment_instrument_id' =>
array( 'title' => ts( 'Payment Type' ),
'operatorType' => CRM_Report_Form::OP_MULTISELECT,
'options' => CRM_Contribute_PseudoConstant::paymentInstrument( )
),
'contribution_status_id' =>
array( 'title' => ts( 'Contribution Status' ),
'operatorType' => CRM_Report_Form::OP_MULTISELECT,
'options' => CRM_Contribute_PseudoConstant::contributionStatus( ),
'default' => array( 1 ),
),
'total_amount' =>
array( 'title' => ts( 'Contribution Amount' ) ),
),
'grouping'=> 'contri-fields',
),
'civicrm_group' =>
array( 'dao' => 'CRM_Contact_DAO_GroupContact',
'alias' => 'cgroup',
'filters' =>
array( 'gid' =>
array( 'name' => 'group_id',
'title' => ts( 'Group' ),
'operatorType' => CRM_Report_Form::OP_MULTISELECT,
'group' => true,
'options' => CRM_Core_PseudoConstant::group( ) ), ), ),
// LUNA ADDED FOR TAX REPORTING
'civicrm_tag' =>
array( 'dao' => 'CRM_Core_DAO_Tag',
'filters' =>
array( 'tid' =>
array( 'name' => 'tag_id',
'title' => ts( 'Tag' ),
'tag' => true,
'operatorType' => CRM_Report_Form::OP_MULTISELECT,
'options' => CRM_Core_PseudoConstant::tag( )
),
),
),
// LUNA END ADDED FOR TAX REPORTING
'civicrm_contribution_ordinality' =>
array( 'dao' => 'CRM_Contribute_DAO_Contribution',
'alias' => 'cordinality',
'filters' =>
array( 'ordinality' =>
array( 'title' => ts( 'Contribution Ordinality' ),
'operatorType' => CRM_Report_Form::OP_MULTISELECT,
'options' => array( 0 => 'First by Contributor',
1 => 'Second or Later by Contributor') ), ), ),
) + $this->addAddressFields(false);
$this->_tagFilter = true;
parent::__construct( );
}
function preProcess( ) {
parent::preProcess( );
}
function select( ) {
$select = array( );
$this->_columnHeaders = array( );
foreach ( $this->_columns as $tableName => $table ) {
if ( array_key_exists('fields', $table) ) {
foreach ( $table['fields'] as $fieldName => $field ) {
if ( CRM_Utils_Array::value( 'required', $field ) ||
CRM_Utils_Array::value( $fieldName, $this->_params['fields'] ) ) {
if ( $tableName == 'civicrm_address' ) {
$this->_addressField = true;
} else if ( $tableName == 'civicrm_email' ) {
$this->_emailField = true;
}
// only include statistics columns if set
if ( CRM_Utils_Array::value('statistics', $field) ) {
foreach ( $field['statistics'] as $stat => $label ) {
switch (strtolower($stat)) {
case 'sum':
$select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] =
$field['type'];
$this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
break;
case 'count':
$select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
$this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
break;
case 'avg':
$select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] =
$field['type'];
$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
$this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
break;
}
}
} else {
$select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
$this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
$this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value( 'type', $field );
}
}
}
}
}
$this->_select = "SELECT " . implode( ', ', $select ) . " ";
}
function from( ) {
$this->_from = null;
$this->_from = "
FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0";
if ( !empty($this->_params['ordinality_value']) ) {
$this->_from .= "
INNER JOIN (SELECT c.id, IF(COUNT(oc.id) = 0, 0, 1) AS ordinality FROM civicrm_contribution c LEFT JOIN civicrm_contribution oc ON c.contact_id = oc.contact_id AND oc.receive_date < c.receive_date GROUP BY c.id) {$this->_aliases['civicrm_contribution_ordinality']}
ON {$this->_aliases['civicrm_contribution_ordinality']}.id = {$this->_aliases['civicrm_contribution']}.id";
}
// LUNA LEFT JOIN BELOW ON tax_invoicing TABLE FOR TAX REPORTING
$this->_from .= "
LEFT JOIN tax_invoicing {$this->_aliases['tax_invoicing']}
ON {$this->_aliases['civicrm_contribution']}.invoice_id = {$this->_aliases['tax_invoicing']}.invoiceID
LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
{$this->_aliases['civicrm_phone']}.is_primary = 1)";
if ( $this->_addressField OR ( !empty($this->_params['state_province_id_value']) OR !empty($this->_params['country_id_value']) ) ) {
$this->_from .= "
LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
{$this->_aliases['civicrm_address']}.is_primary = 1\n";
}
if ( $this->_emailField ) {
$this->_from .= "
LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
{$this->_aliases['civicrm_email']}.is_primary = 1\n";
}
}
function groupBy( ) {
$this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.id ";
}
function orderBy( ) {
$this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id ";
}
function statistics( &$rows ) {
$statistics = parent::statistics( $rows );
$select = "
SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
SUM( {$this->_aliases['civicrm_contribution']}.total_amount ) as amount,
ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg
";
$sql = "{$select} {$this->_from} {$this->_where}";
$dao = CRM_Core_DAO::executeQuery( $sql );
if ( $dao->fetch( ) ) {
$statistics['counts']['amount'] = array( 'value' => $dao->amount,
'title' => 'Total Amount',
'type' => CRM_Utils_Type::T_MONEY );
$statistics['counts']['avg'] = array( 'value' => $dao->avg,
'title' => 'Average',
'type' => CRM_Utils_Type::T_MONEY );
}
return $statistics;
}
function postProcess( ) {
// get the acl clauses built before we assemble the query
$this->buildACLClause( $this->_aliases['civicrm_contact'] );
parent::postProcess( );
}
function alterDisplay( &$rows ) {
// custom code to alter rows
$checkList = array();
$entryFound = false;
$display_flag = $prev_cid = $cid = 0;
$contributionTypes = CRM_Contribute_PseudoConstant::contributionType( );
$paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument( );
foreach ( $rows as $rowNum => $row ) {
if ( !empty($this->_noRepeats) && $this->_outputMode != 'csv' ) {
// don't repeat contact details if its same as the previous row
if ( array_key_exists('civicrm_contact_id', $row ) ) {
if ( $cid = $row['civicrm_contact_id'] ) {
if ( $rowNum == 0 ) {
$prev_cid = $cid;
} else {
if( $prev_cid == $cid ) {
$display_flag = 1;
$prev_cid = $cid;
} else {
$display_flag = 0;
$prev_cid = $cid;
}
}
if ( $display_flag ) {
foreach ( $row as $colName => $colVal ) {
if ( in_array($colName, $this->_noRepeats) ) {
unset($rows[$rowNum][$colName]);
}
}
}
$entryFound = true;
}
}
}
// convert display name to links
if ( array_key_exists('civicrm_contact_sort_name', $row) &&
CRM_Utils_Array::value( 'civicrm_contact_sort_name', $rows[$rowNum] ) &&
array_key_exists('civicrm_contact_id', $row) ) {
$url = CRM_Utils_System::url( "civicrm/contact/view" ,
'reset=1&cid=' . $row['civicrm_contact_id'],
$this->_absoluteUrl );
$rows[$rowNum]['civicrm_contact_sort_name_link' ] = $url;
$rows[$rowNum]['civicrm_contact_sort_name_hover'] =
ts("View Contact Summary for this Contact.");
}
if ( $value = CRM_Utils_Array::value( 'civicrm_contribution_contribution_type_id', $row ) ) {
$rows[$rowNum]['civicrm_contribution_contribution_type_id'] = $contributionTypes[$value];
$entryFound = true;
}
if ( $value = CRM_Utils_Array::value( 'civicrm_contribution_payment_instrument_id', $row ) ) {
$rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
$entryFound = true;
}
if ( ( $value = CRM_Utils_Array::value( 'civicrm_contribution_total_amount_sum', $row ) ) &&
CRM_Core_Permission::check( 'access CiviContribute' ) ) {
$url = CRM_Utils_System::url( "civicrm/contact/view/contribution" ,
"reset=1&id=".$row['civicrm_contribution_contribution_id']."&cid=".$row['civicrm_contact_id']."&action=view&context=contribution&selectedChild=contribute",
$this->_absoluteUrl );
$rows[$rowNum]['civicrm_contribution_total_amount_sum_link'] = $url;
$rows[$rowNum]['civicrm_contribution_total_amount_sum_hover'] =
ts("View Details of this Contribution.");
$entryFound = true;
}
$entryFound = $this->alterDisplayAddressFields($row,$rows,$rowNum,'contribute/detail','List all contribution(s) for this ')?true:$entryFound;
// skip looking further in rows, if first row itself doesn't
// have the column we need
if ( !$entryFound ) {
break;
}
$lastKey = $rowNum;
}
}
// LUNA ADDED FOR TAX REPORTING
function modifyColumnHeaders( ) {
$this->_columnHeaders['tax_invoicing_pre_tax']['type'] = 1024;
$this->_columnHeaders['tax_invoicing_tax_charge']['type'] = 1024;
$oldHeaders = $this->_columnHeaders;
//print_r($oldHeaders);
unset($this->_columnHeaders['tax_invoicing_pre_tax']);
unset($this->_columnHeaders['tax_invoicing_tax_charge']);
unset($this->_columnHeaders['civicrm_contribution_fee_amount']);
if (isset($this->_columnHeaders['civicrm_contribution_net_amount'])) {
unset($this->_columnHeaders['civicrm_contribution_net_amount']);
}
unset($this->_columnHeaders['civicrm_contribution_total_amount_sum']);
$this->_columnHeaders['tax_invoicing_pre_tax'] = $oldHeaders['tax_invoicing_pre_tax'];
$this->_columnHeaders['tax_invoicing_tax_charge'] = $oldHeaders['tax_invoicing_tax_charge'];
$this->_columnHeaders['civicrm_contribution_fee_amount'] = $oldHeaders['civicrm_contribution_fee_amount'];
if (isset($this->_columnHeaders['civicrm_contribution_net_amount'])) {
$this->_columnHeaders['civicrm_contribution_net_amount'] = $oldHeaders['civicrm_contribution_net_amount'];
}
$this->_columnHeaders['civicrm_contribution_total_amount_sum'] = $oldHeaders['civicrm_contribution_total_amount_sum'];
}
// LUNA END ADDED FOR TAX REPORTING
}
In order for the changes detailed above to work, there must be a CRM_Core_DAO_TaxInvoicing object which is described in a file called TaxInvoicing.php in /sites/all/modules/civicrm/CRM/Core/DAO/TaxInvoicing.php. This file does not exist in civiCRM so it is up to the developer to create this file.
The contents of this file is as follows:
<?php/*
+--------------------------------------------------------------------+
| CiviCRM version 3.4 |
+--------------------------------------------------------------------+
| Copyright CiviCRM LLC (c) 2004-2010 |
+--------------------------------------------------------------------+
| This file is a part of CiviCRM. |
| |
| CiviCRM is free software; you can copy, modify, and distribute it |
| under the terms of the GNU Affero General Public License |
| Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
| |
| CiviCRM is distributed in the hope that it will be useful, but |
| WITHOUT ANY WARRANTY; without even the implied warranty of |
| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
| See the GNU Affero General Public License for more details. |
| |
| You should have received a copy of the GNU Affero General Public |
| License and the CiviCRM Licensing Exception along |
| with this program; if not, contact CiviCRM LLC |
| at info[AT]civicrm[DOT]org. If you have questions about the |
| GNU Affero General Public License or the licensing of CiviCRM, |
| see the CiviCRM license FAQ at http://civicrm.org/licensing |
+--------------------------------------------------------------------+
*/
/**
*
* @package CRM
* @copyright CiviCRM LLC (c) 2004-2010
* $Id$
*
*/
require_once 'CRM/Core/DAO.php';
require_once 'CRM/Utils/Type.php';
class CRM_Core_DAO_TaxInvoicing extends CRM_Core_DAO
{
/**
* static instance to hold the table name
*
* @var string
* @static
*/
static $_tableName = 'tax_invoicing';
/**
* static instance to hold the field values
*
* @var array
* @static
*/
static $_fields = null;
/**
* static instance to hold the FK relationships
*
* @var string
* @static
*/
static $_links = null;
/**
* static instance to hold the values that can
* be imported / apu
*
* @var array
* @static
*/
static $_import = null;
/**
* static instance to hold the values that can
* be exported / apu
*
* @var array
* @static
*/
static $_export = null;
/**
* static value to see if we should log any modifications to
* this table in the civicrm_log table
*
* @var boolean
* @static
*/
static $_log = false;
/**
* Unique Tax Invoice ID
*
* @var int unsigned
*/
public $id;
/**
* InvoiceID
*
* @var int unsigned
*/
public $invoiceID;
/**
* Before tax is added.
*
* @var int unsigned
*/
public $pre_tax;
/**
* Tax amount charged
*
* @var boolean
*/
public $tax;
/**
* After tax is charged
*
* @var boolean
*/
public $post_tax;
function __construct()
{
parent::__construct();
}
/**
* return foreign links
*
* @access public
* @return array
*/
function &links()
{
if (!(self::$_links)) {
self::$_links = array(
'tax_invoicing_id' => 'tax_invoicing:id',
);
}
return self::$_links;
}
/**
* returns all the column names of this table
*
* @access public
* @return array
*/
function &fields()
{
if (!(self::$_fields)) {
self::$_fields = array(
'id' => array(
'name' => 'id',
'type' => CRM_Utils_Type::T_INT,
'required' => true,
) ,
'invoiceID' => array(
'name' => 'invoiceID',
'type' => CRM_Utils_Type::T_INT,
) ,
'pre_tax' => array(
'name' => 'pre_tax',
'type' => CRM_Utils_Type::T_INT,
) ,
'tax' => array(
'name' => 'tax',
'type' => CRM_Utils_Type::T_INT,
) ,
'post_tax' => array(
'name' => 'post_tax',
'type' => CRM_Utils_Type::T_INT,
) ,
);
}
return self::$_fields;
}
/**
* returns the names of this table
*
* @access public
* @return string
*/
function getTableName()
{
return self::$_tableName;
}
/**
* returns if this table needs to be logged
*
* @access public
* @return boolean
*/
function getLog()
{
return self::$_log;
}
/**
* returns the list of fields that can be imported
*
* @access public
* return array
*/
function &import($prefix = false)
{
if (!(self::$_import)) {
self::$_import = array();
$fields = & self::fields();
foreach($fields as $name => $field) {
if (CRM_Utils_Array::value('import', $field)) {
if ($prefix) {
self::$_import['tax_invoicing'] = & $fields[$name];
} else {
self::$_import[$name] = & $fields[$name];
}
}
}
}
return self::$_import;
}
/**
* returns the list of fields that can be exported
*
* @access public
* return array
*/
function &export($prefix = false)
{
if (!(self::$_export)) {
self::$_export = array();
$fields = & self::fields();
foreach($fields as $name => $field) {
if (CRM_Utils_Array::value('export', $field)) {
if ($prefix) {
self::$_export['tax_invoicing'] = & $fields[$name];
} else {
self::$_export[$name] = & $fields[$name];
}
}
}
}
return self::$_export;
}
}
Now you should have the ability to add Pre Tax and Tax Charged to your report criteria in your Detailed Contribution Report.
Multiple Tax Rates
So I was wondering how much more complicated you think it would be to change this from using one single tax rate to using multiple tax rates based on province?
What I'm trying to achieve is a system where the user is charged tax based on their provincial sales tax rate or just GST if they live outside Canada.
However a more general solution might be to allow for tax rules based on fields rather than a set rate (however I'd say this is like a version 3 or 4 kind of thing.)
We'll have to add a mechanism...
Hi Dave,
We'll have to add a mechanism to identify and activate the appropriate tax rate for the region in question. That would be something that would need to be added to the code to the modification in Confirm.php.
Off the top of my head (in broad terms) I would create a series of tax entries in the custom data group to account for every province + a general HST rate for those outside of Canada. Then I would have to cut some code that matched the province/state to the tax rate. That would take place in the mods we made to Confirm.php.
I would like to see this evolve into something where we can add as many tax rates as we like and then match them to regions from within the administer -> configure -> global settings -> tax rates or something like that.
Andrew
This is good work! I hate to
This is good work!
I hate to say this but region by region taxes may be better done in Drupal's Rules module as the conditions for taxes can vary quite a lot from country to country and region to region.
For example, setting aside the fact that most NPOs are sales tax exempt in the US, in the US you can have state, county and city level sales taxes. In fact you can have cities that cross county borders and therefore have different sales taxes in the same city (I know it's painful). I believe this was the a large motivating factor in Ubercart moving taxes to it's Conditional Actions module and now using Rules in Drupal Commerce.
CiviCRM has some rules module integration but the actions are mostly related to Contacts and Mailings (ie bulk e-mail) at this point.
Hook for taxes in CiviCRM
Maybe CiviCRM could handle most simple cases (taxes based on country / state) using an admin page and then add a taxes hook which would allow to define when a tax is applicable and what are the specific rules.
First step i think is to define the database structure for taxes meta data.
So far, what i can think of : id, label, %, weight (which tax is applied first if there is several), country, state, type (event, ...)
For Quebec in Canada, we will have something like (http://en.wikipedia.org/wiki/Taxation_in_Canada) :
And we can quite easily make all the rule for all province in Canada with this structure. Sure it's not the most efficient way to store the data (ex. we need to repeat GST for each province because it's not applicable for all province in Canada - sometimes HST includes it) but it's reasonably easy in that case.
Maybe with column County and City, it will be able to do most of the cases.
We may also need a way to have a default taxe rule if no one is working, so maybe adding a boolean column "default". I am really not sure about this one.
Any thoughts ?
I only see this working for
I only see this working for the most simple of use cases. What if some of the items that you are charging for require different tax rates? Or what if some items require different tax rates in the different jurisdictions in which your constituents reside? I think these are very common scenarios.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Examples ?
@dalin: All right, can you give a concrete example for CiviCRM ? It's not clear to me in which case you will have several items of the same type (event, ...) in the same page with different taxes.
Concerning jurisdiction, it's still a geographic area, right ? So maybe we can abstract this by having another table containing one or several of the following : country, state, county, city, jurisdiction, postal code. Pretty sure it is not the best way to do this in the database. Any suggestion are welcome.
Thanks,
Awesome! Now we're getting some activity : )
@samuelsov: You raise a good point about defining the data and metadata we're collecting. One of the admin guys on civiCRM's support forum (not Lobo) suggested that we use custom data to deal with the tax meta data. For our one-off purposes it was a good idea but moving forward it would be nice to have a civiTax admin section with the ability to add as many taxes and rates as an organization needs. Eventually we'll need to update ours (British Columbia) for PST & GST depending on what the tax laws dictate when they extinguish the HST here.
Collecting and Reporting transaction information is another side of this. We had a fairly shallow requirement so our database table was pretty simple:
$sql = "CREATE TABLE IF NOT EXISTS tax_invoicing (id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
invoiceID varchar(255) NOT NULL DEFAULT 0,
pre_tax float NOT NULL DEFAULT 0,
tax float NOT NULL DEFAULT 0,
tax_charge float NOT NULL DEFAULT 0,
post_tax float NOT NULL DEFAULT 0)
ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;";
This is a good place to start but can also be improved upon.
@dalin: As mentioned, our needs were fairly shallow because we're only dealing with one province and a single tax that is applied to memberships and events. What sort of scenarios are you expecting this module will have to deal with in real world situations?
Andrew
Storing computed taxes
After the metadata structure is defined, i think the most simple structure for storing the actual computed taxes is :
* invoice id (is it contribution_id ?)
* foreign key on the tax meta table
* amount
So, for example, in Quebec, where there are 2 taxes, we will have 2 entry for each invoice.
Another way to do this is to have a dynamic table (like custom group). A new column is added for each new tax. Much more complicated, but probably better performance.
@awasson: Still, i don't get what invoice id is referring to in your case ?
Contribution table column
Contribution table column name: "invoice_id". It's in class CRM_Contribute_Form_Contribution_Confirm (Confirm.php)
In our modification to we put the invoice into a local variable $invoiceID = $this->get( 'invoiceID' ); and then put it in our database table for reporting.
It doesn't have to be done this way but it seemed to make the most sense, so that's how we handled it.
Andrew
Price set as an example
After looking a bit more, we should probably use the same mecanism as price set. Once the price set options are computed, the are stored in civicrm_line_item - linked to an entity_id, entity_table (can be at least civicrm_contribution or civicrm_participant)
We can either use this table, which mean we don't have to change anything for it to appears on receipt and report (needs verification) but we need to change the structure of the table. In the particuliar case of taxes, we don't need to set quantity, unit_price and price_field_value_id but we need to link to the taxe meta data.
Or either create another table on the same spirit.
I will do a little bit more verification to see how well it could work.
invoice_id is a field of
invoice_id is a field of civicrm_contribute so at least we should use civicrm_contribution.id instead as it is more logical and more performant.
We are... That's why we add
We are... That's why we add it into our table so that we can join on invoice_id and pull the data into the reporting page.
For an event the food may be
For an event the food may be taxed at a different rate than the event itself.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
I'm not sure if I've ever
I'm not sure if I've ever seen that sort of criteria for an event. Correct me if I'm wrong as I have limited experience with this sort of thing but usually a meal is either included or not and they roll it all into the gross and then tax it. If on the other hand this is something we need to concern ourselves with it will again another small layer of complexity because we'll need to separate the event cost from the food cost and insert them in our tax_invoicing table. The trick is can we somehow hook the food items in civiEvent for the separate processing?
Keep it simple
Thanks for the example !
My feeling is that we should keep the first version as simple as possible and let hook do more complicated stuff like you mentioned (add ajax helper). So we have to keep your example in mind as a use case we want to be able to do.
Tax Jurisdictions
I think ultimately we'd need a admin panel for setting tax rules because you need to deal with the PST/GST/HST + PEI an Québec charge PST on the GST. Also you have some organizations that have a GST exemption.
I'm working with organizations where the tax rate is based off of not where the purchaser lives but where the event is being held. For an other organization they need to charge sales tax based on where the purchaser lives.
Yes that will add another
Yes that will add another level of complexity to this but since the events are handled by civi and the taxes will be handled by civi as well, it would be just a matter of querying the events information and grabbing the province and mapping it to the appropriate tax. Sounds simple in theory doesn't it... We'll see how it plays out when it's time to try and apply it in practice.
So with that in mind, I'm sure this isn't an edge case and we should be thinking about providing the option to enable event taxes to be applied according to the region that the event occurs in.
You may also need different
You may also need different rules for each item in a price set, which is probably how most people have separate meal line items anyway.
Well I suppose this brings in
Well I suppose this brings in another question, if we are talking about multiple line items, are we then talking about a shopping cart style solution? Could we perhaps approach this through some sort of UC Civi link?
There is already a little
There is already a little module which do sync between ubercart and civicrm - http://drupal.org/project/uc_civicrm
I tend to agree with Dave : maybe for those complex cases, it's better to do it in Drupal because CiviCRM is not a commerce platform.
New financial_item table to handle line items in contributions
The new financial_item table to be introduced in CiviAccounts for CiviCRM 4.2 will provide line items for each item in a price set, for example. More info about the work we are doing on "Accounting integration improvements" is over on the CiviCRM Wiki
Andrew Perry | Director
Community Builders Australia | www.communitybuilders.com.au
Tax Rules applied to a Contribution Type?
Do you envisage that the tax rule could be applied to a Contribution Type, so that every page/price set item that uses that Contribution Type would have the same rules apply?
We are working on CiviAccounts for improved Accounting integration to be introduced in CiviCRM 4.2.
Andrew Perry | Director
Community Builders Australia | www.communitybuilders.com.au
I can't speak for the rest of
I can't speak for the rest of those here but that is the way I would expect it to work. In our hard-wired version we have forced every event and membership to take on a tax.
CiviAccounts
Hi All
Sorry that I missed this post earlier or I could have helped short cut this discussion as we are well on the way to being able to achieve what you want through the CiviAccounts project!
We'd love your help in getting things right to deal with your use case.
joemurray and I are leading the charge with CiviAccounts and one of the key things we are working on is to have flexibility with the way items in Price Sets are treated in the "accounting records" by adding in a new financial_item table.
This financial_item table will be populated with each item in a price_set and can also be populated with an associated tax financial_item. This tax item could in future be determined based on a tax rule or tax set associated with the item in the price set.
If you are willing to help on CiviAccounts, or your organisation is willing to contribute to development of the "tax rules" addition as part of CiviAccounts, then we could look to get this into CiviCRM 4.2!
If you have MySQL Workbench you can download the colour coded data schema that shows how we are proposing to add the financial_item table to CiviCRM 4.2
You'll see in an earlier version (10.2, which unfortunately I didn't colour code so it is harder to see the new tables) that I had proposed a tax_type_revision table that would store the rates applicable for a particular tax at a particular time, given these change. It was outside scope for what we needed to get done for CiviAccounts, but if you can contribute to this work then it would be great to look at bringing this back in!
Looking forward to your comments!
Andrew Perry | Director
Community Builders Australia | www.communitybuilders.com.au
civiAccounts
Andrew,
Thanks for joining in. I'm just taking a break from my desktop (long day so far) but I'll be back shortly and I'll have a look at what you've been up to.
Our work so far has been with civiCRM 3.x so there are likely differences in what we've done but I'm happy to help out as much as I can. As far as I'm concerned the Drupal/civiCRM combination is a real winner but one of the hold-ups in our area is the tax issue so whatever it takes to smooth that out will be worth the effort.
Cheers,
Andrew
Ok, where were we?...
Last week there was a good discussion regarding criteria for moving forward in designing the tax module. So far I think the points are as follows:
Dealing with multiple taxes Multiple Tax Rates, Hook for taxes in CiviCRM, Tax Jurisdictions
Storing computed taxes link
I had a look at some information at civiAccounts and it looks like a great addition to the civiCRM toolset... Hopefully the discussions going on here will have some influence.
So where are we at in terms of defining a civiCRM tax module? Do we prototype a simple module to get started or do we hold off and spec out every last detail?
Andrew
Next steps
I think the next steps would be to decide on an architecture, interface(s) and the use cases you will be supporting in the first, say, two phases.
An early decision is whether this is going to be targetted at Drupal/CiviCRM or potentially just CiviCRM in the future (ie Drupal & Joomla! & WordPress). The CiviCRM extensions framework will likely grow to support .php and .tpl files in extensions. Lobo was guessing that perhaps 20 - 30 hours was required to allow it support Drupal modules that use just CiviCRM api and hooks (with a small bit of changes to them likely needed). They obviously wouldn't be able to depend on Drupal FAPI or contrib modules like rules.
If we can get this module to support the tax regimes for non-profits in Canada it'll probably work for most other places given the complexities we face ;). I'd suggest we aim to handle the following in the next version:
Joe Murray
Update
Hi All,
I thought I would check in and see what the interest level is with the Tax module.
I'm still interested in moving forward and taking the work that we did (described in the first couple of posts), refining it to be more flexible with regard to the comments above and move forward with a proper module. Joe Murray (above) maps out what I think is an achievable set of goals.
I've moved on to civiCRM 4.x but I haven't looked into how it has changed regarding 3rd party extensions and modules. My hope is that it will provide the hooks we need to make this modular. The problem we had with our civiCRM 3.x hack was that we had to insert our code inline within civiCRM's core code. With any luck, the new architecture will allow us to make a helper module where we can override the code with ours.
First on the agenda for me is to review the 4.x changes and methods. Then I'll attempt to prototype the hack we used into a proof of concept module. If anyone wants to help, please by all means join in on the discussion.
Cheers,
Andrew
Wiki page to organize requirements
I created a blog post about this topic at http://civicrm.org/blogs/sarahgladstone/supporting-australian-tax-rules
and related wiki page at: http://wiki.civicrm.org/confluence/display/CRMDOC41/Taxes+and+Fees+for+C...
New requirements page based on CiviCRM 4.3
Parvez Saleh of Veda Consulting has updated the requirements for taxes based on the CiviCRM 4.3 data structures. He's posted a wiki at: http://wiki.civicrm.org/confluence/pages/viewpage.action?pageId=83493410 and a blog post at http://civicrm.org/blogs/parvez/charging-sales-tax-value-added-tax-vat-c...
For anyone who is following
For anyone who is following this thread, I'm just now putting the finishing touches on my CiviCRM Tax Field Extension Module and will post a link for anyone who's interested to download and test it.
Note: This extension module has its own tax rules system and does not use the CiviCRM Financial Accounts API. There is another group working on that approach.
It's designed to work with CiviCRM 4.3.x and higher and does not require any modifications to CiviCRM. You install/activate it, set up taxes, apply them to contribution types and it does the rest.
I have been testing it on a Drupal 7 test site with the Dummy Test Payment Processor and it works exactly as planned but it will need testing to ensure that it works correctly in production.
I'll post a link in the next day or two once I've run a couple more tests on it and written a little documentation.
Cheers,
Andrew
Thanks Andrew, are you at
Thanks Andrew, are you at CiviCon? It would be great to catch up there!
Andrew Perry | Director
Community Builders Australia | www.communitybuilders.com.au
Hi Andrew, I wish I could go
Hi Andrew,
I wish I could go to CiviCon this year but I have some things tying me up locally. There is a slight chance I can make it but from the looks of things I'll have to miss it. There are so many Civi people I chat with electronically and I'd really like to catch up. Next year for sure!
Link to the extension
Ok, as promised, here's the link: https://github.com/awasson/civiTAX
Please do test this extension and provide feedback, bug reports, feature requests to the issue queue (https://github.com/awasson/civiTAX/issues)
I have not fleshed out the documentation for this extension/module but the readme file provides the Cole's Notes on how to acquire, rename and use it in your CiviCRM website.
I've tested it with Drupal 7 - CiviCRM 4.4.x but I'm also interested in hearing how it works with Wordpress or Joomla sites as they are not my expertise.
NOTE: At this time the extension does not add the "after tax" amount to the invoices so please update your templates (particularly the offline one that is emailed) to indicate that the amount invoices is subject to tax. I am currently working on a solution to alter the invoiced amount to reflect the amount that is charged so this may change in the near future.
Cheers,
Andrew
Update
Just a quick note:
I made an update to the code on Github yesterday to handle "pay later" transactions. If you grabbed a copy of it earlier, please grab an updated version. It really only affected one file but I also cleaned up a few things left over from development testing.
Cheers,
Andrew