Ellucian Ethos to Salesforce EDA ( Person / Contact )

## ETHOS MODELS:
#1. /persons
#2. /addresses

## RECIPE STEPS:
#1. Pulls persons from ethos and transformed data.
#2. Insert/update contact into SF matching with external guid (ethos id).
#3. Pulls address from ethos by address id which is present in persons info.
#4. Insert/update address into SF matching with external guid (ethos id).

connectors:

- 
    name: ethosPersonReader
    type: ethosReader
    delayedRead: true
    parameterizedBy: ethosParamList
    properties:
      path: '/api/persons/{{var.param}}'
      dataModelVersion: 12.1.0
      maxRecords: 4
    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: 'preference'
                  type: 'string'
                - name: 'startOn'
                  type: 'string'
                - name: 'endOn'
                  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: '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: 'citizenshipCountry'
          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: 'genderIdentity'
          type: 'object'
          objectSchema:
            name: objectSchemaSchema
            fields:
              - name: 'id'
                type: 'string'
        - name: 'id'
          type: 'string'
        - name: 'languages'
          type: 'array'
          items:
            name: 'arrayElement'
            type: 'object'
            objectSchema:
              name: objectSchemaSchema
              fields:
                - name: 'code'
                  type: 'string'
                - name: 'preference'
                  type: 'string'
        - name: 'maritalStatus'
          type: 'object'
          objectSchema:
            name: objectSchemaSchema
            fields:
              - name: 'maritalCategory'
                type: 'string'
              - name: 'detail'
                type: 'object'
                objectSchema:
                  name: objectSchemaSchema
                  fields:
                    - name: 'id'
                      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: 'middleName'
                  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: 'personalPronoun'
          type: 'object'
          objectSchema:
            name: objectSchemaSchema
            fields:
              - name: 'id'
                type: 'string'
        - name: 'phones'
          type: 'array'
          items:
            name: 'arrayElement'
            type: 'object'
            objectSchema:
              name: objectSchemaSchema
              fields:
                - name: 'preference'
                  type: 'string'
                - name: 'countryCallingCode'
                  type: 'string'
                - name: 'number'
                  type: 'string'
                - name: 'extension'
                  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: 'privacyCategory'
                type: 'string'
              - name: 'detail'
                type: 'object'
                objectSchema:
                  name: objectSchemaSchema
                  fields:
                    - name: 'id'
                      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: 'startOn'
                  type: 'string'
                - name: 'endOn'
                  type: 'string'    
        - name: 'credentials'
          type: 'array'
          items:
            name: 'arrayElement'
            type: 'object'
            objectSchema:
              name: objectSchemaSchema
              fields:
                - name: 'type'
                  type: 'string'
                - name: 'value'
                  type: 'string'
                - name: 'startOn'
                  type: 'string' 
                - name: 'endOn'
                  type: 'string'  
        - name: 'veteranStatus'
          type: 'object'
          objectSchema:
            name: objectSchemaSchema
            fields:
              - name: 'category'
                type: 'string'
        - name: 'socialMedia'
          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: 'socialMediaType'
                        type: 'string'

-
    name: ethosAddressReader
    type: ethosReader
    delayedRead: true
    parameterizedBy: filterAddressInfo
    properties:
      path: '/api/addresses/{{var.address_id}}'
      dataModelVersion: 11.1.0
    schema:
      fields:
        - name: 'id'
          type: 'string'
        - name: 'latitude'
          type: 'string'
        - name: 'longitude'
          type: 'string'  
        - name: 'addressLines'
          type: 'array'
          items:
            name: 'arrayElement'
            type: 'string'
        - name: 'place'
          type: 'object'
          objectSchema:
            name: objectSchemaSchema
            fields:
              - name: 'country'
                type: 'object'
                objectSchema:
                  name: objectSchemaSchema
                  fields:
                    - name: 'title'
                      type: 'string'
                    - name: 'locality'
                      type: 'string'
                    - name: 'postalCode'
                      type: 'string'
                    - name: 'region'
                      type: 'object'
                      objectSchema:
                        name: objectSchemaSchema
                        fields:
                          - name: 'title'
                            type: 'string'
                    - name: 'subRegion'
                      type: 'object'
                      objectSchema:
                        name: objectSchemaSchema
                        fields:
                          - name: 'title'
                            type: 'string'

- 
    name: sfContactReader
    type: salesforceReader
    delayedRead: true
    properties: 
      useBearerToken: true
      query: SELECT Id, External_GUID__c FROM Contact WHERE External_GUID__c != NULL
    schema:
      fields:
        - name: Id
          type: String
        - name: External_GUID__c
          type: String

- 
    name: hedAddressReader
    type: salesforceReader
    delayedRead: true
    properties: 
      useBearerToken: true
      query: SELECT Id, hed__Parent_Contact__c, External_GUID__c, hed__Address_Type__c FROM hed__Address__c WHERE External_GUID__c != NULL
    schema:
      fields:
        - name: Id
          type: String 
        - name: hed__Parent_Contact__c
          type: String 
        - name: External_GUID__c
          type: String 
        - name: hed__Address_Type__c
          type: String

- 
    name: sfEthosReader
    type: salesforceReader
    delayedRead: true
    properties: 
      useBearerToken: true
      query: SELECT ID__c, Name, Code__c FROM Ethos__c WHERE RecordType.Name IN ('Religions', 'Races', 'Ethnicities', 'Citizenship Statuses')
    schema:
      fields:
        - name: 'ID__c'
          type: 'String' 
        - name: 'Name'
          type: 'String' 
        - name: 'Code__c'
          type: 'String'

-
    name: sfContactWriter
    type: salesforceWriter
    properties:
      sfObject: Contact  
      batchSize: 200
      useBearerToken: true

-
    name: sfAddressWriter
    type: salesforceWriter
    properties:
      sfObject: hed__Address__c  
      batchSize: 200
      useBearerToken: true

formats:
  - name: text
    type: text

statements: # this statement reads data from Ethos Proxy APIs into an in-memory staging table

- statement: |
     (ethosParamList) => 
                        SELECT '0a7246df-9c1f-4666-bf5d-d68eccd8fd67'     param  
                        UNION                        
                        SELECT '04cb9eb3-4d66-4801-9b84-66a609ed2d25'     param

- statement: print ethosParamList

- statement: |
     (dataCount) => SELECT count(*) FROM ethosParamList                          
  - statement: print dataCount

### START BUNDLER: PERSONS
  - statement: REFRESH ethosPersonReader

- statement: |
     (dataCount) => SELECT count(*) FROM ethosPersonReader                          
  - statement: print dataCount

- statement: print ethosPersonReader

- statement: |
      (ethosPersonsInfo) => SELECT  
                                person.id                                   `ethos_id`,
                                person.privacyStatus.detail.id              `privacyStatus_id`,
                                person.names[0].firstName                   `FirstName`,
                                person.names[0].middleName                  `MiddleName`,
                                person.names[0].lastName                    `LastName`,
                                person.names[0].title                       `Title`,

date_format(to_date(person.dateOfBirth, 'yyyy-MM-dd'), "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") 
                                                                            `Birthdate`, 
                                date_format(to_date(person.dateDeceased, 'yyyy-MM-dd'), "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") 
                                                                            `Deceased_Date__c`,

initcap(ifnull(person.gender, ''))          `hed__Gender__c`,
                                person.religion.id                          `religion_id`,
                                person.ethnicity.ethnicGroup.id             `ethnicity_id`,
                                person.genderIdentity.id                    `genderIdentity_id`,
                                person.personalPronoun.id                   `personalPronoun_id`,

initcap(ifnull(person.maritalStatus.maritalCategory, ''))        `Marital_Status__c`,

CASE WHEN ifnull(person.veteranStatus.category, '') = "" THEN "false"
                                ELSE "true" END                             `hed__Military_Service__c`,

CASE WHEN person.citizenshipStatus.category = "nonCitizen" THEN "International"
                                WHEN person.citizenshipStatus.category = "citizen" THEN "Domestic"
                                ELSE "" END                                 `Citizenship_Type__c`,
                                person.citizenshipStatus.detail.id          `citizenshipStatus_id`,

person.countryOfBirth                       `hed__Country_of_Origin__c`,
                                person.citizenshipCountry                   `hed__Citizenship__c`,
                                person.races[0].race.id                     `race_id`

FROM ethosPersonReader person

##Start: Get phone 
  - statement: |
      (phoneInfo) => SELECT 
                                  person.id                                 person_id, 
                                  a.phones.* 
                            FROM ethosPersonReader person 
                            lateral view explode(person.phones) a as phones

- statement: |
      (personPhoneInfo) => SELECT 
                                  p.person_id                                 person_id,

CASE WHEN p.type.phoneType = "other" OR p.type.phoneType = "vacation" THEN "other"
                                  ELSE p.type.phoneType END                  `phone_type`,
                                  p.preference                                preference,
                                  concat((ifnull(p.countryCallingCode, "")), p.number, (ifnull(p.extension, "")))       phone_number  
                            FROM phoneInfo p

# Removed duplicate phone type
  - statement: |
      (phoneInfo) => SELECT *,
                        ROW_NUMBER() OVER (PARTITION BY person_id, phone_type ORDER BY preference DESC) AS Row
                      FROM personPhoneInfo

- statement: (finalPersonsPhoneInfo) => SELECT * FROM phoneInfo WHERE Row = 1

- statement: |
      (personsWithPhoneInfo) => SELECT  
                            person.ethos_id                             `External_GUID__c`,

CASE WHEN phone.phone_type = "home" THEN "Home"
                            WHEN phone.phone_type = "business" THEN "Work"
                            WHEN phone.phone_type = "mobile" THEN "Mobile"
                            WHEN phone.phone_type <> "" THEN "Other"
                            ELSE "" END                                 `hed__PreferredPhone__c`,
                            phone1.phone_number                         `HomePhone`,
                            phone2.phone_number                         `hed__WorkPhone__c`,
                            phone3.phone_number                         `MobilePhone`,
                            phone4.phone_number                         `OtherPhone`

FROM ethosPersonsInfo person

LEFT JOIN finalPersonsPhoneInfo phone ON person.ethos_id = phone.person_id AND phone.preference = "primary"
                          LEFT JOIN finalPersonsPhoneInfo phone1 ON person.ethos_id = phone1.person_id AND phone1.phone_type = "home"
                          LEFT JOIN finalPersonsPhoneInfo phone2 ON person.ethos_id = phone2.person_id AND phone2.phone_type = "business"
                          LEFT JOIN finalPersonsPhoneInfo phone3 ON person.ethos_id = phone3.person_id AND phone3.phone_type = "mobile"
                          LEFT JOIN finalPersonsPhoneInfo phone4 ON person.ethos_id = phone4.person_id AND phone4.phone_type = "other"

##END
##START: GET EMAIL
  - statement: |
      (emailInfo) => SELECT 
                                  person.id                                 person_id, 
                                  a.emails.* 
                            FROM ethosPersonReader person 
                            lateral view explode(person.emails) a as emails

- statement: |
      (preferenceEmail) => SELECT 
                                  e.person_id                                 person_id, 
                                  e.type.emailType                            email_type,
                                  concat(
                                    replace(lower(e.address), ' ', ''),
                                    if( 
                                        find_in_set("{{env.name}}","Dev,Test,FullSandbox") > 0,
                                        ".invalid",
                                        ""
                                     ) 
                                   )                                         email_address 
                            FROM emailInfo e 
                            WHERE e.address rlike ('[A-Z0-9a-z._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,64}') AND (e.address != '' AND e.address != ' ')
                            AND e.address IS NOT NULL AND e.preference = "primary"

- statement: |
      (personEmailInfo) => SELECT 
                                  e.person_id                                 person_id, 
                                  e.type.emailType                            email_type,
                                  e.preference                                preference,
                                  concat(
                                    replace(lower(e.address), ' ', ''),
                                    if( 
                                        find_in_set("{{env.name}}","Dev,Test,FullSandbox") > 0,
                                        ".invalid",
                                        ""
                                     ) 
                                   )                                         email_address 
                            FROM emailInfo e 
                            WHERE e.address rlike ('[A-Z0-9a-z._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,64}') AND (e.address != '' AND e.address != ' ')
                            AND e.address IS NOT NULL

# Removed duplicate Email type
  - statement: |
      (emailInfo) => SELECT *,
                        ROW_NUMBER() OVER (PARTITION BY person_id, email_type ORDER BY preference) AS Row
                      FROM personEmailInfo

- statement: (finalPersonsEmailInfo) => SELECT * FROM emailInfo WHERE Row = 1

- statement: |
      (personsWithEmailInfo) => SELECT  
                            person.ethos_id                             `External_GUID__c`,

CASE WHEN email.email_type = "school" THEN "University Email"
                            WHEN email.email_type = "business" THEN "Work Email"
                            WHEN email.email_type = "personal" THEN "Alternate Email"                            
                            ELSE "" END                                 `hed__Preferred_Email__c`,
                            email1.email_address                        `hed__UniversityEmail__c`,
                            email2.email_address                        `hed__WorkEmail__c`,
                            email3.email_address                        `hed__AlternateEmail__c`,
                            email.email_address                         `Email`

FROM ethosPersonsInfo person

LEFT JOIN preferenceEmail email ON person.ethos_id = email.person_id
                          LEFT JOIN finalPersonsEmailInfo email1 ON person.ethos_id = email1.person_id AND email1.email_type = "school"
                          LEFT JOIN finalPersonsEmailInfo email2 ON person.ethos_id = email2.person_id AND email2.email_type = "business"
                          LEFT JOIN finalPersonsEmailInfo email3 ON person.ethos_id = email3.person_id AND email3.email_type = "personal"

##End
##START: ROLES INFO
  - statement: |
      (rolesInfo) => SELECT 
                                  person.id                                 person_id, 
                                  a.roles.* 
                            FROM ethosPersonReader person 
                            lateral view explode(person.roles) a as roles

- statement: |
      (personRolesInfo) => SELECT 
                                  r.person_id                                 person_id,

CASE WHEN r.role = "employee" THEN "Employee"
                                  WHEN r.role = "student" THEN "Student"
                                  WHEN r.role = "applicant" THEN "Applicant"
                                  ELSE "" END                                 `role_type`, 
                                  CASE WHEN r.role = "employee" THEN "1"
                                  WHEN r.role = "student" THEN "2"
                                  WHEN r.role = "applicant" THEN "3"
                                  ELSE "4" END                                 `role_ranking`
                            FROM rolesInfo r

- statement: |
      (rolesInfo) => SELECT *,
                        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY role_ranking) AS Row
                      FROM personRolesInfo
                      WHERE role_ranking IS NOT NULL AND role_ranking <> ""

- statement: (finalPersonsRoleInfo) => SELECT * FROM rolesInfo WHERE Row = 1

##START: CREDENTIALS INFO
  - statement: |
      (credentialsInfo) => SELECT 
                                  person.id                                 person_id, 
                                  a.credentials.* 
                            FROM ethosPersonReader person 
                            lateral view explode(person.credentials) a as credentials

- statement: |
      (personCredentialsInfo) => SELECT 
                                  c.person_id                                 person_id, 
                                  c.value                                     credentials_type_value,
                                  c.type                                     credentials_type
                            FROM credentialsInfo c 
                            WHERE c.type IS NOT NULL AND c.type <> ""
                            AND (upper(c.type) = "COLLEAGUEPERSONID" OR upper(c.type) = "SSN")

- statement: |
      (credentialsInfo) => SELECT *,
                        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY credentials_type_value) AS Row
                      FROM personCredentialsInfo
                      WHERE credentials_type_value IS NOT NULL AND credentials_type_value <> ""

- statement: (finalColleaguePersonId) => SELECT * FROM credentialsInfo WHERE Row = 1 AND upper(credentials_type) = "COLLEAGUEPERSONID"

- statement: (finalSsnInfo) => SELECT * FROM credentialsInfo WHERE Row = 1 AND upper(credentials_type) = "SSN"
##END

##START: RACES, ETHNICITIES & RELIGION INFO
  - statement: REFRESH sfEthosReader
  - statement: |
      (personInfoFromEthosReader) => 
        SELECT
          p.ethos_id                     `person_id`,

CASE WHEN IFNULL(race.Name, '') = 'Asian' THEN 'Asian'
               WHEN IFNULL(race.Name, '') = 'White' THEN 'White'
               WHEN IFNULL(race.Name, '') = 'Black or African American' THEN 'Black or African American'
               WHEN IFNULL(race.Name, '') = 'American/Alaska Native' THEN 'American Indian or Alaska Native'
               WHEN IFNULL(race.Name, '') = '	Hawaiian/Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
          ELSE '' END                    `race_name`,

CASE WHEN IFNULL(ethni.Name, '') = 'Hispanic/Latino' THEN 'Hispanic or Latino'
               WHEN IFNULL(ethni.Name, '') = 'Non Hispanic/Latino' THEN 'Not Hispanic or Latino'
          ELSE '' END                    `ethnicity_name`,

CASE WHEN IFNULL(rel.Name, '') = 'Hindu' THEN 'Hindu'
               WHEN IFNULL(rel.Name, '') = 'Christian' THEN 'Christian'
               WHEN IFNULL(rel.Name, '') = 'Christian' THEN 'Christian'
               WHEN IFNULL(rel.Name, '') = 'Jewish' THEN 'Jewish'
               WHEN IFNULL(rel.Name, '') = 'Muslim' THEN 'Muslim'
               WHEN IFNULL(rel.Name, '') <> '' THEN 'Other'
            ELSE '' END                  `religion_name`,

CASE WHEN IFNULL(citz.Name, '') = 'U.S. Citizen' THEN 'U.S. Citizen or U.S. National'
               WHEN IFNULL(citz.Name, '') = 'Alien' THEN 'Other (Non-U.S.)'
               WHEN IFNULL(citz.Name, '') = 'Non-Resident Alien' THEN 'Other (Non-U.S.)'
               WHEN IFNULL(citz.Name, '') = 'Resident Alien' THEN 'U.S. Permanent Resident'
               WHEN IFNULL(citz.Name, '') = 'Unknown' THEN 'Other (Non-U.S.)'
          ELSE '' END                      `citizenShip_status`

FROM ethosPersonsInfo p
        LEFT JOIN sfEthosReader race ON p.race_id = race.ID__c
        LEFT JOIN sfEthosReader ethni ON p.ethnicity_id = ethni.ID__c
        LEFT JOIN sfEthosReader rel ON p.religion_id = rel.ID__c
        LEFT JOIN sfEthosReader citz ON p.citizenshipStatus_id = citz.ID__c

- statement: print personInfoFromEthosReader
##END
  - statement: |
      (finalPersonsInfo) => SELECT 
                            ssn.credentials_type_value                  `hed__Social_Security_Number__c`,
                            "Home"                                      `hed__Primary_Address_Type__c`,
                            person.ethos_id                             `External_GUID__c`,

person.FirstName                            `FirstName`,
                            person.LastName                             `LastName`,
                            person.Title                                `Title`, 
                            person.Birthdate                            `Birthdate`, 
                            person.Deceased_Date__c                     `hed__Date_Deceased__c`, 
                            person.hed__Gender__c                       `hed__Gender__c`, 
                            person.hed__Military_Service__c             `hed__Military_Service__c`,

