From bf45dbe8cd559230d4bb042a1f714fc6488d88d3 Mon Sep 17 00:00:00 2001 From: Pradeep Nayak Date: Wed, 24 Jul 2013 13:09:35 +0530 Subject: [PATCH] -- worked on CRM-12844 ---------------------------------------- * CRM-12844: Automated recurring contribution "civicrm_line_item" records have wrong financial type id http://issues.civicrm.org/jira/browse/CRM-12844 --- CRM/Contribute/BAO/Contribution.php | 3 +- CRM/Core/Payment/BaseIPN.php | 20 +-- CRM/Price/BAO/LineItem.php | 5 +- CRM/Upgrade/Incremental/php/FourThree.php | 134 ++++++++++++++++++++ CRM/Upgrade/Incremental/sql/4.3.6.mysql.tpl | 25 +++- 5 files changed, 175 insertions(+), 12 deletions(-) diff --git a/CRM/Contribute/BAO/Contribution.php b/CRM/Contribute/BAO/Contribution.php index b41d2c0f0c..f4f22a1ef7 100644 --- a/CRM/Contribute/BAO/Contribution.php +++ b/CRM/Contribute/BAO/Contribution.php @@ -2848,7 +2848,8 @@ WHERE contribution_id = %1 "; $params['trxnParams']['total_amount'] = - $params['total_amount']; } - elseif ($params['prevContribution']->contribution_status_id == array_search('Pending', $contributionStatus)) { + elseif ($params['prevContribution']->contribution_status_id == array_search('Pending', $contributionStatus) + && $params['prevContribution']->is_pay_later) { $financialTypeID = CRM_Utils_Array::value('financial_type_id', $params) ? $params['financial_type_id'] : $params['prevContribution']->financial_type_id; if ($params['contribution']->contribution_status_id == array_search('Cancelled', $contributionStatus)) { $params['trxnParams']['to_financial_account_id'] = NULL; diff --git a/CRM/Core/Payment/BaseIPN.php b/CRM/Core/Payment/BaseIPN.php index cb96a8b48a..f72bbd0738 100644 --- a/CRM/Core/Payment/BaseIPN.php +++ b/CRM/Core/Payment/BaseIPN.php @@ -161,7 +161,7 @@ class CRM_Core_Payment_BaseIPN { //add lineitems for recurring payments if (CRM_Utils_Array::value('contributionRecur', $objects) && $objects['contributionRecur']->id && $addLineItems) { - $this->addrecurLineItems($objects['contributionRecur']->id, $contribution->id); + $this->addrecurLineItems($objects['contributionRecur']->id, $contribution->id, CRM_Core_DAO::$_nullArray); } if (!CRM_Utils_Array::value('skipComponentSync', $input)) { @@ -219,7 +219,7 @@ class CRM_Core_Payment_BaseIPN { //add lineitems for recurring payments if (CRM_Utils_Array::value('contributionRecur', $objects) && $objects['contributionRecur']->id && $addLineItems) { - $this->addrecurLineItems($objects['contributionRecur']->id, $contribution->id); + $this->addrecurLineItems($objects['contributionRecur']->id, $contribution->id, CRM_Core_DAO::$_nullArray); } if (!CRM_Utils_Array::value('skipComponentSync', $input)) { @@ -465,7 +465,7 @@ LIMIT 1;"; //add lineitems for recurring payments if (CRM_Utils_Array::value('contributionRecur', $objects) && $objects['contributionRecur']->id && $addLineItems) { - $this->addrecurLineItems($objects['contributionRecur']->id, $contribution->id); + $this->addrecurLineItems($objects['contributionRecur']->id, $contribution->id, $input); } // next create the transaction record @@ -483,8 +483,8 @@ LIMIT 1;"; if ($contribution->id) { $contributionStatuses = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name'); - if (!$input['prevContribution']->is_pay_later && - $input['prevContribution']->contribution_status_id == array_search('Pending', $contributionStatuses)) { + if ((!$input['prevContribution'] && $paymentProcessorId) || (!$input['prevContribution']->is_pay_later && + $input['prevContribution']->contribution_status_id == array_search('Pending', $contributionStatuses))) { $input['payment_processor'] = $paymentProcessorId; } $input['contribution_status_id'] = array_search('Completed', $contributionStatuses); @@ -750,7 +750,7 @@ LIMIT 1;"; ); } - function addrecurLineItems($recurId, $contributionId) { + function addrecurLineItems($recurId, $contributionId, &$input) { $lineSets = $lineItems = array(); //Get the first contribution id with recur id @@ -765,8 +765,12 @@ LIMIT 1;"; $lineSets[$pricesetID->price_set_id][] = $value; } } - - CRM_Price_BAO_LineItem::processPriceSet($contributionId, $lineSets); + if (!empty($input)) { + $input['line_item'] = $lineSets; + } + else { + CRM_Price_BAO_LineItem::processPriceSet($contributionId, $lineSets); + } } } } diff --git a/CRM/Price/BAO/LineItem.php b/CRM/Price/BAO/LineItem.php index aefa9abb0c..b84bc727a2 100644 --- a/CRM/Price/BAO/LineItem.php +++ b/CRM/Price/BAO/LineItem.php @@ -116,6 +116,7 @@ class CRM_Price_BAO_LineItem extends CRM_Price_DAO_LineItem { li.price_field_id, li.participant_count, li.price_field_value_id, + li.financial_type_id, pfv.description"; $fromClause = " @@ -158,6 +159,7 @@ class CRM_Price_BAO_LineItem extends CRM_Price_DAO_LineItem { 'html_type' => $dao->html_type, 'description' => $dao->description, 'entity_id' => $entityId, + 'financial_type_id' => $dao->financial_type_id, 'membership_type_id' => $dao->membership_type_id, 'membership_num_terms' => $dao->membership_num_terms, ); @@ -225,8 +227,7 @@ class CRM_Price_BAO_LineItem extends CRM_Price_DAO_LineItem { 'membership_num_terms' => CRM_Utils_Array::value('membership_num_terms', $options[$oid]), 'auto_renew' => CRM_Utils_Array::value('auto_renew', $options[$oid]), 'html_type' => $fields['html_type'], - 'financial_type_id' => CRM_Utils_Array::value( 'financial_type_id', $options[$oid]), - + 'financial_type_id' => CRM_Utils_Array::value('financial_type_id', $options[$oid]), ); if ($values[$oid]['membership_type_id'] && !isset($values[$oid]['auto_renew'])) { $values[$oid]['auto_renew'] = CRM_Core_DAO::getFieldValue('CRM_Member_DAO_MembershipType', $values[$oid]['membership_type_id'], 'auto_renew'); diff --git a/CRM/Upgrade/Incremental/php/FourThree.php b/CRM/Upgrade/Incremental/php/FourThree.php index e0b1da7533..66be1bf9df 100644 --- a/CRM/Upgrade/Incremental/php/FourThree.php +++ b/CRM/Upgrade/Incremental/php/FourThree.php @@ -325,6 +325,14 @@ ADD CONSTRAINT `FK_civicrm_financial_item_contact_id` FOREIGN KEY (`contact_id`) $this->addTask(ts('Upgrade DB to 4.3.5: SQL'), 'task_4_3_x_runSql', $rev); } + function upgrade_4_3_6($rev) { + $this->addTask(ts('Upgrade DB to 4.3.6: SQL'), 'task_4_3_x_runSql', $rev); + // CRM-12844 + // update line_item, financial_trxn and financial_item table for recurring contributions + $this->addTask(ts('Update financial_account_id in financial_trxn table'), 'updateFinancialTrxnData', $rev); + $this->addTask(ts('Update Line Item Data'), 'updateLineItemData', $rev); + } + //CRM-11636 function assignFinancialTypeToPriceRecords() { $upgrade = new CRM_Upgrade_Form(); @@ -861,6 +869,132 @@ ALTER TABLE civicrm_financial_account return TRUE; } + + /** + * Update financial_account_id for bad data in financial_trxn table + * CRM-12844 + * + */ + function updateFinancialTrxnData(CRM_Queue_TaskContext $ctx) { + $sql = "SELECT cc.id contribution_id, cc.contribution_recur_id, cft.payment_processor_id, +cft.id financial_trxn_id, cfi.entity_table, cft.from_financial_account_id, cft.to_financial_account_id + +FROM `civicrm_contribution` cc +LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_id = cc.id +LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id +LEFT JOIN civicrm_entity_financial_trxn ceft1 ON ceft1.financial_trxn_id = ceft.financial_trxn_id +LEFT JOIN civicrm_financial_item cfi ON cfi.id = ceft1.entity_id +WHERE ceft.entity_table = 'civicrm_contribution' AND cc.contribution_recur_id IS NOT NULL +AND ceft1.entity_table = 'civicrm_financial_item' AND cft.id IS NOT NULL AND cft.payment_instrument_id = %1 + +ORDER BY cft.id "; + $paymentInstrument = CRM_Contribute_PseudoConstant::paymentInstrument(); + $param = array(1 => array(array_search('Credit Card', $paymentInstrument), 'Integer')); + $dao = CRM_Core_DAO::executeQuery($sql, $param); + $financialTrxn = array(); + $subsequentPayments = array(); + while ($dao->fetch()) { + if (!array_key_exists($dao->contribution_recur_id, $financialTrxn)) { + $financialTrxn[$dao->contribution_recur_id] = array( + 'from_financial_account_id' => $dao->to_financial_account_id, + 'payment_processor_id' => $dao->payment_processor_id, + $dao->contribution_id => 1, + ); + if (!is_null($dao->from_financial_account_id)) { + $sql = 'UPDATE civicrm_financial_trxn SET from_financial_account_id = NULL WHERE id = %1'; + $params = array(1 => array($dao->financial_trxn_id, 'Integer')); + CRM_Core_DAO::executeQuery($sql, $params); + } + } + elseif (!array_key_exists($dao->contribution_id, $financialTrxn[$dao->contribution_recur_id])) { + if (($dao->entity_table == 'civicrm_line_item' && $dao->to_financial_account_id == $financialTrxn[$dao->contribution_recur_id]['from_financial_account_id']) + || ($dao->entity_table == 'civicrm_financial_trxn' && $dao->from_financial_account_id == $financialTrxn[$dao->contribution_recur_id]['from_financial_account_id'])) { + continue; + } + $subsequentPayments[$dao->contribution_recur_id][$dao->entity_table][] = $dao->financial_trxn_id; + } + } + foreach ($subsequentPayments as $key => $value) { + foreach ($value as $table => $val) { + if ($table == 'civicrm_financial_trxn') { + $field = 'from_financial_account_id'; + } + else { + $field = 'to_financial_account_id'; + } + $sql = "UPDATE civicrm_financial_trxn SET $field = " . $financialTrxn[$dao->contribution_recur_id]['from_financial_account_id'] . ', +payment_processor_id = ' . $financialTrxn[$dao->contribution_recur_id]['payment_processor_id'] . ' WHERE +id IN (' . implode(',', $val) . ')'; + CRM_Core_DAO::executeQuery($sql); + } + } + return TRUE; + } + + /** + * Update financial_account_id for bad data in financial_trxn table + * CRM-12844 + * + */ + function updateLineItemData(CRM_Queue_TaskContext $ctx) { + $sql = "SELECT cc.id contribution_id, cc.contribution_recur_id, +cc.financial_type_id contribution_financial_type, +cli.financial_type_id line_financial_type_id, +cli.price_field_id, cli.price_field_value_id, cli.label, cli.id line_item_id, +cfi.financial_account_id +FROM `civicrm_line_item` cli +LEFT JOIN civicrm_contribution cc ON cc.id = cli.entity_id +LEFT JOIN civicrm_financial_item cfi ON cfi.entity_id = cli.id +LEFT JOIN civicrm_price_field cpf ON cpf.id = cli.price_field_id +LEFT JOIN civicrm_price_set cps ON cps.id = cpf.price_set_id +LEFT JOIN civicrm_price_field_value cpfv ON cpfv.id = cli.price_field_value_id +WHERE cfi.entity_table = 'civicrm_line_item' +AND cli.entity_table = 'civicrm_contribution' +AND cps.is_quick_config = 1 AND cc.contribution_recur_id IS NOT NULL +ORDER BY cli.id"; + $dao = CRM_Core_DAO::executeQuery($sql); + $financialTrxn = $subsequentPayments = array(); + while ($dao->fetch()) { + if (!array_key_exists($dao->contribution_recur_id, $financialTrxn)) { + $financialTrxn[$dao->contribution_recur_id] = array( + 'price_field_id' => $dao->price_field_id, + 'price_field_value_id' => $dao->price_field_value_id, + 'label' => $dao->label, + 'financial_account_id' => $dao->financial_account_id, + $dao->contribution_id => 1, + ); + } + else { + if ($dao->price_field_value_id == $financialTrxn[$dao->contribution_recur_id]['price_field_value_id']) { + continue; + } + $subsequentPayments[$dao->contribution_recur_id][] = $dao->line_item_id; + } + } + foreach ($subsequentPayments as $key => $value) { + $sql = "UPDATE civicrm_line_item cli +LEFT JOIN civicrm_financial_item cfi ON cli.id = cfi.entity_id +SET +cli.label = %1, +cli.price_field_id = %2, +cli.price_field_value_id = %3, +cfi.financial_account_id = %4, +cfi.description = %5, +cli.financial_type_id = %6 +WHERE cfi.entity_table = 'civicrm_line_item' +AND cli.entity_table = 'civicrm_contribution' AND cli.id IN (" . implode(',', $value). ');'; + $params = array( + 1 => array($financialTrxn[$key]['label'], 'String'), + 2 => array($financialTrxn[$key]['price_field_id'], 'Integer'), + 3 => array($financialTrxn[$key]['price_field_value_id'], 'Integer'), + 4 => array($financialTrxn[$key]['financial_account_id'], 'Integer'), + 5 => array($financialTrxn[$key]['label'], 'String'), + 6 => array($dao->contribution_financial_type, 'Integer'), + ); + CRM_Core_DAO::executeQuery($sql, $params); + } + return TRUE; + } /** * replace contribution_type to financial_type in table diff --git a/CRM/Upgrade/Incremental/sql/4.3.6.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.3.6.mysql.tpl index dcbbb43846..c5205b4646 100644 --- a/CRM/Upgrade/Incremental/sql/4.3.6.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/4.3.6.mysql.tpl @@ -19,4 +19,27 @@ cps.financial_type_id = CASE WHEN ccg.id IS NOT NULL THEN ccg.financial_type_id END -WHERE cps.is_quick_config = 1; \ No newline at end of file +WHERE cps.is_quick_config = 1; + +-- CRM-12844 +-- DELETE bad data +DELETE cli FROM `civicrm_contribution` cc +LEFT JOIN civicrm_line_item cli ON cli.entity_id = cc.id +LEFT JOIN civicrm_financial_item cfi ON cfi.entity_id = cli.id AND cfi.entity_table = 'civicrm_line_item' +LEFT JOIN civicrm_price_field cpf ON cpf.id = cli.price_field_id +LEFT JOIN civicrm_price_set cps ON cps.id = cpf.price_set_id +WHERE cc.contribution_recur_id IS NOT NULL +AND cli.entity_table = 'civicrm_contribution' AND cfi.id IS NULL +AND cps.is_quick_config = 1; + +-- Set from_financial_account_id to null +UPDATE `civicrm_contribution` cc +LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_id = cc.id +LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id +LEFT JOIN civicrm_entity_financial_trxn ceft1 ON ceft1.financial_trxn_id = ceft.financial_trxn_id +LEFT JOIN civicrm_financial_item cfi ON cfi.id = ceft1.entity_id +LEFT JOIN civicrm_entity_financial_account cefa ON cefa.entity_id = cft.payment_processor_id +SET cft.from_financial_account_id = NULL +WHERE ceft.entity_table = 'civicrm_contribution' AND cc.contribution_recur_id IS NOT NULL +AND ceft1.entity_table = 'civicrm_financial_item' AND cft.id IS NOT NULL AND cft.payment_instrument_id = 1 AND cfi.entity_table = 'civicrm_line_item' AND cft.from_financial_account_id IS NOT NULL +AND cefa.entity_table = 'civicrm_payment_processor' AND cefa.financial_account_id = cft.to_financial_account_id; -- 2.25.1