Adamw has uploaded a new change for review. https://gerrit.wikimedia.org/r/60364
Change subject: Contacts and Contributions export fixes ...................................................................... Contacts and Contributions export fixes * Internal processing is limited to 100 rows at a time. * No longer dependent on deprecated Advanced Search display mode. Change-Id: I64899a427053a513f5884d7b112b4e6e92cb7a47 --- M sites/all/modules/wmf_reports/CRM/Contact/ContactsAndContributionsExport.php 1 file changed, 46 insertions(+), 51 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/crm refs/changes/64/60364/1 diff --git a/sites/all/modules/wmf_reports/CRM/Contact/ContactsAndContributionsExport.php b/sites/all/modules/wmf_reports/CRM/Contact/ContactsAndContributionsExport.php index 6358040..9acbfc2 100644 --- a/sites/all/modules/wmf_reports/CRM/Contact/ContactsAndContributionsExport.php +++ b/sites/all/modules/wmf_reports/CRM/Contact/ContactsAndContributionsExport.php @@ -9,7 +9,7 @@ static function alterExport(&$table, &$headerRows, &$sqlColumns, &$exportMode) { // Allows rolling up to c. 2,000 contributions per donor. Clearly - // unsustainable, 'cos the spreadsheet has two columns for each contribution. + // unsustainable. CRM_Core_DAO::executeQuery("SET SESSION group_concat_max_len = 60000"); $sql = <<<EOS @@ -27,17 +27,18 @@ civicrm_primary_id AS contact_id, GROUP_CONCAT( CONCAT( - contribution_id,',', - total_amount,',', - UNIX_TIMESTAMP( COALESCE( receive_date, '' ) ) + civicrm_contribution.id,',', + civicrm_contribution.total_amount,',', + UNIX_TIMESTAMP( COALESCE( civicrm_contribution.receive_date, '' ) ) ) SEPARATOR ';' ) AS rollup, COUNT(*) AS count FROM {$table} - WHERE COALESCE(contribution_id, '') <> '' + JOIN civicrm_contribution + ON civicrm_primary_id = civicrm_contribution.contact_id GROUP BY civicrm_primary_id - ORDER BY civicrm_primary_id, receive_date DESC + ORDER BY civicrm_primary_id, civicrm_contribution.receive_date DESC ) EOS; CRM_Core_DAO::executeQuery($sql); @@ -48,9 +49,9 @@ $max_contribution_count = CRM_Core_DAO::singleValueQuery($sql); $alter_columns = array(); - if ($max_contribution_count > 1) + if ($max_contribution_count > 0) { - foreach (range(2, $max_contribution_count) as $index) + foreach (range(1, $max_contribution_count) as $index) { $alter_columns += array( "total_amount_{$index}" => array('type' => "DECIMAL(20,2)"), @@ -82,14 +83,30 @@ $headerRows[] = $label; } - //TODO add index on contact_id and contribution_id if - // export jobs are huge + $limit = 100; + $offset = 0; - $sql = "SELECT * FROM {$table}_rollup"; + while (true) + { + $sql = "SELECT * FROM {$table}_rollup LIMIT {$offset}, {$limit}"; + + if (!self::alterChunk($table, $dao)) { + break; + } + + $offset += $limit; + } + + self::copyLinkData($table); + } + + static protected function alterChunk($table, $sql) + { $dao = CRM_Core_DAO::executeQuery($sql); - $delete_ids = array(); + $result = false; while ($dao->fetch()) { + $result = true; $contribution_strs = explode(';', $dao->rollup); $contributions = array(); foreach ($contribution_strs as $str) @@ -107,13 +124,11 @@ $set_clauses[] = "sybunt = {$sybunt}"; } - $master_row_contribution = array_shift($contributions); if (!empty($contributions)) { - $row_index = 2; + $row_index = 1; $params_index = 1; foreach ($contributions as $contribution) { - $delete_ids[] = $contribution[0]; $set_clauses[] = "total_amount_{$row_index} = %{$params_index}"; $params[$params_index++] = array($contribution[1], 'String'); $set_clauses[] = "receive_date_{$row_index} = FROM_UNIXTIME( %{$params_index} )"; @@ -128,29 +143,23 @@ $sql = <<<EOS UPDATE {$table} SET {$set_clause} - WHERE contribution_id = {$master_row_contribution[0]} + WHERE civicrm_primary_id = {$dao->contact_id} EOS; CRM_Core_DAO::executeQuery($sql, $params); } } + return $result; + } - if (!empty($delete_ids)) - { - $delete_ids_clause = implode(", ", $delete_ids); - $sql = <<<EOS -DELETE FROM {$table} - WHERE contribution_id IN ({$delete_ids_clause}) -EOS; - CRM_Core_DAO::executeQuery($sql); - } - + static protected function copyLinkData($table) + { $sql = <<<EOS UPDATE {$table} SET notes = ( SELECT GROUP_CONCAT(CONCAT(subject, ': ', note) SEPARATOR '\n\n') FROM civicrm_note - WHERE civicrm_note.contact_id = {$table}.civicrm_primary_id - GROUP BY {$table}.civicrm_primary_id + WHERE civicrm_note.contact_id = civicrm_primary_id + GROUP BY civicrm_primary_id ORDER BY civicrm_note.id ) EOS; @@ -164,9 +173,9 @@ JOIN civicrm_group_contact ON civicrm_group_contact.group_id = civicrm_group.id WHERE - civicrm_group_contact.contact_id = {$table}.civicrm_primary_id + civicrm_group_contact.contact_id = civicrm_primary_id AND civicrm_group_contact.status = 'Added' - GROUP BY {$table}.civicrm_primary_id + GROUP BY civicrm_primary_id ORDER BY civicrm_group.title ) EOS; @@ -186,8 +195,8 @@ JOIN civicrm_contact target_contact ON target_contact.id = related_ab.contact_id_b WHERE - related_ab.contact_id_a = {$table}.civicrm_primary_id - GROUP BY {$table}.civicrm_primary_id + related_ab.contact_id_a = civicrm_primary_id + GROUP BY civicrm_primary_id ) EOS; CRM_Core_DAO::executeQuery($sql); @@ -206,8 +215,8 @@ JOIN civicrm_contact target_contact ON target_contact.id = related_ba.contact_id_a WHERE - related_ba.contact_id_b = {$table}.civicrm_primary_id - GROUP BY {$table}.civicrm_primary_id + related_ba.contact_id_b = civicrm_primary_id + GROUP BY civicrm_primary_id )) EOS; CRM_Core_DAO::executeQuery($sql); @@ -228,29 +237,15 @@ ON activity_type.value = civicrm_activity.activity_type_id AND activity_type.option_group_id = civicrm_option_group.id WHERE - civicrm_activity.source_contact_id = {$table}.civicrm_primary_id + civicrm_activity.source_contact_id = civicrm_primary_id AND civicrm_option_group.name = 'activity_type' - GROUP BY {$table}.civicrm_primary_id + GROUP BY civicrm_primary_id ) EOS; CRM_Core_DAO::executeQuery($sql); - - $drop_columns = array( - 'civicrm_primary_id', - 'contribution_id' - ); - foreach ($drop_columns as $dropping) - { - $column_index = array_search($dropping, array_keys($sqlColumns)); - unset($sqlColumns[$dropping]); - unset($headerRows[$column_index]); - - $sql = "ALTER TABLE {$table} DROP COLUMN {$dropping}"; - CRM_Core_DAO::executeQuery($sql); - } } - static function calc_bunts($contributions) + static protected function calc_bunts($contributions) { $config = CRM_Core_Config::singleton(); $fy = $config->fiscalYearStart; -- To view, visit https://gerrit.wikimedia.org/r/60364 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I64899a427053a513f5884d7b112b4e6e92cb7a47 Gerrit-PatchSet: 1 Gerrit-Project: wikimedia/fundraising/crm Gerrit-Branch: master Gerrit-Owner: Adamw <awi...@wikimedia.org> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits