Drupal Upgrade: How to Migrate data from Ecommerce module to Ubercart in a Drupal site

Anoop John | Updated 28 Jul 2016 | 6 min read

Ecommerce used to be the preferred ecommerce solution for Drupal before Ubercart came into the picture. We recently worked on upgrading a Drupal 5 site with Ecommerce to Drupal 7 with Ubercart. The challenge with this upgrade was that the source data existed only in the D5 database and the corresponding code to programmatically access the ecommerce data was in D5 as well while the destination tables existed only in the D7 database with the corresponding code existing only in the D7 database. The solution we went with was to go for a full SQL query based migration to copy over the Ecommerce data from its tables to the Ubercart tables.

The full migration process was created as a re-executable sql script because we had to iteratively fix issues in the migration. The following set of sql queries were used in the migration. We hope this helps someone trying to Migrate Drupal Ecommerce module to Ubercart. Contact Us if you need help with migrating from Ecommerce to Ubercart or for that matter migrating from any system to Drupal + Ubercart.

/*
DROP table tmp_ec_migrate_products;
DROP table tmp_ec_migrate_txnid_order_id;
*/

-- Temporary table to store the nid,vid pairs that are inserted into uc_products table
CREATE TABLE IF NOT EXISTS tmp_ec_migrate_products (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  nid int(10),
  vid int(10),
  PRIMARY KEY (id),
  INDEX (nid),
  INDEX (vid)
);

-- Temporary table to store the nid,vid pairs that are inserted into uc_products table
CREATE TABLE IF NOT EXISTS tmp_ec_migrate_txnid_order_id (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  txnid int(10),
  order_id int(10),
  PRIMARY KEY (id),
  INDEX (txnid),
  INDEX (order_id)
);

DELETE uop.* FROM uc_order_products uop JOIN tmp_ec_migrate_txnid_order_id tto ON
uop.order_id = tto.order_id;
DELETE upr.* FROM uc_payment_receipts upr JOIN tmp_ec_migrate_txnid_order_id tto ON
upr.order_id = tto.order_id;
DELETE uoc.* FROM uc_order_comments uoc JOIN tmp_ec_migrate_txnid_order_id tto ON
uoc.order_id = tto.order_id;
DELETE uol.* FROM uc_order_line_items uol JOIN tmp_ec_migrate_txnid_order_id tto ON
uol.order_id = tto.order_id;
DELETE uo.* FROM uc_orders uo JOIN tmp_ec_migrate_txnid_order_id tto ON
uo.order_id = tto.order_id;
DELETE up.* FROM uc_products up JOIN tmp_ec_migrate_products tp ON
up.vid = tp.vid AND up.nid = tp.nid;

DELETE tp.* FROM tmp_ec_migrate_products tp;
DELETE tto.* FROM tmp_ec_migrate_txnid_order_id tto;

/**
 * Migration of ec products
 */

-- Store the nids and vids to be deleted if the script is to be re-run 
INSERT INTO tmp_ec_migrate_products (
  nid, vid
)
SELECT
  ep.nid, ep.vid
FROM
  ec_product ep
  LEFT JOIN ec_product_tangible ept ON ep.nid = ept.nid AND ep.vid = ept.vid
  LEFT JOIN uc_products up ON up.nid = ep.nid AND up.vid = ep.vid WHERE up.nid IS NULL;

-- Load uc_products from ec_products tables
INSERT INTO uc_products (
  vid, nid, model, list_price,
  cost, sell_price, weight, weight_units,
  length, width, height, length_units,
  pkg_qty, default_qty, unique_hash, ordering,
  shippable
)
SELECT
  ep.vid, ep.nid, ep.sku, 0,
  0, ep.price, 0, '',
  0, 0, 0, '',
  1, 1, MD5(CONCAT(ep.vid, ep.nid, ep.sku, ep.price, NOW())) AS unique_hash, 0,
  CASE ept.nid WHEN NULL THEN 0 ELSE 1 END AS shippable
FROM
  ec_product ep
  LEFT JOIN ec_product_tangible ept ON ep.nid = ept.nid AND ep.vid = ept.vid
  LEFT JOIN uc_products up ON up.nid = ep.nid AND up.vid = ep.vid WHERE up.nid IS NULL;

/**
 * Migration of ec transactions
 */

SELECT @new_order_id := MAX(order_id)+1 FROM uc_orders;

-- Create and store order_ids for new orders to be imported from the ec_transaction table
INSERT INTO tmp_ec_migrate_txnid_order_id (
  txnid, order_id
)
SELECT
  txnid,
  @new_order_id := @new_order_id + 1
FROM ec_transaction;

