How can I export a zip code with the suffix in the same field?

Question

I send my addresses to a mailing house for a bulk magazine mailing. They require my zip+4 in one field. I can fix this in LibreOffice or Excel, but I have to be careful that my spreadsheet app doesn't eat the zip codes' leading zeroes. Is there a better solution?

Answer 1

I wrote this little snippet of code in a larger extension, which will automatically append the postal code suffix to postal codes when exporting postal codes:

/**
 * On export, append the postal_code_suffix to the postal_code.
 */
function tweaks_civicrm_export(&$exportTempTable, &$headerRows, &$sqlColumns, &$exportMode, &$componentTable, &$ids) {
  // Primary postal code will have a sqlColumns['postal_code'] = 'postal_code'.
  // Non-primary will have the location type appended - e.g. work_postal_code.
  foreach ($sqlColumns as $key => $column) {
    if (substr($key, -11) == 'postal_code') {
      // Primary postal_code.
      if ($key == 'postal_code') {
        // Update the primary postal code.
        $sql = "UPDATE $exportTempTable " .
        "LEFT JOIN civicrm_address ca ON $exportTempTable.civicrm_primary_id = ca.contact_id AND ca.is_primary = 1 " .
        "SET $exportTempTable.postal_code = IF(ca.postal_code_suffix, CONCAT(ca.postal_code,'-',ca.postal_code_suffix), ca.postal_code)";
        CRM_Core_DAO::singleValueQuery($sql);
      }
      // Non-primary postal code
      else {
        $locationType = str_replace('_', ' ', substr($key, 0, -12));
        $sql = "UPDATE $exportTempTable " .
        "LEFT JOIN civicrm_address ca ON $exportTempTable.civicrm_primary_id = ca.contact_id " .
        "JOIN civicrm_location_type clt ON ca.location_type_id = clt.id AND clt.name = '$locationType' " .
        "SET $exportTempTable.$key = IF(ca.postal_code_suffix, CONCAT(ca.postal_code,'-',ca.postal_code_suffix), ca.postal_code)";
        CRM_Core_DAO::singleValueQuery($sql);
      }
    }
  }
}