Writing Errors to a Salesforce Error Object

#   _____           _              _____        __      
#  |  __ \         (_)            |_   _|      / _|     
#  | |__) |___  ___ _ _ __   ___    | |  _ __ | |_ ___  
#  |  _  // _ \/ __| | '_ \ / _ \   | | | '_ \|  _/ _ \ 
#  | | \ \  __/ (__| | |_) |  __/  _| |_| | | | || (_) |
#  |_|  \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ 
#                    | |                                
#                    |_|

# Project Name - Writing Errors to a Salesforce Error Object

# Recipe URL - https://app.lingk.io/a/10932/tf/18481

# Description - This recipe exhibits a pattern where you can utilize the 'error' response on Salesforce updates/inserts to update a Salesforce error object for issue tracking.

## Recipe steps:
                # 1. Get persons from colleague ethos API.
                # 2. Create a list of ethos person ids with its address
                # 3. Create payload to insert contact in SF.
                # 4. Insert/Update contact in SF by using Colleague_Id__c.

# Systems - Ethos, Salesforce
# Connectors - JSON
# Data Flows - Single Direction
# Connection Type - API

# Add Recipe notes / Change log information here!

#    _____                            _                 
#   / ____|                          | |                
#  | |     ___  _ __  _ __   ___  ___| |_ ___  _ __ ___ 
#  | |    / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __|
#  | |___| (_) | | | | | | |  __/ (__| || (_) | |  \__ \
#   \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_|  |___/
#

# CONNECTORS specify what data will be pulled into the in-memory database during processing

connectors:

# Configure Ethos & Salesforce connectors in your Environment before running this recipe
  # Ethos Setup - https://help.lingk.io/en/articles/292-ellucian-ethos-connector-setup-guide
  # Salesforce Setup - https://help.lingk.io/en/articles/267-salesforce-connector-setup-guide

# Colleague_Id__c size increased from size(7) to size(40).

###### Start - Ethos Reader connectors ######