-- Load uc_orders from ec_transaction and ec_transaction_address tables
INSERT INTO uc_orders (
  order_id,
  uid,
  order_status,
  order_total,
  primary_email,
  delivery_first_name,
  delivery_last_name,
  delivery_phone,
  delivery_company,
  delivery_street1,
  delivery_street2,
  delivery_city,
  delivery_zone,
  delivery_postal_code,
  delivery_country,
  billing_first_name,
  billing_last_name,
  billing_phone,
  billing_company,
  billing_street1,
  billing_street2,
  billing_city,
  billing_zone,
  billing_postal_code,
  billing_country,
  payment_method,
  data,
  host,
  created,
  modified,
  product_count,
  currency
)
SELECT
  tto.order_id,
  et.uid,
  CASE et.payment_status WHEN 1 THEN 'pending' WHEN 2 THEN 'completed'
ELSE 'pending' END AS order_status,
  et.gross,
  et.mail,
  etas.firstname,
  etas.lastname,
  '' AS delivery_phone,
  '' AS delivery_company,
  etas.street1,
  etas.street2,
  etas.city,
  0 AS delivery_zone,
  etas.zip,
  0 AS delivery_country,
  etab.firstname,
  etab.lastname,
  '' AS billing_phone,
  '' AS billing_company,
  etab.street1,
  etab.street2,
  etab.city,
  0 AS billing_zone,
  etab.zip,
  0 AS billing_country,
  CASE et.payment_method WHEN 'authorize_net' THEN 'credit' WHEN 'manually_add'
THEN 'other' ELSE 'other' END AS payment_method,
  'a:0:{}' AS data,
  '127.0.0.1' AS host,
  et.created,
  et.changed,
  0 AS product_count,
  'USD' AS currency
FROM
  ec_transaction et
  JOIN tmp_ec_migrate_txnid_order_id tto ON et.txnid = tto.txnid
  LEFT JOIN ec_transaction_address etab ON et.txnid = etab.txnid AND etab.type = 'billing'
  LEFT JOIN ec_transaction_address etas ON et.txnid = etas.txnid AND etas.type = 'shipping';

-- Create temporary table with indexed fields for handling country and state data
CREATE TABLE IF NOT EXISTS tmp_ec_migrate_ect_address (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  txnid int(10),
  state varchar(100),
  country char(2),
  type char(1),
  PRIMARY KEY (id),
  INDEX (txnid),
  INDEX (state),
  INDEX (country),
  INDEX (type)
);
-- Load temporary table with country and state data for billing address
INSERT INTO tmp_ec_migrate_ect_address (
  txnid,
  state,
  country,
  type
)
SELECT
  txnid,
  UPPER(state),
  UPPER(CASE country WHEN 'an' THEN 'CW' WHEN 'uk' THEN 'GB' WHEN 'Un'
THEN 'US' WHEN 'cs' THEN 'RS' WHEN '' THEN 'US' WHEN NULL THEN 'US'
ELSE country END) AS country_corrected,
  'b'
FROM ec_transaction_address
WHERE type = 'billing';

-- Load temporary table with country and state data for shipping address
INSERT INTO tmp_ec_migrate_ect_address (
  txnid,
  state,
  country,
  type
)
SELECT
  txnid,
  UPPER(state),
  UPPER(CASE country WHEN 'an' THEN 'CW' WHEN 'uk' THEN 'GB' WHEN 'Un'
THEN 'US' WHEN 'cs' THEN 'RS' WHEN '' THEN 'US' WHEN NULL THEN 'US'
ELSE country END) AS country_corrected,
  's'
FROM ec_transaction_address
WHERE type = 'shipping';

-- Update billing_zone for orders loaded from ec_transaction table
UPDATE uc_orders uo
  JOIN tmp_ec_migrate_txnid_order_id tto ON uo.order_id = tto.order_id
  LEFT JOIN tmp_ec_migrate_ect_address etab ON tto.txnid = etab.txnid
AND etab.type = 'b'
  LEFT JOIN uc_zones uczb ON etab.state = UPPER(uczb.zone_name)
SET uo.billing_zone = uczb.zone_id
WHERE uo.billing_zone = 0;

-- Update delivery_zone for orders loaded from ec_transaction table
UPDATE uc_orders uo
  JOIN tmp_ec_migrate_txnid_order_id tto ON uo.order_id = tto.order_id
  LEFT JOIN tmp_ec_migrate_ect_address etas ON tto.txnid = etas.txnid AND etas.type = 's'
  LEFT JOIN uc_zones uczs ON etas.state = UPPER(uczs.zone_name)
SET uo.delivery_zone = uczs.zone_id
WHERE uo.delivery_zone = 0;

-- Update billing_country for orders loaded from ec_transaction table
UPDATE uc_orders uo
  JOIN tmp_ec_migrate_txnid_order_id tto ON uo.order_id = tto.order_id
  LEFT JOIN tmp_ec_migrate_ect_address etab ON etab.txnid = tto.txnid
  LEFT JOIN uc_countries uccb ON etab.country = UPPER(uccb.country_iso_code_2)
SET uo.billing_country = uccb.country_id
WHERE uo.billing_country = 0;

