[BUG] GUEST customer creation - duplicate entry - probably guest orders only

[WeLoTech]

Current Behavior

On initial setup I frequently encounter the same error: "WooCommerce Error."

It appears that during the auto-sync of a customer and address using the woocommerce_fusion plugin, an IntegrityError is raised due to a duplicate entry for the primary key. This leads to a DuplicateEntryError being thrown. This error occurs approximately 30 times and seems to be associated exclusively with all guest orders (maybe only the guest orders that order multiple times with the same guest email).

Traceback (most recent call last):
  File "apps/frappe/frappe/model/base_document.py", line 554, in db_insert
    frappe.db.sql(
  File "apps/frappe/frappe/database/database.py", line 227, in sql
    self._cursor.execute(query, values)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 563, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 825, in _read_query_result
    result.read()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "env/lib/python3.10/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "env/lib/python3.10/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1062, "Duplicate entry '' for key 'PRIMARY'")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "apps/woocommerce_fusion/woocommerce_fusion/tasks/sync_sales_orders.py", line 472, in create_or_link_customer_and_address
    customer.save()
  File "apps/frappe/frappe/model/document.py", line 337, in save
    return self._save(*args, **kwargs)
  File "apps/frappe/frappe/model/document.py", line 359, in _save
    return self.insert()
  File "apps/frappe/frappe/model/document.py", line 302, in insert
    self.db_insert(ignore_if_duplicate=ignore_if_duplicate)
  File "apps/frappe/frappe/model/base_document.py", line 581, in db_insert
    raise frappe.DuplicateEntryError(self.doctype, self.name, e)
frappe.exceptions.DuplicateEntryError: ('Customer', '', IntegrityError(1062, "Duplicate entry '' for key 'PRIMARY'"))

Customer Data{'name': '', 'owner': 'admin', 'creation': '<TIMESTAMP>', 'modified': '<TIMESTAMP>', 'modified_by': 'admin', 'docstatus': 0, 'idx': 0, 'woocommerce_email': '<MAIL>', 'naming_series': 'CUST-.YYYY.-', 'woocommerce_customer_id': None, 'salutation': None, 'customer_name': ' ', 'customer_type': 'Company', 'customer_group': None, 'territory': None, 'gender': None, 'lead_name': None, 'opportunity_name': None, 'prospect_name': None, 'account_manager': None, 'image': None, 'default_currency': None, 'default_bank_account': None, 'default_price_list': None, 'is_internal_customer': 0, 'represents_company': None, 'market_segment': None, 'industry': None, 'customer_pos_id': None, 'website': None, 'language': 'de', 'customer_details': None, 'customer_primary_address': None, 'primary_address': None, 'customer_primary_contact': None, 'mobile_no': None, 'email_id': None, 'tax_id': None, 'tax_category': None, 'tax_withholding_category': None, 'payment_terms': None, 'loyalty_program': None, 'loyalty_program_tier': None, 'default_sales_partner': None, 'default_commission_rate': 0.0, 'so_required': 0, 'dn_required': 0, 'is_frozen': 0, 'sync_with_woocommerce': 0, 'disabled': 0, 'woocommerce_server': None, 'doctype': 'Customer', 'accounts': [], 'companies': [], 'portal_users': [], 'credit_limits': [], 'sales_team': [], '__islocal': True, '__unsaved': 1}

Steps To Reproduce

1. There may be multiple orders both by registered and by non-registered customers (guests), optionally with the same guest email
2. The woocommerce_email is filled with the guest mail
3. Setup the plugin initially to a Woocommerce instance that fulfills above mentioned criteria
4. The auto sync will have errors in error-log

Expected Behavior

Guests who order are also created as customers in the erp with the invoice details added by the guest and woocommerce_email just like registered users would order.

Anything else?

What would be workarounds to fix manually or programmatically while a fix is created?

Environment and Versions

erpnext 15.36.1
frappe 15.41.0
woocommerce_fusion 1.7.3

Operating System

Other

What browsers are you seeing the problem on?

No response

[WeLoTech]

@dvdl16 any pointers here?

[dvdl16]

@WeLoTech thanks for raising the issue. Unfortunately this part of the code was inherited from ERPNext's v14 WooCommerce integration. I'm traveling until the 9th of Oct, can only look into this when I'm back

[WeLoTech]

Okay. I will have a look in the meantime to see if I can deploy a fix.

[WeLoTech]

I've made some quick fixes addressing the core issue with customer generation. Previously, the identifier for customers/contacts was solely based on woocommerce_email, which led to complications.

Key Changes:

1. Initial Fix: Resolved the business/individual ordering problem.
2. Recent Commit: Implemented a more robust solution for the identifier issue.

New Unique Identifier: Instead of relying solely on woocommerce_email, I've introduced a new mechanism. If no email exists, an order-based guest user is created. This approach prevents the creation of a single ERPNext customer without an email from being overwritten by a new customer (i.e., the same user being replaced by a different user).

I believe this resolves the underlying issue. Please review and let me know if there are any further concerns.

[dvdl16]

Fixed in #118 and released in v1.7.4