关于consolibyte mysql_mirror双向同步的地址及数据同步问题咨询

Hey there! Let's break down solutions for your two mysql_mirror script issues:

The mysql_mirror script lets you manipulate data during sync, so you can tweak how addresses are pulled from QuickBooks Desktop (QBD) and pushed to your target database. Here's how to approach it:

• Identify address fields in QBD: Typically, QBD stores customer addresses split into fields like BillAddr1 (often the contact name), BillAddr2 (street address), BillCity, BillState, BillPostalCode, and BillCountry. Your goal is to exclude the name field(s) and combine the rest into a structured string Google Maps can parse.
• Modify the script's address mapping: Find the section in the script where customer data is synced (look for functions handling Customer entities). Instead of pulling all address fields, only include the street, city, state, zip, and country. For example, if you're using PHP (common with Consolibyte scripts), you could build the clean address like this:

  $clean_bill_address = trim($qb_customer['BillAddr2'] . ', ' . $qb_customer['BillCity'] . ', ' . $qb_customer['BillState'] . ' ' . $qb_customer['BillPostalCode']);
  

• Handle single-field addresses (if applicable): If QBD stores the full address in one field (including the name), use regex or string splitting to strip the name line. For example, if the name is always the first line, split the address by newlines and skip the first element:

  $address_lines = explode("\n", $qb_customer['FullBillAddress']);
  $clean_address = implode(', ', array_slice($address_lines, 1));
  

• Validate with Google Maps API: After modifying the script, test a few cleaned addresses with the Geocoding API to ensure they're parseable. Adjust the formatting if some addresses fail to resolve (e.g., add country code for international addresses).

If these fields aren't making it to your target database, the issue is almost certainly missing field mappings in the mysql_mirror script. Here's how to fix it:

• Locate QBD field names: In QBD, customer phone numbers are usually in the Phone field, and websites are stored in WebAddr. Confirm these by inspecting raw QBD data pulls or checking the QBD SDK references.
• Update the sync mapping: Find the part of the script that maps QBD customer fields to your target database columns. Add entries for Phone and WebAddr to the mapping array. For example:

  $customer_mapping = [
      'Name' => 'customer_name',
      'Phone' => 'customer_phone', // Add this line
      'WebAddr' => 'customer_website', // Add this line
      // ... other existing mappings
  ];
  

• Check bidirectional sync logic: Since you're using two-way sync, make sure the reverse mapping (from target database back to QBD) also includes these fields. Look for the section where data is pushed from your target to QBD and add the corresponding field mappings there.
• Verify QBD access permissions: Ensure your QBD connection has permission to read/write these fields. Sometimes, restricted access in QBD can prevent certain data from being pulled via the API.

Hope these steps resolve both issues! If you need help pinpointing the exact lines in the mysql_mirror script to modify, feel free to share a snippet of the customer sync code.

内容的提问来源于stack exchange，提问作者sehri