#!/usr/bin/env python3 """ WooCommerce CASH ON DELIVERY Orders to Saleor Migration ======================================================= For stores with COD only - no payment gateway, no transaction IDs. Payment is collected on delivery, so payment status = fulfillment status. Key differences from card payments: - No payment_method details needed (or set to 'mirumee.payments.dummy') - No transaction IDs - Payment is marked as received when order is fulfilled - Simpler order structure """ import os import sys import json import uuid import argparse from datetime import datetime from typing import Dict, List, Optional from dataclasses import dataclass import psycopg2 WP_DB_CONFIG = { 'host': os.getenv('WP_DB_HOST', 'localhost'), 'port': int(os.getenv('WP_DB_PORT', 3306)), 'user': os.getenv('WP_DB_USER', 'wordpress'), 'password': os.getenv('WP_DB_PASSWORD', ''), 'database': os.getenv('WP_DB_NAME', 'wordpress'), } SALEOR_DB_CONFIG = { 'host': os.getenv('SALEOR_DB_HOST', 'localhost'), 'port': int(os.getenv('SALEOR_DB_PORT', 5432)), 'user': os.getenv('SALEOR_DB_USER', 'saleor'), 'password': os.getenv('SALEOR_DB_PASSWORD', ''), 'database': os.getenv('SALEOR_DB_NAME', 'saleor'), } # COD Status Mapping # WC: wc-pending -> Saleor: UNCONFIRMED (order received, not processed) # WC: wc-processing -> Saleor: UNFULFILLED (preparing for delivery) # WC: wc-completed -> Saleor: FULFILLED + payment marked as received ORDER_STATUS_MAP = { 'wc-pending': 'UNCONFIRMED', 'wc-processing': 'UNFULFILLED', 'wc-on-hold': 'UNCONFIRMED', 'wc-completed': 'FULFILLED', 'wc-cancelled': 'CANCELED', 'wc-refunded': 'CANCELED', # COD refunds are manual 'wc-failed': 'CANCELED', } @dataclass class CODOrder: """COD Order with minimal payment info""" wc_order_id: int order_number: str status: str date_created: datetime date_modified: datetime customer_email: str customer_first_name: str customer_last_name: str customer_phone: Optional[str] total: float # in cents subtotal: float tax: float shipping: float currency: str billing_address: Dict shipping_address: Dict customer_note: str shipping_method: str items: List[Dict] is_paid: bool # Derived from status (completed = paid) class CODOrderExporter: """Export COD orders from WooCommerce""" def __init__(self, wp_db_config: Dict): try: import pymysql self.conn = pymysql.connect( host=wp_db_config['host'], port=wp_db_config['port'], user=wp_db_config['user'], password=wp_db_config['password'], database=wp_db_config['database'], cursorclass=pymysql.cursors.DictCursor ) except ImportError: raise ImportError("pymysql required") def get_orders(self, limit: Optional[int] = None, status: Optional[str] = None) -> List[CODOrder]: """Fetch COD orders""" query = """ SELECT p.ID as wc_order_id, p.post_date as date_created, p.post_modified as date_modified, p.post_status as status, meta_total.meta_value as total, meta_subtotal.meta_value as subtotal, meta_tax.meta_value as tax, meta_shipping.meta_value as shipping, meta_currency.meta_value as currency, meta_email.meta_value as customer_email, meta_first.meta_value as customer_first_name, meta_last.meta_value as customer_last_name, meta_phone.meta_value as customer_phone, meta_shipping_method.meta_value as shipping_method, meta_customer_note.meta_value as customer_note FROM wp_posts p LEFT JOIN wp_postmeta meta_total ON p.ID = meta_total.post_id AND meta_total.meta_key = '_order_total' LEFT JOIN wp_postmeta meta_subtotal ON p.ID = meta_subtotal.post_id AND meta_subtotal.meta_key = '_order_subtotal' LEFT JOIN wp_postmeta meta_tax ON p.ID = meta_tax.post_id AND meta_tax.meta_key = '_order_tax' LEFT JOIN wp_postmeta meta_shipping ON p.ID = meta_shipping.post_id AND meta_shipping.meta_key = '_order_shipping' LEFT JOIN wp_postmeta meta_currency ON p.ID = meta_currency.post_id AND meta_currency.meta_key = '_order_currency' LEFT JOIN wp_postmeta meta_email ON p.ID = meta_email.post_id AND meta_email.meta_key = '_billing_email' LEFT JOIN wp_postmeta meta_first ON p.ID = meta_first.post_id AND meta_first.meta_key = '_billing_first_name' LEFT JOIN wp_postmeta meta_last ON p.ID = meta_last.post_id AND meta_last.meta_key = '_billing_last_name' LEFT JOIN wp_postmeta meta_phone ON p.ID = meta_phone.post_id AND meta_phone.meta_key = '_billing_phone' LEFT JOIN wp_postmeta meta_shipping_method ON p.ID = meta_shipping_method.post_id AND meta_shipping_method.meta_key = '_shipping_method' LEFT JOIN wp_postmeta meta_customer_note ON p.ID = meta_customer_note.post_id AND meta_customer_note.meta_key = 'customer_note' WHERE p.post_type = 'shop_order' """ params = [] if status: query += " AND p.post_status = %s" params.append(status) query += " ORDER BY p.post_date DESC" if limit: query += f" LIMIT {limit}" with self.conn.cursor() as cursor: cursor.execute(query, params) rows = cursor.fetchall() orders = [] for row in rows: billing = self._get_address(row['wc_order_id'], 'billing') shipping = self._get_address(row['wc_order_id'], 'shipping') items = self._get_items(row['wc_order_id']) # For COD: order is paid when status is completed is_paid = row['status'] == 'wc-completed' order = CODOrder( wc_order_id=row['wc_order_id'], order_number=f"WC-{row['wc_order_id']}", status=row['status'], date_created=row['date_created'], date_modified=row['date_modified'], customer_email=row['customer_email'] or '', customer_first_name=row['customer_first_name'] or '', customer_last_name=row['customer_last_name'] or '', customer_phone=row['customer_phone'], total=float(row['total'] or 0) * 100, subtotal=float(row['subtotal'] or 0) * 100, tax=float(row['tax'] or 0) * 100, shipping=float(row['shipping'] or 0) * 100, currency=row['currency'] or 'RSD', billing_address=billing or self._empty_address(), shipping_address=shipping or billing or self._empty_address(), shipping_method=row['shipping_method'] or 'Cash on Delivery', customer_note=row['customer_note'] or '', items=items, is_paid=is_paid ) orders.append(order) return orders def _get_address(self, order_id: int, prefix: str) -> Optional[Dict]: query = f""" SELECT MAX(CASE WHEN meta_key = '_{prefix}_first_name' THEN meta_value END) as first_name, MAX(CASE WHEN meta_key = '_{prefix}_last_name' THEN meta_value END) as last_name, MAX(CASE WHEN meta_key = '_{prefix}_company' THEN meta_value END) as company, MAX(CASE WHEN meta_key = '_{prefix}_address_1' THEN meta_value END) as address_1, MAX(CASE WHEN meta_key = '_{prefix}_address_2' THEN meta_value END) as address_2, MAX(CASE WHEN meta_key = '_{prefix}_city' THEN meta_value END) as city, MAX(CASE WHEN meta_key = '_{prefix}_postcode' THEN meta_value END) as postcode, MAX(CASE WHEN meta_key = '_{prefix}_country' THEN meta_value END) as country, MAX(CASE WHEN meta_key = '_{prefix}_phone' THEN meta_value END) as phone FROM wp_postmeta WHERE post_id = %s """ with self.conn.cursor() as cursor: cursor.execute(query, (order_id,)) row = cursor.fetchone() if not row or not row['first_name']: return None return { 'first_name': row['first_name'] or '', 'last_name': row['last_name'] or '', 'company_name': row['company'] or '', 'street_address_1': row['address_1'] or '', 'street_address_2': row['address_2'] or '', 'city': row['city'] or '', 'postal_code': row['postcode'] or '', 'country': row['country'] or 'RS', 'phone': row['phone'] or '', } def _empty_address(self) -> Dict: return { 'first_name': '', 'last_name': '', 'company_name': '', 'street_address_1': '', 'street_address_2': '', 'city': '', 'postal_code': '', 'country': 'RS', 'phone': '' } def _get_items(self, order_id: int) -> List[Dict]: query = """ SELECT oi.order_item_name as name, meta_sku.meta_value as sku, meta_qty.meta_value as quantity, meta_subtotal.meta_value as subtotal, meta_total.meta_value as total, meta_tax.meta_value as tax FROM wp_woocommerce_order_items oi LEFT JOIN wp_woocommerce_order_itemmeta meta_sku ON oi.order_item_id = meta_sku.order_item_id AND meta_sku.meta_key = '_sku' LEFT JOIN wp_woocommerce_order_itemmeta meta_qty ON oi.order_item_id = meta_qty.order_item_id AND meta_qty.meta_key = '_qty' LEFT JOIN wp_woocommerce_order_itemmeta meta_subtotal ON oi.order_item_id = meta_subtotal.order_item_id AND meta_subtotal.meta_key = '_line_subtotal' LEFT JOIN wp_woocommerce_order_itemmeta meta_total ON oi.order_item_id = meta_total.order_item_id AND meta_total.meta_key = '_line_total' LEFT JOIN wp_woocommerce_order_itemmeta meta_tax ON oi.order_item_id = meta_tax.order_item_id AND meta_tax.meta_key = '_line_tax' WHERE oi.order_id = %s AND oi.order_item_type = 'line_item' """ with self.conn.cursor() as cursor: cursor.execute(query, (order_id,)) rows = cursor.fetchall() items = [] for row in rows: qty = int(row['quantity'] or 1) items.append({ 'name': row['name'] or '', 'sku': row['sku'] or '', 'quantity': qty, 'subtotal': float(row['subtotal'] or 0) * 100, 'total': float(row['total'] or 0) * 100, 'tax': float(row['tax'] or 0) * 100, }) return items class CODSaleorImporter: """Import COD orders into Saleor""" def __init__(self, saleor_db_config: Dict): self.conn = psycopg2.connect( host=saleor_db_config['host'], port=saleor_db_config['port'], user=saleor_db_config['user'], password=saleor_db_config['password'], database=saleor_db_config['database'] ) self.email_to_user_id: Dict[str, uuid.UUID] = {} self._ensure_tables() self._load_mappings() def _ensure_tables(self): with self.conn.cursor() as cursor: cursor.execute(""" CREATE TABLE IF NOT EXISTS wc_cod_customer_mapping ( email VARCHAR(255) PRIMARY KEY, saleor_user_id UUID NOT NULL, first_name VARCHAR(255), last_name VARCHAR(255), phone VARCHAR(255), order_count INTEGER DEFAULT 0, created_at TIMESTAMP DEFAULT NOW() ); """) cursor.execute(""" CREATE TABLE IF NOT EXISTS wc_order_mapping ( wc_order_id BIGINT PRIMARY KEY, saleor_order_id UUID NOT NULL, customer_email VARCHAR(255), migrated_at TIMESTAMP DEFAULT NOW() ); """) self.conn.commit() def _load_mappings(self): with self.conn.cursor() as cursor: cursor.execute("SELECT email, saleor_user_id FROM wc_cod_customer_mapping") for row in cursor.fetchall(): self.email_to_user_id[row[0]] = row[1] def get_channel_id(self) -> uuid.UUID: with self.conn.cursor() as cursor: cursor.execute("SELECT id FROM channel_channel WHERE slug = 'default-channel' LIMIT 1") return cursor.fetchone()[0] def create_user(self, email: str, first_name: str, last_name: str, phone: Optional[str], address: Dict, dry_run: bool = False) -> uuid.UUID: """Create a customer user from order data""" if email in self.email_to_user_id: return self.email_to_user_id[email] user_id = uuid.uuid4() if dry_run: print(f" [DRY RUN] Would create user: {email}") return user_id with self.conn.cursor() as cursor: # Create user cursor.execute(""" INSERT INTO account_user (id, email, first_name, last_name, is_staff, is_active, date_joined, password) VALUES (%s, %s, %s, %s, %s, %s, NOW(), %s) """, (user_id, email, first_name, last_name, False, True, '!')) # Create address addr_id = uuid.uuid4() cursor.execute(""" INSERT INTO account_address (id, first_name, last_name, company_name, street_address_1, street_address_2, city, postal_code, country, phone) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, (addr_id, address['first_name'], address['last_name'], address['company_name'], address['street_address_1'], address['street_address_2'], address['city'], address['postal_code'], address['country'], phone or '')) cursor.execute(""" INSERT INTO account_user_addresses (user_id, address_id) VALUES (%s, %s) """, (user_id, addr_id)) cursor.execute(""" UPDATE account_user SET default_billing_address_id = %s, default_shipping_address_id = %s WHERE id = %s """, (addr_id, addr_id, user_id)) cursor.execute(""" INSERT INTO wc_cod_customer_mapping (email, saleor_user_id, first_name, last_name, phone) VALUES (%s, %s, %s, %s, %s) """, (email, user_id, first_name, last_name, phone)) self.conn.commit() self.email_to_user_id[email] = user_id return user_id def import_order(self, order: CODOrder, create_users: bool = True, dry_run: bool = False) -> Optional[uuid.UUID]: """Import a COD order""" # Check existing with self.conn.cursor() as cursor: cursor.execute("SELECT saleor_order_id FROM wc_order_mapping WHERE wc_order_id = %s", (order.wc_order_id,)) if cursor.fetchone(): print(f" Order {order.order_number} already migrated") return None order_id = uuid.uuid4() channel_id = self.get_channel_id() saleor_status = ORDER_STATUS_MAP.get(order.status, 'UNCONFIRMED') # Get or create user user_id = None if create_users and order.customer_email: if order.customer_email not in self.email_to_user_id: self.create_user(order.customer_email, order.customer_first_name, order.customer_last_name, order.customer_phone, order.billing_address, dry_run) user_id = self.email_to_user_id.get(order.customer_email) if dry_run: paid_status = "PAID" if order.is_paid else "UNPAID" print(f" [DRY RUN] Would create order: {order.order_number} ({paid_status})") return order_id with self.conn.cursor() as cursor: # Create billing address bill_id = uuid.uuid4() cursor.execute(""" INSERT INTO order_orderbillingaddress (id, first_name, last_name, company_name, street_address_1, street_address_2, city, postal_code, country, phone) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, (bill_id, order.billing_address['first_name'], order.billing_address['last_name'], order.billing_address['company_name'], order.billing_address['street_address_1'], order.billing_address['street_address_2'], order.billing_address['city'], order.billing_address['postal_code'], order.billing_address['country'], order.billing_address['phone'])) # Create shipping address ship_id = uuid.uuid4() cursor.execute(""" INSERT INTO order_ordershippingaddress (id, first_name, last_name, company_name, street_address_1, street_address_2, city, postal_code, country, phone) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, (ship_id, order.shipping_address['first_name'], order.shipping_address['last_name'], order.shipping_address['company_name'], order.shipping_address['street_address_1'], order.shipping_address['street_address_2'], order.shipping_address['city'], order.shipping_address['postal_code'], order.shipping_address['country'], order.shipping_address['phone'])) # Insert order cursor.execute(""" INSERT INTO order_order ( id, created_at, updated_at, status, user_email, user_id, currency, total_gross_amount, total_net_amount, shipping_price_gross_amount, shipping_price_net_amount, shipping_method_name, channel_id, billing_address_id, shipping_address_id, billing_address, shipping_address, metadata, origin, should_refresh_prices, tax_exemption, discount_amount, display_gross_prices, customer_note ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, ( order_id, order.date_created, order.date_modified, saleor_status, order.customer_email, user_id, order.currency, order.total, order.subtotal, order.shipping, order.shipping, order.shipping_method, channel_id, bill_id, ship_id, json.dumps(order.billing_address), json.dumps(order.shipping_address), json.dumps({ 'woo_order_id': order.wc_order_id, 'cod_payment': True, 'payment_collected_on_delivery': order.is_paid }), 'BULK_CREATE', False, False, 0.0, True, order.customer_note )) # Insert order lines for item in order.items: cursor.execute("SELECT id FROM product_productvariant WHERE sku = %s", (item['sku'],)) variant = cursor.fetchone() variant_id = variant[0] if variant else None qty = item['quantity'] unit_net = item['subtotal'] / qty if qty else 0 unit_gross = (item['subtotal'] + item['tax']) / qty if qty else 0 cursor.execute(""" INSERT INTO order_orderline (id, order_id, product_name, product_sku, quantity, currency, unit_price_net_amount, unit_price_gross_amount, total_price_net_amount, total_price_gross_amount, unit_discount_amount, unit_discount_type, tax_rate, is_shipping_required, variant_id, created_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, (uuid.uuid4(), order_id, item['name'], item['sku'], qty, order.currency, unit_net, unit_gross, item['subtotal'], item['subtotal'] + item['tax'], 0.0, 'FIXED', '0.15', True, variant_id, order.date_created)) # For COD: Create a dummy payment record for completed orders # This marks that payment was collected on delivery if order.is_paid: cursor.execute(""" INSERT INTO payment_payment ( id, gateway, is_active, to_confirm, order_id, total, captured_amount, currency, charge_status, partial, modified_at, created_at ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, ( uuid.uuid4(), 'mirumee.payments.dummy', # Dummy gateway for COD False, # Not active (completed) False, order_id, order.total, order.total, # Fully captured (collected on delivery) order.currency, 'FULLY_CHARGED', False, order.date_modified, order.date_created )) # Record mapping cursor.execute(""" INSERT INTO wc_order_mapping (wc_order_id, saleor_order_id, customer_email) VALUES (%s, %s, %s) """, (order.wc_order_id, order_id, order.customer_email)) self.conn.commit() paid_marker = "✓" if order.is_paid else "○" print(f" Created order: {order.order_number} {paid_marker}") return order_id def main(): parser = argparse.ArgumentParser(description='Migrate WooCommerce COD Orders to Saleor') parser.add_argument('--orders', action='store_true', help='Migrate orders') parser.add_argument('--create-users', action='store_true', help='Create customer accounts from order emails') parser.add_argument('--dry-run', action='store_true', help='Preview only') parser.add_argument('--limit', type=int, help='Limit order count') parser.add_argument('--status', type=str, help='Filter by status (wc-completed, etc)') args = parser.parse_args() if not args.orders: parser.print_help() sys.exit(1) print("=== WooCommerce COD Orders to Saleor Migration ===\n") print("Connecting...") try: exporter = CODOrderExporter(WP_DB_CONFIG) importer = CODSaleorImporter(SALEOR_DB_CONFIG) print("Connected!\n") except Exception as e: print(f"Failed: {e}") sys.exit(1) print("Fetching orders...") orders = exporter.get_orders(limit=args.limit, status=args.status) print(f"Found {len(orders)} orders\n") # Stats paid_count = sum(1 for o in orders if o.is_paid) unpaid_count = len(orders) - paid_count print(f"Breakdown: {paid_count} paid (delivered), {unpaid_count} unpaid (pending/processing)\n") print("Migrating...") for i, order in enumerate(orders, 1): status_marker = "✓" if order.is_paid else "○" print(f"[{i}/{len(orders)}] {order.order_number} {status_marker} {order.customer_email}") try: importer.import_order(order, create_users=args.create_users, dry_run=args.dry_run) except Exception as e: print(f" ERROR: {e}") print(f"\n{'Preview' if args.dry_run else 'Migration'} complete!") print(f"Total orders: {len(orders)}") if args.create_users: print(f"Customers created: {len(importer.email_to_user_id)}") if __name__ == '__main__': main()