""" Sync members (Customers + Suppliers) from Cin7 Contacts to yudao member_user + member_address Cin7 Contact fields: id, type, email, firstName, lastName, phone, mobile, company, address1, address2, city, state, postCode, country, isActive, createdDate, modifiedDate, priceColumn, subGroup yudao member_user: id, email, subGroup, mobile, nickname, name, password, company, country, state, city, post_code, type, status, sex, register_ip, register_terminal, level_id, groupId, creator, create_time, updater, tenant_id yudao member_address: id, user_id, name, mobile, email, area_id, detail_address, default_status, lat, lng, ... Business rules: 1. Sync ALL contacts — both Customer and Supplier 2. Use Cin7 id as yudao id (same value) 3. If address fields exist, create member_address row (new members only) 4. priceColumn maps to yudao level_id via member_level lookup """ from db import query_all, insert_many, update_many # Map Cin7 contact_id → yudao member_id (same value) MEMBER_ID_MAP = {} # Fixed BCrypt hash for password "123456" — mirrors yudao's sys.user.init-password config MEMBER_PASSWORD_HASH = "$2a$04$.kgDnlVmV5bKEyrDnStVleIDcWKFIpkz.joK0vT.aww9ozVEfUKd6" # Level id cache: priceColumn (lowercase) -> yudao level id LEVEL_ID_MAP = {} def _str(val): if val is None: return "" return str(val).strip() def _load_level_map(): """Load yudao member_level: priceColumn (lowercase) -> id.""" global LEVEL_ID_MAP rows = query_all("SELECT id, name FROM member_level WHERE status=0 AND deleted=0") LEVEL_ID_MAP = {r["name"].lower(): r["id"] for r in rows} if rows else {} def _load_area_cache(): """ Load system_area names into memory for fast lookup. Returns a dict {name_lower -> id}, or {} if table doesn't exist. """ try: rows = query_all("SELECT id, name FROM system_area") return {r["name"].lower(): r["id"] for r in rows} if rows else {} except Exception: return {} def sync_members(cin7_api, tenant_id=0): """ Fetch ALL Cin7 Contacts and sync to yudao in bulk. Strategy: 1. Load existing member ids in one query_all. 2. Load area cache in one query_all. 3. Split contacts into insert / update lists. 4. One insert_many for new members. 5. One update_many for existing members. 6. One insert_many for new member addresses. No per-row DB round-trips. """ global MEMBER_ID_MAP MEMBER_ID_MAP = {} print("\n=== Sync Members (ALL Contacts) ===") _load_level_map() print(f"[member] Level map loaded: {LEVEL_ID_MAP}") # Pre-load existing members and area lookup in parallel (both are read-only) existing_rows = query_all("SELECT id FROM member_user WHERE deleted=0") existing_ids = {r["id"] for r in existing_rows} area_cache = _load_area_cache() raw = cin7_api.get_contacts() contacts = raw.get("d", []) if isinstance(raw, dict) else raw total = len(contacts) print(f"[member] Fetched {total} contacts, building batch...") to_insert = [] # new member_user rows to_update = [] # existing member_user rows addr_rows = [] # new member_address rows (only for new members) for contact in contacts: cin7_id = contact.get("id") if not cin7_id: continue email = _str(contact.get("email")) c_type = _str(contact.get("type")) mobile = _str(contact.get("mobile") or contact.get("phone")) first = _str(contact.get("firstName")) last = _str(contact.get("lastName")) company = _str(contact.get("company")) country = _str(contact.get("country")) state = _str(contact.get("state")) city = _str(contact.get("city")) post_code = _str(contact.get("postCode")) status = 0 if contact.get("isActive", True) else 1 price_col = _str(contact.get("priceColumn")).lower() level_id = LEVEL_ID_MAP.get(price_col, 0) sub_group = _str(contact.get("subGroup")) MEMBER_ID_MAP[cin7_id] = cin7_id if cin7_id in existing_ids: to_update.append(( email, mobile, first, last, company, country, state, city, post_code, c_type, status, level_id, sub_group, cin7_id, )) else: to_insert.append(( cin7_id, email, mobile, first, # nickname = firstName last, # name = lastName MEMBER_PASSWORD_HASH, company, country, state, city, post_code, c_type, status, level_id, sub_group, tenant_id, )) # Build address for new member (postal fields) p_addr = _str(contact.get("postalAddress1")) p_city = _str(contact.get("postalCity")) p_state = _str(contact.get("postalState")) p_country = _str(contact.get("postalCountry")) p_post = _str(contact.get("postalPostCode")) full_addr = " ".join(filter(None, [p_addr, p_city, p_state, p_country, p_post])).strip() if full_addr: lookup_name = (p_city or p_state or p_country).lower() area_id = area_cache.get(lookup_name, 0) addr_rows.append(( cin7_id, last, mobile, email, area_id, full_addr, tenant_id, )) # Bulk INSERT new members if to_insert: insert_many( "INSERT INTO member_user " "(id, email, mobile, nickname, name, password, company, country, state, city, " "post_code, type, status, sex, register_ip, register_terminal, level_id, " "sub_group, creator, create_time, updater, tenant_id) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " "0, '0.0.0.0', 20, %s, %s, 'cin7_sync', NOW(), 'cin7_sync', %s)", to_insert, ) print(f"[member] Inserted {len(to_insert)} new members") # Bulk UPDATE existing members if to_update: update_many( "UPDATE member_user SET " "email=%s, mobile=%s, nickname=%s, name=%s, " "company=%s, country=%s, state=%s, city=%s, " "post_code=%s, type=%s, status=%s, level_id=%s, " "sub_group=%s, update_time=NOW(), updater='cin7_sync' " "WHERE id=%s", to_update, ) print(f"[member] Updated {len(to_update)} existing members") # Bulk INSERT new addresses (only for newly created members) if addr_rows: insert_many( "INSERT INTO member_address " "(user_id, name, mobile, email, area_id, detail_address, " "default_status, creator, create_time, updater, tenant_id) " "VALUES (%s, %s, %s, %s, %s, %s, 1, 'cin7_sync', NOW(), 'cin7_sync', %s)", addr_rows, ) print(f"[member] Inserted {len(addr_rows)} member addresses") print(f"\n[member] Done. {len(to_insert)} created, {len(to_update)} updated, " f"{len(addr_rows)} addresses added.") return MEMBER_ID_MAP