# Ethos Persons
- 
    name: ethosPersons
    type: ethosReader
    delayedRead: true
    properties:
      path: /api/persons?criteria={"roles":[{"role":"employee"}]}
      dataModelVersion: 12.3.0
    schema:
      fields: 
        - name: 'addresses' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'address' 
                  type: 'object' 
                  objectSchema: 
                    name: objectSchemaSchema 
                    fields: 
                      - name: 'id' 
                        type: 'string' 
                      - name: 'endOn' 
                        type: 'string' 
                      - name: 'preference' 
                        type: 'string' 
                      - name: 'startOn' 
                        type: 'string' 
                      - name: 'type' 
                        type: 'object' 
                        objectSchema: 
                          name: objectSchemaSchema 
                          fields: 
                            - name: 'addressType' 
                              type: 'string' 
                            - name: 'detail' 
                              type: 'object' 
                              objectSchema: 
                                name: objectSchemaSchema 
                                fields: 
                                  - name: 'id' 
                                    type: 'string' 
        - name: 'alternativeCredentials' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'type' 
                  type: 'object' 
                  objectSchema: 
                    name: objectSchemaSchema 
                    fields: 
                      - name: 'id' 
                        type: 'string' 
                      - name: 'value' 
                        type: 'string' 
        - name: 'citizenshipCountry' 
          type: 'string' 
        - name: 'citizenshipStatus' 
          type: 'object' 
          objectSchema: 
            name: objectSchemaSchema 
            fields: 
              - name: 'category' 
                type: 'string' 
              - name: 'detail' 
                type: 'object' 
                objectSchema: 
                  name: objectSchemaSchema 
                  fields: 
                    - name: 'id' 
                      type: 'string' 
        - name: 'countryOfBirth' 
          type: 'string' 
        - name: 'credentials' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'type' 
                  type: 'string' 
                - name: 'value' 
                  type: 'string' 
        - name: 'dateDeceased' 
          type: 'string' 
        - name: 'dateOfBirth' 
          type: 'string' 
        - name: 'emails' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'address' 
                  type: 'string' 
                - name: 'preference' 
                  type: 'string' 
                - name: 'type' 
                  type: 'object' 
                  objectSchema: 
                    name: objectSchemaSchema 
                    fields: 
                      - name: 'detail' 
                        type: 'object' 
                        objectSchema: 
                          name: objectSchemaSchema 
                          fields: 
                            - name: 'id' 
                              type: 'string' 
                            - name: 'emailType' 
                              type: 'string' 
        - name: 'ethnicity' 
          type: 'object'
          objectSchema: 
            name: objectSchemaSchema 
            fields: 
              - name: 'ethnicGroup' 
                type: 'object' 
                objectSchema: 
                  name: objectSchemaSchema 
                  fields: 
                    - name: 'id' 
                      type: 'string' 
                    - name: 'reporting' 
                      type: 'array' 
                      items: 
                        name: 'arrayElement' 
                        type: 'object' 
                        objectSchema: 
                          name: objectSchemaSchema 
                          fields: 
                            - name: 'country' 
                              type: 'object' 
                              objectSchema: 
                                name: objectSchemaSchema 
                                fields: 
                                  - name: 'code' 
                                    type: 'string' 
                                  - name: 'ethnicCategory' 
                                    type: 'string' 
        - name: 'gender' 
          type: 'string' 
        - name: 'id' 
          type: 'string' 
        - name: 'identityDocuments' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'country' 
                  type: 'object' 
                  objectSchema: 
                    name: objectSchemaSchema 
                    fields: 
                      - name: 'code' 
                        type: 'string' 
                      - name: 'region' 
                        type: 'object'
                        objectSchema: 
                          name: objectSchemaSchema 
                          fields: 
                            - name: 'code' 
                              type: 'string' 
                - name: 'documentId' 
                  type: 'string' 
                - name: 'expiresOn' 
                  type: 'string' 
                - name: 'type' 
                  type: 'object' 
                  objectSchema: 
                    name: objectSchemaSchema 
                    fields: 
                      - name: 'category' 
                        type: 'string' 
                      - name: 'detail' 
                        type: 'object' 
                        objectSchema: 
                          name: objectSchemaSchema 
                          fields: 
                            - name: 'id' 
                              type: 'string' 
        - name: 'interests' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'id' 
                  type: 'string' 
        - name: 'maritalStatus' 
          type: 'object' 
          objectSchema: 
            name: objectSchemaSchema 
            fields: 
            - name: 'detail' 
              type: 'object' 
              objectSchema: 
                name: objectSchemaSchema 
                fields: 
                  - name: 'id' 
                    type: 'string' 
                  - name: 'maritalCategory' 
                    type: 'string' 
        - name: 'names' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'firstName' 
                  type: 'string' 
                - name: 'fullName' 
                  type: 'string' 
                - name: 'lastName' 
                  type: 'string' 
                - name: 'lastNamePrefix' 
                  type: 'string' 
                - name: 'middleName' 
                  type: 'string' 
                - name: 'pedigree' 
                  type: 'string' 
                - name: 'preference' 
                  type: 'string' 
                - name: 'title' 
                  type: 'string' 
                - name: 'type' 
                  type: 'object' 
                  objectSchema: 
                    name: objectSchemaSchema 
                    fields: 
                      - name: 'category' 
                        type: 'string' 
                      - name: 'detail' 
                        type: 'object' 
                        objectSchema: 
                          name: objectSchemaSchema 
                          fields: 
                            - name: 'id' 
                              type: 'string' 
        - name: 'phones' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'number' 
                  type: 'string' 
                - name: 'type' 
                  type: 'object' 
                  objectSchema: 
                    name: objectSchemaSchema 
                    fields: 
                      - name: 'detail' 
                        type: 'object' 
                        objectSchema: 
                          name: objectSchemaSchema 
                          fields: 
                            - name: 'id' 
                              type: 'string' 
                            - name: 'phoneType' 
                              type: 'string' 
        - name: 'privacyStatus' 
          type: 'object' 
          objectSchema: 
            name: objectSchemaSchema 
            fields: 
              - name: 'detail' 
                type: 'object' 
                objectSchema: 
                  name: objectSchemaSchema 
                  fields: 
                    - name: 'id' 
                      type: 'string' 
                    - name: 'privacyCategory' 
                      type: 'string' 
        - name: 'races' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'race' 
                  type: 'object' 
                  objectSchema: 
                    name: objectSchemaSchema 
                    fields: 
                      - name: 'id' 
                        type: 'string' 
                      - name: 'reporting' 
                        type: 'array' 
                        items: 
                          name: 'arrayElement' 
                          type: 'object' 
                          objectSchema: 
                            name: objectSchemaSchema 
                            fields: 
                              - name: 'country' 
                                type: 'object' 
                                objectSchema: 
                                  name: objectSchemaSchema 
                                  fields: 
                                    - name: 'code' 
                                      type: 'string' 
                                    - name: 'racialCategory' 
                                      type: 'string' 
        - name: 'religion' 
          type: 'object' 
          objectSchema: 
            name: objectSchemaSchema 
            fields: 
              - name: 'id' 
                type: 'string' 
        - name: 'roles' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'role' 
                  type: 'string'
        - name: 'socialMedia' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'preference' 
                  type: 'string'
                - name: 'address' 
                  type: 'string'
                - name: 'type' 
                  type: 'object' 
                  objectSchema: 
                    name: objectSchemaSchema 
                    fields: 
                      - name: 'socialMediaType' 
                        type: 'string' 
                      - name: 'detail' 
                        type: 'object' 
                        objectSchema: 
                          name: objectSchemaSchema 
                          fields: 
                            - name: 'id' 
                              type: 'string'
        - name: 'veteranStatus'  
          type: 'object' 
          objectSchema: 
            name: objectSchemaSchema 
            fields: 
              - name: 'category' 
                type: 'string'
              - name: 'detail' 
                type: 'object' 
                objectSchema: 
                  name: objectSchemaSchema 
                  fields: 
                    - name: 'id' 
                      type: 'string'

