CRM-20954 Fatal SQL error when completing a partially paid contribution in localized installation

          Details

          • Type: Bug
          • Status: Done/Fixed
          • Priority: Trivial
          • Resolution: Fixed/Completed
          • Affects Version/s: 4.7.16
          • Fix Version/s: 4.7.24
          • Component/s: None
          • Labels:
          • Versioning Impact:
            Patch (backwards-compatible bug fixes)
          • Documentation Required?:
            None
          • Funding Source:
            Contributed Code
          • Verified?:
            No

            Description

            To reproduce :

            • have a CiviCRM localized with translated financial status (civicrm/admin/options)
            • have a partially paid contribution
            • record a new payment with the remaining amount required to complete the payment

            The system will crash with DB Error: syntax error

            Clearly the status_id was not found :

            UPDATE civicrm_financial_item fi LEFT JOIN civicrm_entity_financial_trxn eft ON (eft.entity_id = fi.id AND eft.entity_table = 'civicrm_financial_item') SET status_id = WHERE eft.financial_trxn_id IN (101, 101)

            In the code, we have :

            $financialItemStatus = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_FinancialItem', 'status_id');
 $paidStatus = array_search('Paid', $financialItemStatus);

             

            I'm not even sure if the financial status appear anywhere on the screen for the end user (reports ?) but nothing forbid to do it in the UI (civicrm/admin/options) and it's a bad idea in the code to compare a value that might be translated.

             

             

              Attachments

                Activity

                [CRM-20954] Fatal SQL error when completing a partially paid contribution in localized installation
                Samuel Vanhove added a comment - - edited

                Not sure what is the good practice here.

                • CRM_Core_PseudoConstant::get has an option "localize" but it doesn't seems to work (at least in 4.7.16) – we might want to fix it and use this parameter
                • We could use CRM_Core_PseudoConstant::get with option 'labelColumn' => 'name' : 
                  $financialItemStatus = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_FinancialItem', 'status_id', array('labelColumn' => 'name'));
$paidStatus = array_search('Paid', $financialItemStatus);
                • We might use getKey instead if we don't need all the statuses :
                $paidStatus = CRM_Core_PseudoConstant::getKey('CRM_Financial_DAO_FinancialItem', 'status_id', 'Paid');

                 

                Samuel Vanhove added a comment -

                cf. https://github.com/civicrm/civicrm-core/pull/10747

                Adam Kwiatkowski added a comment -

                When marking an existing offline payment as completed (no recurring), it generates this same error, even with the latest patch.

                Aug 03 01:09:16 [info] $backTrace = #0 public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Error.php(378): CRM_Core_Error::backtrace("backTrace", TRUE)
                #1 public_html/administrator/components/com_civicrm/civicrm/CRM/Utils/Type.php(476): CRM_Core_Error::fatal("One of parameters (value: ) is not of the type Integer")
                #2 public_html/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php(1385): CRM_Utils_Type::validate(NULL, "Integer")
                #3 public_html/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php(1302): CRM_Core_DAO::composeQuery("UPDATE civicrm_financial_item SET status_id = %1 WHERE entity_id = %2 and ent...", (Array:2), TRUE)
                #4 public_html/administrator/components/com_civicrm/civicrm/CRM/Contribute/BAO/Contribution.php(3610): CRM_Core_DAO::executeQuery("UPDATE civicrm_financial_item SET status_id = %1 WHERE entity_id = %2 and ent...", (Array:2))
                #5 public_html/administrator/components/com_civicrm/civicrm/CRM/Contribute/BAO/Contribution.php(3369): CRM_Contribute_BAO_Contribution::updateFinancialAccounts((Array:59), "changedStatus")
                #6 public_html/administrator/components/com_civicrm/civicrm/CRM/Contribute/BAO/Contribution.php(234): CRM_Contribute_BAO_Contribution::recordFinancialAccounts((Array:59))
                #7 public_html/administrator/components/com_civicrm/civicrm/CRM/Contribute/BAO/Contribution.php(508): CRM_Contribute_BAO_Contribution::add((Array:59), (Array:1))
                #8 public_html/administrator/components/com_civicrm/civicrm/CRM/Contribute/Form/Contribution.php(1733): CRM_Contribute_BAO_Contribution::create((Array:59), (Array:1))
                #9 public_html/administrator/components/com_civicrm/civicrm/CRM/Contribute/Form/Contribution.php(1041): CRM_Contribute_Form_Contribution->submit((Array:36), 2, NULL)
                #10 public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Form.php(447): CRM_Contribute_Form_Contribution->postProcess()
                #11 public_html/administrator/components/com_civicrm/civicrm/CRM/Core/QuickForm/Action/Upload.php(169): CRM_Core_Form->mainProcess()
                #12 public_html/administrator/components/com_civicrm/civicrm/CRM/Core/QuickForm/Action/Upload.php(136): CRM_Core_QuickForm_Action_Upload->realPerform(Object(CRM_Contribute_Form_Contribution), "upload")
                #13 public_html/administrator/components/com_civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Upload->perform(Object(CRM_Contribute_Form_Contribution), "upload")
                #14 public_html/administrator/components/com_civicrm/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contribute_Form_Contribution), "upload")

                Adam Kwiatkowski added a comment -

                BAO/Contribution.php line 3614 -> 

                1 => array(CRM_Core_PseudoConstant::getKey('CRM_Financial_BAO_FinancialItem', 'status_id', 'Paid'), 'Integer'), same call returns null.

                This causes the SQL to fail.

                Adam Kwiatkowski added a comment -

                Replaced with this to return ID 1 - Completed

                 

                $paid = civicrm_api3('OptionValue', 'getsingle', array(
                'sequential' => 1,
                'return' => array("value"),
                'label' => "paid",
                ));

                $fparams = array(
                1 => array($paid['value'], 'Integer'),
                2 => array($lineItemDetails['id'], 'Integer'),
                );

                  People

                  • Assignee:
                    Monish Deb
                    Reporter:
                    Samuel Vanhove

                    Dates

                    • Created:
                      Updated:
                      Resolved: