Mwalker has submitted this change and it was merged.
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
M
sites/all/modules/wmf_reports/CRM/Contact/Form/Task/ContactsAndContributionsExport.php
2 files changed, 60 insertions(+), 66 deletions(-)
Approvals:
Mwalker: Verified; Looks good to me, approved
diff --git
a/sites/all/modules/wmf_reports/CRM/Contact/ContactsAndContributionsExport.php
b/sites/all/modules/wmf_reports/CRM/Contact/ContactsAndContributionsExport.php
index 6358040..d3ad025 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)"),
@@ -67,12 +68,9 @@
'relationships' => array('type' => 'TEXT'),
'activities' => array('type' => 'TEXT'),
);
+ $alter_clauses = array();
foreach ($alter_columns as $name => $desc)
{
- $sql = <<<EOS
-ALTER TABLE {$table} ADD {$name} {$desc['type']}
-EOS;
- CRM_Core_DAO::executeQuery($sql);
$sqlColumns[$name] = $name;
if (!empty($desc['label'])) {
$label = $desc['label'];
@@ -80,16 +78,36 @@
$label = ucfirst(strtr($name, '_', ' '));
}
$headerRows[] = $label;
+
+ $alter_clauses[] = "ADD {$name} {$desc['type']}";
+ }
+ $sql = "ALTER TABLE {$table} " . implode( ", ", $alter_clauses );
+ CRM_Core_DAO::executeQuery($sql);
+
+ $limit = 100;
+ $offset = 0;
+
+ while (true)
+ {
+ $sql = "SELECT * FROM {$table}_rollup LIMIT {$offset}, {$limit}";
+
+ if (!self::alterChunk($table, $sql)) {
+ break;
+ }
+
+ $offset += $limit;
}
- //TODO add index on contact_id and contribution_id if
- // export jobs are huge
+ self::copyLinkData($table);
+ }
- $sql = "SELECT * FROM {$table}_rollup";
+ 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,19 +125,15 @@
$set_clauses[] = "sybunt = {$sybunt}";
}
- $master_row_contribution = array_shift($contributions);
- if (!empty($contributions)) {
- $row_index = 2;
- $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} )";
- $params[$params_index++] = array($contribution[2],
'String');
- $row_index++;
- }
+ $row_index = 1;
+ $params_index = 1;
+ foreach ($contributions as $contribution)
+ {
+ $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} )";
+ $params[$params_index++] = array($contribution[2], 'String');
+ $row_index++;
}
if (!empty($set_clauses))
@@ -128,29 +142,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 +172,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 +194,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 +214,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 +236,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;
diff --git
a/sites/all/modules/wmf_reports/CRM/Contact/Form/Task/ContactsAndContributionsExport.php
b/sites/all/modules/wmf_reports/CRM/Contact/Form/Task/ContactsAndContributionsExport.php
index 1f3231a..13319c9 100644
---
a/sites/all/modules/wmf_reports/CRM/Contact/Form/Task/ContactsAndContributionsExport.php
+++
b/sites/all/modules/wmf_reports/CRM/Contact/Form/Task/ContactsAndContributionsExport.php
@@ -21,7 +21,7 @@
$this->set('mappingId', $mapping->id);
}
- $this->set( 'exportMode' , CRM_Export_Form_Select::CONTRIBUTE_EXPORT );
+ $this->set( 'exportMode' , CRM_Export_Form_Select::CONTACT_EXPORT );
$this->assign( 'matchingContacts', TRUE );
$this->set( 'componentIds', $this->_componentIds );
$this->set( 'selectAll' , FALSE );
--
To view, visit https://gerrit.wikimedia.org/r/60364
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: I64899a427053a513f5884d7b112b4e6e92cb7a47
Gerrit-PatchSet: 3
Gerrit-Project: wikimedia/fundraising/crm
Gerrit-Branch: master
Gerrit-Owner: Adamw <[email protected]>
Gerrit-Reviewer: Mwalker <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