# Ethos Address
- 
    name: ethosAddresses
    type: ethosReader
    parameterizedBy: addressIdsList
    delayedRead: true
    properties:
      path: /api/addresses/{{var.id}}
      dataModelVersion: 11.1.0
    schema:
      fields:
        - name: '__parameters' 
          type: 'object' 
          objectSchema: 
            name: objectSchemaSchema 
            fields: 
              - name: 'id' 
                type: 'string' 
        - name: 'addressLines' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'string' 
        - name: 'geographicAreas' 
          type: 'array' 
          items: 
            name: 'arrayElement' 
            type: 'object' 
            objectSchema: 
              name: objectSchemaSchema 
              fields: 
                - name: 'id' 
                  type: 'string' 
        - name: 'id' 
          type: 'string' 
        - name: 'place' 
          type: 'object' 
          objectSchema: 
            name: objectSchemaSchema 
            fields: 
              - name: 'country' 
                type: 'object' 
                objectSchema: 
                  name: objectSchemaSchema 
                  fields: 
                    - name: 'code' 
                      type: 'string' 
                    - name: 'locality' 
                      type: 'string' 
                    - name: 'postalCode' 
                      type: 'string' 
                    - name: 'postalTitle' 
                      type: 'string'
                    - name: 'title' 
                      type: 'string'
                    - name: 'region' 
                      type: 'object'
                      objectSchema: 
                        name: objectSchemaSchema 
                        fields: 
                          - name: 'code' 
                            type: 'string' 
                          - name: 'title' 
                            type: 'string' 
                    - name: 'subRegion' 
                      type: 'object' 
                      objectSchema: 
                        name: objectSchemaSchema 
                        fields: 
                          - name: 'code' 
                            type: 'string' 
                          - name: 'title' 
                            type: 'string'

###### End - Ethos Reader connectors ######

###### Start - Salesforce Reader connectors ######

# Salesforce Contact Reader
-
    name: salesforceContactReader
    type: salesforceReader
    parameterizedBy: batchedContacts
    delayedRead: true
    properties:
      useBearerToken: true
      query: SELECT 
              Id, 
              Colleague_Id__c
             FROM Contact 
             WHERE Colleague_Id__c IN (
                    {% for element in var.output %}
                    '{{element.id}}'{% if not loop.last %},{% endif %}
                    {% endfor %}
                   )
    schema:
      fields:
        - name: 'Id'
          type: 'string'
        - name: 'Colleague_Id__c'
          type: 'string'

# Salesforce Address Reader
-
    name: salesforceAddressReader
    type: salesforceReader
    delayedRead: true
    parameterizedBy: parentContactIdList
    properties:
      useBearerToken: true
      query: SELECT 
              Id, 
              hed__Address_Type__c, 
              hed__Parent_Contact__c, 
              hed__Default_Address__c, 
              hed__MailingStreet__c, 
              hed__MailingCity__c, 
              hed__MailingState__c 
             FROM hed__Address__c
             WHERE hed__Parent_Contact__c IN (
                {% for element in var.output %}
                '{{element.id}}'{% if not loop.last %},{% endif %}
                {% endfor %}
              )
    schema:
      fields:
        - name: 'Id'
          type: 'String'
        - name: 'hed__Address_Type__c'
          type: 'string'  
        - name: 'hed__Parent_Contact__c'
          type: 'string' 
        - name: 'hed__Default_Address__c'
          type: 'string'
        - name: 'hed__MailingStreet__c'
          type: 'string'  
        - name: 'hed__MailingCity__c'
          type: 'string' 
        - name: 'hed__MailingState__c'
          type: 'string'

###### End - Salesforce Reader connectors ######

###### Start - Salesforce Writer connectors ######

# Salesforce address writer
-
    name: salesforceAddressWriter
    type: salesforceBulkWriter
    onError: continue
    isSerial: true
    properties:
      onError: continue
      useBearerToken: true
      sfObject: hed__Address__c 
      batchSize: 50

# Salesforce Error Writer
-
    name: salesforceErrorWriter
    type: salesforceBulkWriter
    onError: continue
    isSerial: true
    properties:
      onError: continue
      useBearerToken: true
      sfObject: hed__Error__c 
      batchSize: 100

###### End - Salesforce Writer connectors ######

###### Start - BULK V2 Error Json ######

-
    name: salesforceErrorSchema
    type: json
    delayedRead: true
    parameterizedBy: salesforceError
    properties:
      jsonObject: |
        {
          "Id": "{{var.Id}}",
          "Colleague_Id__c": "{{var.Colleague_Id__c}}",
          "hed__Parent_Contact__c": "{{var.hed__Parent_Contact__c}}",  
          "sf__Error": "{{var.sf__Error|replace('\r', '\\r')|replace('\n', '\\n')|replace('"', '')|replace('[', '')|replace(']', '')}}"
        }   
    schema:
      fields:
        - name: 'Id'
          type: 'string' 
        - name: 'Colleague_Id__c'
          type: 'string' 
        - name: 'hed__Parent_Contact__c'
          type: 'string' 
        - name: 'sf__Error'
          type: 'string'

###### End - BULK V2 Error Json ######

#   _______        _        
#  |__   __|      | |       
#     | | __ _ ___| | _____ 
#     | |/ _` / __| |/ / __|
#     | | (_| \__ \   <\__ \
#     |_|\__,_|___/_|\_\___/

# Custom functions that can be used within a recipe.
tasks:
-
    name: batchTask
    type: dataOperation 
    function: batch   
    parameters:
       inputBatchGroupBy: # required
         id: desc      
       inputBatchSize: 200 # inputBatchSize # required (to test)
       inputBatchFields: "id" # comma delimited for other fields #inputBatchFields #required
       batchedColumnAlias: output # outputColumnName - optional with a default of "output"
       # inputTable: # optional
       # result: #optional

#    _____ _        _                            _       
#   / ____| |      | |                          | |      
#  | (___ | |_ __ _| |_ ___ _ __ ___   ___ _ __ | |_ ___ 
#   \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __|
#   ____) | || (_| | ||  __/ | | | | |  __/ | | | |_\__ \
#  |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/

# STATEMENTS specify how the data should be processed while in memory

statements:

#******************************************************************** D I S C L A I M E R ***********************************************************************************************
#                                                                                                                                                                                       * 
# Note that in an effort to keep recipes optimized for DPH (Data Processing Hours), print statements should be commented out after development has concluded for a recipe.              *
# For more information on DPH optimization, please visit the following help article - https://help.lingk.io/en/articles/212-minimizing-data-processing-hours-on-the-lingk-platform      *
#                                                                                                                                                                                       * 
#******************************************************************** D I S C L A I M E R ***********************************************************************************************

# Get newly created Contacts from Salesforce
- statement: REFRESH salesforceContactReader
- statement: (salesforceContactReaderCount) => SELECT COUNT(*) FROM salesforceContactReader
#- statement: print salesforceContactReaderCount

###### Start - inline addresses and list them ######

# The inline function explodes an array of structs into a table. Uses column names col1, col2, etc. by default unless specified otherwise.
- statement: (inlineAddress) => SELECT
                                  id,
                                  inline(addresses)
                                FROM ethosPersons
#- statement: print inlineAddress

# Join the recent contacts created in Salesforce from 'salesforceContactReader' with the inlineAddress data retrieved in the previous statement
- statement: (addressInfoFromEthosPersonModalForNewlyCreatedContact) =>
                  SELECT
                    sf.Id                     `sfContactId`,
                    a.*
                  FROM inlineAddress a
                  INNER JOIN salesforceContactReader sf ON TRIM(a.id) = TRIM(sf.Colleague_Id__c)

# Count and print the amount of records retrieved from the JOIN statement 
- statement: (addressInfoFromEthosPersonModalForNewlyCreatedContactCount) => SELECT COUNT(*) FROM addressInfoFromEthosPersonModalForNewlyCreatedContact
#- statement: print addressInfoFromEthosPersonModalForNewlyCreatedContactCount

# After joining the 2 tables (the salesforce contacts and ethos persons addressess), retrieve the id from the new table
- statement: (addressIdsList) => SELECT
                                  address.id                  `id`
                                 FROM addressInfoFromEthosPersonModalForNewlyCreatedContact
#- statement: print addressIdsList

# Ethos Addresses
# Refresh the ethosAddresses connector (line 444) providing the id value through the ParametrizedBy as a list in order to update the URL path
- statement: REFRESH ethosAddresses
- statement: (ethosAddressesCount) => SELECT COUNT(*) FROM ethosAddresses
#- statement: print ethosAddressesCount

###### End - inline addresses and list them ######

###### Start - Create payload to insert address in SF ######

# Information retrieved from ethosAddresses joins the data from addressInfoFromEthosPersonModalForNewlyCreatedContact using the Id as the key
# CASE function works as a dictionary and retrieves a specific value depending on the type field value pulled from the table as long as it is not null.
- statement: (addressToCreateInSf) =>
                  SELECT
                    ac.sfContactId                                              `hed__Parent_Contact__c`,
                    IFNULL(a.place.country.title, '')                           `hed__MailingCountry__c`,
                    CONCAT_WS(',', a.addressLines)                              `hed__MailingStreet__c`,
                    IFNULL(a.place.country.locality, '')                        `hed__MailingCity__c`,
                    IFNULL(a.place.country.region.title, '')                    `hed__MailingState__c`,
                    IFNULL(a.place.country.postalCode, '')                      `hed__MailingPostalCode__c`,

CASE
                      WHEN IFNULL(UPPER(TRIM(ac.type.addressType)), '') = 'HOME' THEN 'Home'
                      WHEN IFNULL(UPPER(TRIM(ac.type.addressType)), '') = 'BUSINESS' THEN 'Work'
                    ELSE 'Other'
                    END                                                         `hed__Address_Type__c`,
                    CASE
                      WHEN IFNULL(UPPER(TRIM(ac.preference)), '') = 'PRIMARY' THEN 'true'
                    ELSE 'false'
                    END                                                         `hed__Default_Address__c`
                  FROM ethosAddresses a
                  INNER JOIN addressInfoFromEthosPersonModalForNewlyCreatedContact ac ON TRIM(a.id) = TRIM(ac.address.id)

#- statement: print addressToCreateInSf

