Aller directement à la fin des métadonnées
Aller au début des métadonnées

Here are the case's for erroneous data that need to be repaired in upgrade for CRM-13420

 

Case 1.

 Added a new contribution without selecting Paid By.

 

Case 2.

 

 Added a new contribution without selecting Paid By. (financial_trxn = 100)

 Edited the newly added contribution and changed Paid By to EFT (financial_trxn = 101)

 

Case 3

 

Added a new Pending contribution without selecting Paid By. (financial_trxn = 108)

  Edited the contribution status and changed it to Completed (financial_trxn = 109)

  Edited the newly added contribution and changed Paid By to EFT (financial_trxn = 110)

 

Case 4.

  Added a new Pending contribution without selecting Paid By. (financial_trxn = 111)

  Edited the contribution status and changed it to Completed and added fee amount as $3 (financial_trxn = 112 and 113)

  Edited the newly added contribution and changed Paid By to EFT (financial_trxn = 114)

 

The from_financial_account_id is stored as 1 rather then 6 in financial_trxn = 110, 101, 114 in case 2-4.

Étiquette
  • Aucun
  1. Dec 03, 2013

    JoeMurray dit :

    Here is a potential approach based on the idea that the transaction that needs to have its from_financial_account_id changed to 6 is the first one for a contribution that has payment_instrument_id non-null.

    1. Produce a bunch of edits before and after the payment instrument is changed to non-null.

    2. Run the following query and check its results (note that this only works if the users never change it from a non-null to a null value):
      SELECT sq1.*,sq2.first_non_null FROM
      (SELECT eftc.entity_id as contribution_id, max(ft.id) as last_null from civicrm_financial_trxn ft inner join civicrm_entity_financial_trxn eftc on eftc.financial_trxn_id=ft.id AND eftc.entity_table='civicrm_contribution' WHERE ft.payment_instrument_id is null group by contribution_id) AS sq1
      INNER JOIN
      (SELECT eftc.entity_id as contribution_id, min(ft.id) as first_non_null from civicrm_financial_trxn ft inner join civicrm_entity_financial_trxn eftc on eftc.financial_trxn_id=ft.id AND eftc.entity_table='civicrm_contribution' WHERE ft.payment_instrument_id is not null group by contribution_id) AS sq2
      on sq1.contribution_id=sq2.contribution_id;

    3. Assuming that looks good, then the fix is pretty simple:
      update civicrm_financial_trxn ft set ft.from_financial_account_id=6 where ft.id = (SELECT sq2.first_non_null FROM
      (SELECT eftc.entity_id as contribution_id, max(ft.id) as last_null from civicrm_financial_trxn ft inner join civicrm_entity_financial_trxn eftc on eftc.financial_trxn_id=ft.id AND eftc.entity_table='civicrm_contribution' WHERE ft.payment_instrument_id is null group by contribution_id) AS sq1
      INNER JOIN
      (SELECT eftc.entity_id as contribution_id, min(ft.id) as first_non_null from civicrm_financial_trxn ft inner join civicrm_entity_financial_trxn eftc on eftc.financial_trxn_id=ft.id AND eftc.entity_table='civicrm_contribution' WHERE ft.payment_instrument_id is not null group by contribution_id) AS sq2 
      on sq1.contribution_id=sq2.contribution_id);