person.hed__Country_of_Origin__c            `hed__Country_of_Origin__c`,
                            person.hed__Citizenship__c                  `hed__Citizenship__c`,

CASE WHEN IFNULL(phone.hed__PreferredPhone__c, '') = '' 
                                THEN 
                                  CASE WHEN IFNULL(phone.HomePhone, '') != '' THEN 'Home'
                                       WHEN IFNULL(phone.hed__WorkPhone__c, '') != '' THEN 'Work'
                                       WHEN IFNULL(phone.MobilePhone, '') != '' THEN 'Mobile'
                                       WHEN IFNULL(phone.OtherPhone, '') != '' THEN 'Other'
                                ELSE '' END
                            ELSE phone.hed__PreferredPhone__c END
                                                                         `hed__PreferredPhone__c`,
                            phone.HomePhone                             `HomePhone`,
                            phone.hed__WorkPhone__c                     `hed__WorkPhone__c`,
                            phone.MobilePhone                           `MobilePhone`,
                            phone.OtherPhone                            `OtherPhone`,

CASE WHEN (email.hed__Preferred_Email__c IS NULL OR email.hed__Preferred_Email__c = "") AND 
                            (email.Email IS NOT NULL AND email.Email <> "") THEN "Alternate" 
                            ELSE email.hed__Preferred_Email__c
                            END                                         `hed__Preferred_Email__c`,
                            email.hed__UniversityEmail__c               `hed__UniversityEmail__c`,
                            email.hed__WorkEmail__c                     `hed__WorkEmail__c`,

CASE WHEN (email.hed__Preferred_Email__c IS NULL OR email.hed__Preferred_Email__c = "") AND 
                            (email.Email IS NOT NULL AND email.Email <> "") THEN email.Email 
                            ELSE email.hed__AlternateEmail__c
                            END                                         `hed__AlternateEmail__c`,
                            email.Email                                 `Email`,

er.ethnicity_name                           `hed__Ethnicity__c`,
                            er.race_name                                `hed__Race__c`,
                            er.religion_name                            `hed__Religion__c`,
                            er.citizenShip_status                       `hed__Citizenship_Status__c`

FROM ethosPersonsInfo person
                          LEFT JOIN finalSsnInfo ssn ON person.ethos_id = ssn.person_id 
                          LEFT JOIN finalColleaguePersonId cred ON person.ethos_id = cred.person_id

LEFT JOIN personsWithPhoneInfo phone ON person.ethos_id = phone.External_GUID__c 
                          LEFT JOIN personsWithEmailInfo email ON person.ethos_id = email.External_GUID__c                           
                          LEFT JOIN personInfoFromEthosReader er ON person.ethos_id = er.person_id

# count the data
  - statement: (dataCount) => select count(*) from finalPersonsInfo
  - statement: print dataCount

# Removed duplicate contact record by GUID
  - statement: |
      (contactInformationGroupingWithGuid) => SELECT *,
                                                ROW_NUMBER() OVER (PARTITION BY External_GUID__c ORDER BY LastName) AS RowGuid
                                              FROM finalPersonsInfo

- statement: (finalContactInfo) => SELECT * FROM contactInformationGroupingWithGuid WHERE RowGuid = 1

# count the data
  - statement: (dataCount) => select count(*) from finalContactInfo
  - statement: print dataCount

- statement: REFRESH sfContactReader

- statement: |
      (contactToInsert) => SELECT 
                              contact.hed__Social_Security_Number__c,
                              contact.hed__Primary_Address_Type__c,
                              contact.External_GUID__c,

contact.FirstName,
                              contact.LastName,
                              contact.Title,
                              contact.Birthdate,

contact.hed__Date_Deceased__c,
                              contact.hed__Gender__c,
                              contact.hed__Military_Service__c,
                              contact.hed__Country_of_Origin__c,
                              contact.hed__Citizenship__c,