# Count and print the records that need to be created in SF
- statement: (addressToCreateInSfCount) => SELECT COUNT(*) FROM addressToCreateInSf
#- statement: print addressToCreateInSfCount

###### End - Create payload to insert address in SF ######

###### Start - Create hed__Parent_Contact__c batch to get existing address ######

# Retrieve the DISTINCT records that needs to be created in SF
- statement: |
    (parentContactId) =>
        SELECT DISTINCT
          hed__Parent_Contact__c          `id`
        FROM addressToCreateInSf
#- statement: PRINT parentContactId

# This statement executes a batch process task where the name, inputTable, outputTable and the results are the parameters needed 
- statement: |
      execute task 
          --name batchTask
          --inputTable parentContactId
          --outputBatchTable parentContactIdList
          --result resultStatus

# From the previous statement the outputBatchTable is used to refresh the Salesforce connector through the ParametrizedBy
- statement: REFRESH salesforceAddressReader

# Count and print the records from the Salesforce Address Reader
- statement: (salesforceAddressReaderCount) => SELECT COUNT(*) FROM salesforceAddressReader
#- statement: PRINT salesforceAddressReaderCount

###### End - Create hed__Parent_Contact__c batch to get existing address ######

###### Start - Get address records to update ######

# Join the table that contains the records that need to be updated with the table that contains the Salesforce Address Reader
- statement: (addressToUpdate) => SELECT
                                    s.Id,
                                    a.*
                                  FROM addressToCreateInSf a
                                  INNER JOIN salesforceAddressReader s ON TRIM(s.hed__Parent_Contact__c) = TRIM(a.hed__Parent_Contact__c)
                                    AND (
                                      IFNULL(TRIM(UPPER(s.hed__MailingStreet__c)),'') = IFNULL(TRIM(UPPER(a.hed__MailingStreet__c)),'')
                                      OR IFNULL(s.hed__MailingStreet__c, '') = '' OR IFNULL(a.hed__MailingStreet__c, '') = ''
                                    )
                                    AND (
                                      IFNULL(TRIM(UPPER(s.hed__MailingCity__c)),'') = IFNULL(TRIM(UPPER(a.hed__MailingCity__c)),'')
                                      OR IFNULL(s.hed__MailingCity__c, '') = '' OR IFNULL(a.hed__MailingCity__c, '') = ''
                                    )
                                    AND (
                                      IFNULL(TRIM(UPPER(s.hed__MailingState__c)),'') = IFNULL(TRIM(UPPER(a.hed__MailingState__c)),'')
                                      OR IFNULL(s.hed__MailingState__c, '') = '' OR IFNULL(a.hed__MailingState__c, '') = ''
                                    )

#- statement: print addressToUpdate

# Count and print the records that needs to be updated
- statement: (addressToUpdateCount) => SELECT COUNT(*) FROM addressToUpdate
#- statement: print addressToUpdateCount

###### End - Get address records to update ######

###### Start - Get address records to insert ######

# The 'LEFT ANTI JOIN' function retrieves the rows from the first table (table after the FROM statement) that do not match with the rows on the second table (right table)
- statement: (addressToInsert) => SELECT 
                                    a.*
                                  FROM addressToCreateInSf a
                                  LEFT ANTI JOIN salesforceAddressReader s ON TRIM(s.hed__Parent_Contact__c) = TRIM(a.hed__Parent_Contact__c)
                                    AND (
                                      IFNULL(TRIM(UPPER(s.hed__MailingStreet__c)),'') = IFNULL(TRIM(UPPER(a.hed__MailingStreet__c)),'')
                                      OR IFNULL(s.hed__MailingStreet__c, '') = '' OR IFNULL(a.hed__MailingStreet__c, '') = ''
                                    )
                                    AND (
                                      IFNULL(TRIM(UPPER(s.hed__MailingCity__c)),'') = IFNULL(TRIM(UPPER(a.hed__MailingCity__c)),'')
                                      OR IFNULL(s.hed__MailingCity__c, '') = '' OR IFNULL(a.hed__MailingCity__c, '') = ''
                                    )
                                    AND (
                                      IFNULL(TRIM(UPPER(s.hed__MailingState__c)),'') = IFNULL(TRIM(UPPER(a.hed__MailingState__c)),'')
                                      OR IFNULL(s.hed__MailingState__c, '') = '' OR IFNULL(a.hed__MailingState__c, '') = ''
                                    )

