jenkins-bot has submitted this change and it was merged.
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(-)
Approvals:
Awight: Looks good to me, approved
jenkins-bot: Verified
diff --git a/silverpop_export/update_table.sql
b/silverpop_export/update_table.sql
index 6145f89..cceb70e 100755
--- a/silverpop_export/update_table.sql
+++ b/silverpop_export/update_table.sql
@@ -42,6 +42,7 @@
-- Address information
city varchar(128),
country varchar(2),
+ state varchar(64),
postal_code varchar(128),
tzoffset float,
@@ -231,30 +232,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
@@ -364,6 +370,7 @@
-- Address information
city varchar(128),
country varchar(2),
+ state varchar(24),
postal_code varchar(128),
tzoffset float,
@@ -400,6 +407,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: merged
Gerrit-Change-Id: I023d572e748d4830211993aa6e648c4a78801776
Gerrit-PatchSet: 2
Gerrit-Project: wikimedia/fundraising/tools
Gerrit-Branch: master
Gerrit-Owner: Ejegg <[email protected]>
Gerrit-Reviewer: Awight <[email protected]>
Gerrit-Reviewer: Katie Horn <[email protected]>
Gerrit-Reviewer: Springle <[email protected]>
Gerrit-Reviewer: jenkins-bot <>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