contact.hed__PreferredPhone__c,
                              contact.HomePhone,
                              contact.hed__WorkPhone__c,
                              contact.MobilePhone,
                              contact.OtherPhone,

contact.hed__Preferred_Email__c,
                              contact.hed__UniversityEmail__c,
                              contact.hed__WorkEmail__c,
                              contact.hed__AlternateEmail__c,
                              contact.Email,

contact.hed__Ethnicity__c,
                              contact.hed__Race__c,
                              contact.hed__Religion__c,
                              contact.hed__Citizenship_Status__c

From finalContactInfo contact
                           LEFT ANTI JOIN sfContactReader ON contact.External_GUID__c = sfContactReader.External_GUID__c

- statement: print contactToInsert
# count the data
  - statement: (dataCount) => select count(*) FROM contactToInsert
  - statement: print dataCount

# insert the contact info into SF
#  - statement: INSERT contactToInsert INTO sfContactWriter

- statement: stop
  - statement: REFRESH sfContactReader

### END BUNDLER: PERSONS

##Start SF Person-Address Insert Operation
  - statement: |
      (personsAddressInfo) => SELECT 
                                  person.id                                 person_id, 
                                  a.addresses.* 
                            FROM ethosPersonReader person 
                            lateral view explode(person.addresses) a as addresses

# count the data
  - statement: (dataCount) => select count(*) FROM personsAddressInfo
  - statement: print dataCount

- statement: |
      (filterAddressInfo) => SELECT 
                                  a.person_id                          `person_id`,
                                  contact.Id                           `contact_id`,
                                  a.address.id                         `address_id`,

CASE WHEN a.type.addressType = "home" THEN "Home"
                                  WHEN a.type.addressType = "business" THEN "Work" 
                                  ELSE "Other" END                     `hed__Address_Type__c`,

CASE WHEN a.preference = "primary" THEN "true" 
                                  ELSE "false" END                     `hed__Default_Address__c`,
                                  a.endOn                              `hed__Latest_End_Date__c`,
                                  a.startOn                            `hed__Latest_Start_Date__c`  
                            FROM personsAddressInfo a 
                            INNER JOIN sfContactReader contact ON a.person_id = contact.External_GUID__c

- statement: |
     (dataCount) => SELECT count(*) FROM filterAddressInfo                          
  - statement: print dataCount

- statement: REFRESH ethosAddressReader

- statement: |
     (dataCount) => SELECT count(*) FROM ethosAddressReader                          
  - statement: print dataCount

- statement: |
      (matchingAddressInfo) => SELECT 
                                  f.address_id                         `External_GUID__c`,
                                  f.contact_id                         `hed__Parent_Contact__c`,
                                  f.hed__Address_Type__c               `hed__Address_Type__c`,
                                  f.hed__Default_Address__c            `hed__Default_Address__c`,
                                  f.hed__Latest_End_Date__c            `hed__Latest_End_Date__c`,
                                  f.hed__Latest_Start_Date__c          `hed__Latest_Start_Date__c`,
                                  a.addressLines[0]                    `hed__MailingStreet__c`,
                                  a.place.country.title                `hed__MailingCountry__c`,
                                  a.place.country.locality             `hed__MailingCity__c`,
                                  a.place.country.region.title         `hed__MailingState__c`,
                                  a.place.country.postalCode           `hed__MailingPostalCode__c`,                                  
                                  a.latitude                           `hed__Geolocation__Latitude__s`,
                                  a.longitude                          `hed__Geolocation__Longitude__s` 
                            FROM filterAddressInfo f
                            INNER JOIN ethosAddressReader a  ON f.address_id =  a.id

- statement: |
     (dataCount) => SELECT count(*) FROM matchingAddressInfo                          
  - statement: print dataCount