#- statement: print addressToInsert

# Count and print the records that needs to be inserted
- statement: (addressToInsertCount) => SELECT COUNT(*) FROM addressToInsert
#- statement: print addressToInsertCount

###### End - Get address records to insert ######

###### Start - Address Update ######

- statement: (updateResults, salesforceError) => UPDATE addressToUpdate INTO salesforceAddressWriter
#- statement: print updateResults   
#- statement: print salesforceError

# Refresh the salesforceErrorSchema in order to update the error message list and pull from there the info that will be synced into Salesforce
- statement: REFRESH salesforceErrorSchema
#- statement: print salesforceErrorSchema

# The error message concatenates a hardcoded string with the salesforce error value
- statement: |
      (failedUpdateAddressInfo) => 
        SELECT 
          a.address.id                                                          `id`,
          CONCAT("Address update failed due to:", " ", err.sf__Error)           `error_message` 
        FROM salesforceErrorSchema err
        INNER JOIN addressInfoFromEthosPersonModalForNewlyCreatedContact a ON TRIM(err.hed__Parent_Contact__c) = TRIM(a.sfContactId)

#- statement: print failedUpdateAddressInfo

# Count and print the records that failed the update process
- statement: (failedUpdateAddressInfoCount) => SELECT COUNT(*) FROM failedUpdateAddressInfo
#- statement: print failedUpdateAddressInfoCount

###### End - Address Update ######

###### Start - Address Insert ######

- statement: (insertResults, salesforceError) => INSERT addressToInsert INTO salesforceAddressWriter
#- statement: print insertResults   
#- statement: print salesforceError

- statement: REFRESH salesforceErrorSchema
#- statement: print salesforceErrorSchema

- statement: |
      (failedInsertAddressInfo) => 
        SELECT 
          a.address.id                                                          `id`,
          CONCAT("Address insert failed due to:", " ", err.sf__Error)           `error_message` 
        FROM salesforceErrorSchema err
        INNER JOIN addressInfoFromEthosPersonModalForNewlyCreatedContact a ON TRIM(err.hed__Parent_Contact__c) = TRIM(a.sfContactId)

#- statement: print failedInsertAddressInfo

# Count and print the records that failed the insert process
- statement: (failedInsertAddressInfoCount) => SELECT COUNT(*) FROM failedInsertAddressInfo
#- statement: print failedInsertAddressInfoCount

###### End - Address Insert ######

###### Start - Combined all rejected and failed information ######

- statement: (allFailedRecordInfo) =>
                  SELECT *, "Address" AS `error_object` FROM failedUpdateAddressInfo
                  UNION
                  SELECT *, "Address" AS `error_object` FROM failedInsertAddressInfo

#- statement: print allFailedRecordInfo
- statement: (allFailedRecordInfoCount) => SELECT COUNT(*) FROM allFailedRecordInfo
#- statement: print allFailedRecordInfoCount

# Get the fields that will compose the final failed record info
- statement: |
        (finalErrorToInsert) => 
          SELECT   
            id                              `Record_Tracking_Id__c`,                
            error_object                           `hed__Object_Type__c`,
            error_message                          `hed__Full_Message__c`,
            date_format(current_timestamp(), "yyyy-MM-dd'T'hh:mm:ss'Z'")  
                                                   `hed__Datetime__c`
          FROM allFailedRecordInfo

#- statement: print finalErrorToInsert
- statement: (finalErrorToInsertCount) => SELECT COUNT(*) FROM finalErrorToInsert
#- statement: print finalErrorToInsertCount

###### End - Combined all rejected and failed information ######

###### Start - Error Insert ######

- statement: (insertResults, salesforceError) => INSERT finalErrorToInsert INTO salesforceErrorWriter
#- statement: print insertResults
#- statement: print salesforceError

###### End - Error Insert ######

# Add more statements to convert, join, aggregrate, transform, and integrate your data