-- Update delivery_country for orders loaded from ec_transaction table
UPDATE uc_orders uo
  JOIN tmp_ec_migrate_txnid_order_id tto ON uo.order_id = tto.order_id
  LEFT JOIN tmp_ec_migrate_ect_address etas ON etas.txnid = tto.txnid
AND etas.type = 's'
  LEFT JOIN uc_countries uccs ON etas.country = UPPER(uccs.country_iso_code_2)
SET uo.delivery_country = uccs.country_id
WHERE uo.delivery_country = 0;

-- Drop temporary table
DROP TABLE tmp_ec_migrate_ect_address;

-- Update product counts for orders loaded from ec_transaction table
UPDATE uc_orders uo
  JOIN tmp_ec_migrate_txnid_order_id tto ON uo.order_id = tto.order_id
  LEFT JOIN (SELECT txnid, COUNT(txnid) AS product_count FROM
ec_transaction_product GROUP BY txnid) tpc ON tto.txnid = tpc.txnid
SET uo.product_count = tpc.product_count
WHERE tpc.product_count IS NOT NULL;

/**
 * Migration of transaction products
 */

-- Load uc_order_products from ec_transaction_product table
INSERT INTO uc_order_products (
  order_id,
  nid,
  title,
  model,
  qty,
  cost,
  price,
  weight,
  data,
  weight_units
)
SELECT
  tto.order_id,
  etp.nid,
  etp.title,
  etp.title AS model,
  etp.qty,
  0 AS cost,
  etp.price,
  ctp.field_weight_0_value,
  'a:1:{s:6:"module";s:10:"uc_product";}' AS data,
  'lb' AS weight_units
FROM
  ec_transaction_product etp
  JOIN tmp_ec_migrate_txnid_order_id tto ON etp.txnid = tto.txnid
  LEFT JOIN content_type_physical_item ctp ON etp.nid = ctp.nid AND
etp.vid = ctp.vid;

/**
 * Migration of authorize.net payment transactions
 */

-- Load uc_payment_receipts from ec_authorize_net table
INSERT INTO uc_payment_receipts (
  order_id,
  method,
  amount,
  uid,
  data,
  comment,
  received
)
SELECT
  tto.order_id,
  'Credit Card' AS method,
  ea.amount,
  uo.uid,
  CONCAT('a:3:{s:6:"module";s:15:"uc_authorizenet";s:8:"txn_type";s:12:"auth_capture";s:6:"txn_id";s:10:"',
ea.anid, '";}') AS data,
  CONCAT('Type: Authorization and capture
ID: ', ea.anid) AS comment,
  uo.modified
FROM
  uc_orders uo
  JOIN tmp_ec_migrate_txnid_order_id tto ON uo.order_id = tto.order_id
  JOIN ec_authorize_net ea ON tto.txnid = ea.txnid;

/**
 * Migration of manually recorded receipts
 */

-- Load uc_payment_receipts for manually added payments recorded in ec_transaction
INSERT INTO uc_payment_receipts (
  order_id,
  method,
  amount,
  uid,
  data,
  comment,
  received
)
SELECT
  tto.order_id,
  'Other' AS method,
  et.gross,
  et.uid,
  '' AS data,
  '' AS comment,
  et.changed
FROM
  tmp_ec_migrate_txnid_order_id tto
  JOIN ec_transaction et ON tto.txnid = et.txnid
WHERE
  et.payment_status = 2 AND et.payment_method = 'manually_add';

/**
 * Migration of transaction notes
 */

-- Load uc_order_comments from ec_transaction_note table
INSERT INTO uc_order_comments (
  order_id,
  uid,
  order_status,
  notified,
  message,
  created
)
SELECT
  tto.order_id,
  et.uid,
  CASE et.payment_status WHEN 1 THEN 'pending' WHEN 2 THEN 'completed'
ELSE 'pending' END AS order_status,
  1 AS notified,
  etn.note,
  et.created
FROM
  ec_transaction et
  JOIN tmp_ec_migrate_txnid_order_id tto ON et.txnid = tto.txnid
  JOIN ec_transaction_note etn ON et.txnid = etn.txnid;

/**
 * Migration of transaction tax
 */

-- Load uc_order_line_items from ec_transaction_misc table
INSERT INTO uc_order_line_items (
  order_id,
  type,
  title,
  amount,
  weight,
  data
)
SELECT
  tto.order_id,
  'tax' AS type,
  'California State Tax' AS title,
  etm.price,
  etm.weight,
  CONCAT('{s:8:"tax_rate";s:5:"0.095";s:14:"taxable_amount";d:',
et.gross, ';s:16:"tax_jurisdiction";s:20:"California State Tax";}') AS
data
FROM
  ec_transaction_misc etm
  JOIN tmp_ec_migrate_txnid_order_id tto ON etm.txnid = tto.txnid
  JOIN ec_transaction et ON tto.txnid = et.txnid;

Drupal Upgrade: How to Migrate data from Ecommerce module to Ubercart in a Drupal site

Related Topics