- statement: |
      (nonMatchingAddressInfo) => SELECT 
                                  f.address_id                         `External_GUID__c`,
                                  f.contact_id                         `hed__Parent_Contact__c`,
                                  f.hed__Address_Type__c               `hed__Address_Type__c`,
                                  f.hed__Default_Address__c            `hed__Default_Address__c`,
                                  f.hed__Latest_End_Date__c            `hed__Latest_End_Date__c`,
                                  f.hed__Latest_Start_Date__c          `hed__Latest_Start_Date__c`
                            FROM filterAddressInfo f 
                            LEFT ANTI JOIN ethosAddressReader a  ON f.address_id =  a.id

- statement: |
     (dataCount) => SELECT count(*) FROM nonMatchingAddressInfo                          
  - statement: print dataCount

# Removed duplicate address record by GUID and contact
  - statement: |
      (addressGroupingWithGuidandContact) => SELECT *,
                                                ROW_NUMBER() OVER (PARTITION BY External_GUID__c, hed__Parent_Contact__c ORDER BY hed__Address_Type__c) AS RowGuid
                                              FROM matchingAddressInfo

- statement: (personsAddressInformation) => SELECT * FROM addressGroupingWithGuidandContact WHERE RowGuid = 1

- statement: |
     (dataCount) => SELECT count(*) FROM personsAddressInformation                          
  - statement: print dataCount

# Removed duplicate address record by GUID
  - statement: |
      (addressInformationGroupingWithGuid) => SELECT *,
                                                ROW_NUMBER() OVER (PARTITION BY External_GUID__c ORDER BY hed__Parent_Contact__c) AS RowGuid
                                              FROM matchingAddressInfo

- statement: (finalPersonsAddress) => SELECT * FROM addressInformationGroupingWithGuid WHERE RowGuid = 1

- statement: |
     (dataCount) => SELECT count(*) FROM finalPersonsAddress                          
  - statement: print dataCount

- statement: REFRESH hedAddressReader

- statement: |
      (addressToUpdate) => SELECT 
                                  a.hed__Parent_Contact__c,
                                  a.External_GUID__c,
                                  a.hed__Address_Type__c,
                                  a.hed__Default_Address__c,
                                  a.hed__Latest_End_Date__c,
                                  a.hed__Latest_Start_Date__c,
                                  a.hed__MailingStreet__c,
                                  a.hed__MailingCountry__c,
                                  a.hed__MailingState__c,
                                  a.hed__MailingCity__c,
                                  a.hed__MailingPostalCode__c,
                                  a.hed__Geolocation__Latitude__s,
                                  a.hed__Geolocation__Longitude__s,
                                  sf.Id                                 `Id`

FROM personsAddressInformation a 
                            INNER JOIN hedAddressReader sf ON a.External_GUID__c = sf.External_GUID__c AND a.hed__Parent_Contact__c = sf.hed__Parent_Contact__c

# count the data
  - statement: (dataCount) => select count(*) FROM addressToUpdate
  - statement: print dataCount

#  - statement: UPDATE addressToUpdate INTO sfAddressWriter

- statement: |
      (addressToInsert) => SELECT 
                                  a.hed__Parent_Contact__c,
                                  a.External_GUID__c,
                                  a.hed__Address_Type__c,
                                  a.hed__Default_Address__c,
                                  a.hed__Latest_End_Date__c,
                                  a.hed__Latest_Start_Date__c,
                                  a.hed__MailingStreet__c,
                                  a.hed__MailingCountry__c,
                                  a.hed__MailingState__c,
                                  a.hed__MailingCity__c,
                                  a.hed__MailingPostalCode__c,
                                  a.hed__Geolocation__Latitude__s,
                                  a.hed__Geolocation__Longitude__s

FROM personsAddressInformation a 
                            LEFT ANTI JOIN hedAddressReader sf ON a.External_GUID__c = sf.External_GUID__c AND a.hed__Parent_Contact__c = sf.hed__Parent_Contact__c

# count the data
  - statement: (dataCount) => select count(*) FROM addressToInsert
  - statement: print dataCount

- statement: INSERT addressToInsert INTO sfAddressWriter

##End SF Person-Address Insert Operation