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 <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
