Ejegg has uploaded a new change for review.
https://gerrit.wikimedia.org/r/176514
Change subject: Add state and postal code to silverpop export
......................................................................
Add state and postal code to silverpop export
Change-Id: I023d572e748d4830211993aa6e648c4a78801776
---
M silverpop_export/update_table.sql
1 file changed, 18 insertions(+), 9 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools
refs/changes/14/176514/1
diff --git a/silverpop_export/update_table.sql
b/silverpop_export/update_table.sql
index 402a5b1..cdbc505 100755
--- a/silverpop_export/update_table.sql
+++ b/silverpop_export/update_table.sql
@@ -43,6 +43,7 @@
-- Address information
city varchar(128),
country varchar(2),
+ state varchar(64),
postal_code varchar(128),
tzoffset float,
@@ -258,30 +259,35 @@
-- Join on civicrm address where we do not already have a geolocated
-- address from contribution tracking
-UPDATE temp_silverpop_export ex, civicrm.civicrm_address addr,
civicrm.civicrm_country ctry
+UPDATE temp_silverpop_export ex
+ JOIN civicrm.civicrm_address addr ON ex.contact_id = addr.contact_id
+ JOIN civicrm.civicrm_country ctry ON addr.country_id = ctry.id
+ LEFT JOIN civicrm.civicrm_state_province st ON addr.state_province_id = st.id
SET
ex.city = addr.city,
ex.country = ctry.iso_code,
- ex.postal_code = addr.postal_code
+ ex.postal_code = addr.postal_code,
+ ex.state = st.name
WHERE
ex.country IS NULL AND
ex.tzoffset IS NULL AND
- ex.contact_id = addr.contact_id AND
- addr.country_id = ctry.id AND
ex.opted_out = 0;
-- And now updated by civicrm address where we have a country but no
-- city from contribution tracking; the countries must match
-UPDATE temp_silverpop_export ex, civicrm.civicrm_address addr,
civicrm.civicrm_country ctry
+UPDATE temp_silverpop_export ex
+ JOIN civicrm.civicrm_address addr ON ex.contact_id = addr.contact_id
+ JOIN civicrm.civicrm_country ctry
+ ON addr.country_id = ctry.id
+ AND ex.country = ctry.iso_code
+ LEFT JOIN civicrm.civicrm_state_province st ON addr.state_province_id = st.id
SET
ex.city = addr.city,
- ex.postal_code = addr.postal_code
+ ex.postal_code = addr.postal_code,
+ ex.state = st.name
WHERE
- ex.country = ctry.iso_code AND
ex.city IS NULL AND
ex.tzoffset IS NULL AND
- ex.contact_id = addr.contact_id AND
- addr.country_id = ctry.id AND
ex.opted_out = 0;
-- Reconstruct the donors likely language from their country if it
@@ -391,6 +397,7 @@
-- Address information
city varchar(128),
country varchar(2),
+ state varchar(24),
postal_code varchar(128),
tzoffset float,
@@ -427,6 +434,8 @@
IFNULL(last_name, '') lastname,
last_ctid ContributionID,
country,
+ state,
+ postal_code,
SUBSTRING(preferred_language, 1, 2) IsoLang,
IF(has_recurred_donation, 'YES', 'NO') has_recurred_donation,
highest_usd_amount,
--
To view, visit https://gerrit.wikimedia.org/r/176514
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I023d572e748d4830211993aa6e648c4a78801776
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/tools
Gerrit-Branch: master
Gerrit-Owner: Ejegg <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
