Magento Anonymize Customer Data

magento-anonymize-customer-data.sql

-- Anonymize Magento company admins
select CONCAT_WS('', 'UPDATE customer_entity AS cu LEFT OUTER JOIN company AS co ON (cu.entity_id = co.super_user_id) SET cu.firstname = ''WS', co.erp_customer_id, ''', cu.lastname = ''', LEFT(REPLACE(REPLACE(REPLACE(co.company_name, ' ', ''), '&', ''), '''', ''), 14), ''', cu.email = ''Customer', co.erp_customer_id, '@qa.curtgroup.com'', cu.password_hash = ''generatedpasswordhash:salty1:1'', co.company_email = cu.email WHERE cu.entity_id = ', cu.entity_id, ';') AS newEmail
-- , '--cu--' as labelcu, cu.*, '--co--' as labelco, co.*
from company AS co
left outer join customer_entity AS cu ON (cu.entity_id = co.super_user_id) where cu.email not like '%@curtgroup.com';

-- Anonymize Magento company sub-users
select CONCAT_WS('', 'UPDATE customer_entity SET firstname = ''WS', co.erp_customer_id, ''', lastname = ''', LEFT(REPLACE(REPLACE(REPLACE(co.company_name, ' ', ''), '&', ''), '''', ''), 14), ''', email = ''SubUser', co.erp_customer_id, '-', cu.entity_id, '@qa.curtgroup.com'', password_hash = ''generatedpasswordhash:salty1:1'' WHERE entity_id = ', cu.entity_id, ';') AS newEmail
-- , cu.*, co.*
from company AS co
LEFT OUTER JOIN company_advanced_customer_entity AS cace ON (cace.company_id = co.entity_id)
left outer join customer_entity as cu ON (cu.entity_id = cace.customer_id)
where cu.email not like '%curtgroup.com' and cu.email not like '%canary%'
;