Mwalker has uploaded a new change for review.
https://gerrit.wikimedia.org/r/72752
Change subject: Updates to the export script
......................................................................
Updates to the export script
Change-Id: I52ad18966c9ff6bb0c66c3857dd00e79c175360b
---
M silverpop_export.sql
1 file changed, 5 insertions(+), 4 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools
refs/changes/52/72752/1
diff --git a/silverpop_export.sql b/silverpop_export.sql
index b49c12a..09682c1 100644
--- a/silverpop_export.sql
+++ b/silverpop_export.sql
@@ -53,7 +53,7 @@
(contact_id, email, first_name, last_name, preferred_language)
SELECT
e.contact_id, e.email, c.first_name, c.last_name,
- SUBSTRING(1, 2, c.preferred_language)
+ SUBSTRING(c.preferred_language, 1, 2)
FROM civicrm.civicrm_email e, civicrm.civicrm_contact c
WHERE
e.email IS NOT NULL AND e.email != '' AND
@@ -133,7 +133,7 @@
SELECT
e.email, ex.id, MAX(ct.id), MAX(ct.total_amount), SUM(ct.total_amount),
count(*),
- SUM(IF(SUBSTRING(ct.trxn_id, 1, 9) = 'RECURRING', 1, 0), 1, 0),
+ SUM(IF(SUBSTRING(ct.trxn_id, 1, 9) = 'RECURRING', 1, 0)),
SUM(IF('2006-07-1' < ct.receive_date AND ct.receive_date < '2007-07-01',
1, 0)),
SUM(IF('2007-07-1' < ct.receive_date AND ct.receive_date < '2008-07-01',
1, 0)),
SUM(IF('2008-07-1' < ct.receive_date AND ct.receive_date < '2009-07-01',
1, 0)),
@@ -172,8 +172,7 @@
latest_currency = SUBSTRING(ct.source, 1, 3),
latest_native_amount = CONVERT(SUBSTRING(ct.source, 5), decimal(20,2)),
latest_usd_amount = ct.total_amount,
- latest_donation = ct.receive_date,
- latest_is_recurring = IF(SUBSTRING(ct.trxn_id, 1, 9) = 'RECURRING', 1, 0)
+ latest_donation = ct.receive_date
WHERE
ex.last_ctid = ct.id;
@@ -244,6 +243,8 @@
unsub_hash = SHA1(CONCAT(last_ctid, email, XXX));
-- Export some random rows
+-- Run something like this from the command line like so...
+-- mysql -h db1008.eqiad.wmnet -u pcoombe -p mwalker < query.sql | sed
"s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > out.csv
SELECT contact_id ContactID, email, first_name firstname, last_name lastname,
last_ctid ContributionID, country,
SUBSTRING(preferred_language, 1, 2) IsoLang, has_recurred_donation,
highest_usd_amount,
--
To view, visit https://gerrit.wikimedia.org/r/72752
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I52ad18966c9ff6bb0c66c3857dd00e79c175360b
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/tools
Gerrit-Branch: master
Gerrit-Owner: Mwalker <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
