Ellucian Ethos to Salesforce EDA ( Academic History)

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

# Project Name - ELLUCIAN ETHOS TO SALESFORCE EDA ( ACADEMIC HISTORY)

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

# Description - This recipe retrieves Ethos Academic History and writes back to Salesforce custom object (hed__Education_History__c)

# Industry - Higher Ed
# Business Process - ACADEMIC HISTORY

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

# Data Models
## Ethos Models Used:
#1. external-education

## Salesforce Objects Used:
#1. Contact
#2. Account
#3. Ethos__c
#4. hed__Account__c
#5. hed__Education_history__c

# Data Sets
# Academic History &

# Add Recipe notes / Change log information here!

## New development - (Sprint ) 
# updated with style guide model
# updated with comment pattern

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

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

connectors:

# Configure Ethos & Salesforce credentials 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

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

-
    name: ethosExternalEducationReader
    type: ethosReader
    delayedRead: true
    properties:
      path: /api/external-education
      dataModelVersion: 7.0
      maxRecords: 10
    schema:
      fields:
        - name: 'credential'
          type: 'object'
          objectSchema:
            name: objectSchemaSchema
            fields:
              - name: 'id'
                type: 'string'
        - name: 'credentialsDate'
          type: 'string'
        - name: 'creditsEarned'
          type: 'string'
        - name: 'disciplines'
          type: 'array'
          items:
            name: 'arrayElement'
            type: 'object'
            objectSchema:
              name: objectSchemaSchema
              fields:
                - name: 'id'
                  type: 'string'
        - name: 'startOn'
          type: 'object'
          objectSchema:
            name: objectSchemaSchema
            fields:
              - name: 'day'
                type: 'string'
              - name: 'month'
                type: 'string'
              - name: 'year'
                type: 'string'
        - name: 'endOn'
          type: 'object'
          objectSchema:
            name: objectSchemaSchema
            fields:
              - name: 'day'
                type: 'string'
              - name: 'month'
                type: 'string'
              - name: 'year'
                type: 'string'
        - name: 'graduatedOn'
          type: 'string'
        - name: 'id'
          type: 'string'
        - name: 'institution'
          type: 'object'
          objectSchema:
            name: objectSchemaSchema
            fields:
              - name: 'id'
                type: 'string'
        - name: 'performanceMeasure'
          type: 'string'
        - name: 'person'
          type: 'object'
          objectSchema:
            name: objectSchemaSchema
            fields:
              - name: 'id'
                type: 'string'
        - name: 'transcriptReceivedOn'
          type: 'string'
        - name: 'classPercentile'
          type: 'string'
        - name: 'classRank'
          type: 'string'
        - name: 'classSize'
          type: 'string'

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

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

- 
    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: sfAccountReader
    type: salesforceReader
    delayedRead: true
    properties: 
      useBearerToken: true
      query: SELECT Id, External_GUID__c FROM Account WHERE RecordType.Name = 'Educational Institution' AND External_GUID__c != NULL
    schema:
      fields:
        - name: Id
          type: String
        - name: External_GUID__c
          type: String

- 
    name: sfEthosReader
    type: salesforceReader
    delayedRead: true
    properties: 
      useBearerToken: true
      query: SELECT Id, ID__c, Name, Code__c FROM Ethos__c WHERE RecordType.Name IN ('Academic Credentials')
    schema:
      fields:
        - name: Id
          type: String
        - name: ID__c
          type: String      
        - name: Name
          type: String
        - name: Code__c
          type: String

- 
    name: hedEducationHistoryReader
    type: salesforceReader
    delayedRead: true
    properties: 
      useBearerToken: true
      query: SELECT Id, hed__Contact__c, hed__Account__c FROM hed__Education_History__c 
    schema:
      fields:
        - name: Id
          type: String
        - name: hed__Contact__c
          type: String    
        - name: hed__Account__c
          type: String

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

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

-
    name: hedEducationHistoryWriter
    type: salesforceBulkWriter
    properties:
      onError: continue
      sfObject: hed__Education_History__c  
      batchSize: 200
      useBearerToken: true

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

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

# 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 ***********************************************************************************************

- statement: REFRESH ethosExternalEducationReader
  - statement: (externalEducationCount) => SELECT COUNT(*) FROM ethosExternalEducationReader
  #- statement: print externalEducationCount

# CONCAT - Returns the concatenation of different values
  # date functions:
   # First - converts the person dates (graduatedOn) from String into a Date formate using the "to_date" function
   # Then - through "date_format" function it provides the format needed to the date retrieved
  - statement: |
      (externalEducationInfo) =>
          SELECT
            id                                        `ethos_id`,
            classPercentile                           `hed__Class_Percentile__c`,
            classRank                                 `hed__Class_Rank__c`,  
            classSize                                 `hed__Class_Size__c`,

CONCAT(startOn.year, '-', startOn.year, '-', startOn.year)  
                                                      `hed__Start_Date__c`,
            CONCAT(endOn.year, '-', endOn.year, '-', endOn.year) 
                                                      `hed__End_Date__c`,
            date_format(to_date(graduatedOn), 'yyyy-MM-dd')                                          
                                                      `hed__Graduation_Date__c`,
            institution.id                            `institution_id`,
            person.id                                 `person_id`,
            credential.id                             `academic_cred_id`
          FROM ethosExternalEducationReader

#- statement: print externalEducationInfo

- statement: REFRESH sfContactReader
  - statement: (sfContactReaderCount) => SELECT COUNT(*) FROM sfContactReader
  #- statement: print sfContactReaderCount

- statement: REFRESH sfAccountReader
  - statement: (sfAccountReaderCount) => SELECT COUNT(*) FROM sfAccountReader
  #- statement: print sfAccountReaderCount

- statement: REFRESH sfEthosReader
  - statement: (sfEthosReaderCount) => SELECT COUNT(*) FROM sfEthosReader
  #- statement: print sfEthosReaderCount

# date functions:
   # First - converts the person dates (graduatedOn) from String into a Date formate using the "to_date" function
   # Then - through "date_format" function it provides the format needed to the date retrieved
  - statement: |
      (finalExternalEducationInfo) =>
        SELECT
          e.ethos_id,
          e.hed__Class_Percentile__c,
          e.hed__Class_Rank__c,
          e.hed__Class_Size__c,
          date_format(to_date(e.hed__Start_Date__c), 'yyyy-MM-dd') 
                                                 `hed__Start_Date__c`,
          date_format(to_date(e.hed__End_Date__c), 'yyyy-MM-dd') 
                                                 `hed__End_Date__c`,
          e.hed__Graduation_Date__c,

sfCon.Id                               `hed__Contact__c`,
          sfAcc.Id                               `hed__Account__c`,
          ethos.Code__c                          `hed__Degree_Earned__c`

FROM externalEducationInfo e
        INNER JOIN sfContactReader sfCon ON e.person_id = sfCon.External_GUID__c
        INNER JOIN sfAccountReader sfAcc ON e.institution_id = sfAcc.External_GUID__c
        LEFT JOIN sfEthosReader ethos ON e.academic_cred_id = ethos.ID__c

#- statement: print finalExternalEducationInfo

- statement: REFRESH hedEducationHistoryReader
  #- statement: print hedEducationHistoryReader

#### Start - Update hed__Education_History__c ####

- statement: |
      (educationHistoryInfoToUpdate) =>
          SELECT
            edu.Id,
            f.hed__Degree_Earned__c,
            f.hed__Class_Percentile__c,                                  
            f.hed__Class_Rank__c,
            f.hed__Class_Size__c,
            f.hed__Start_Date__c,
            f.hed__End_Date__c,
            f.hed__Graduation_Date__c

FROM finalExternalEducationInfo f
          INNER JOIN hedEducationHistoryReader edu ON f.hed__Contact__c = edu.hed__Contact__c
            AND f.hed__Account__c = edu.hed__Account__c

#- statement: print educationHistoryInfoToUpdate
  - statement: (educationHistoryInfoToUpdateCount) => SELECT COUNT(*) FROM educationHistoryInfoToUpdate
  #- statement: print educationHistoryInfoToUpdateCount
#  - statement: UPDATE educationHistoryInfoToUpdate INTO hedEducationHistoryWriter

#### End - Update hed__Education_History__c ####

#### Start - Insert hed__Education_History__c ####

# 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: |
      (educationHistoryInfoToInsert) =>
          SELECT
            f.hed__Contact__c,
            f.hed__Account__c,
            f.hed__Degree_Earned__c,
            f.hed__Class_Percentile__c,                                  
            f.hed__Class_Rank__c,
            f.hed__Class_Size__c,
            f.hed__Start_Date__c,
            f.hed__End_Date__c,
            f.hed__Graduation_Date__c

FROM finalExternalEducationInfo f
          LEFT ANTI JOIN hedEducationHistoryReader edu ON f.hed__Contact__c = edu.hed__Contact__c
            AND f.hed__Account__c = edu.hed__Account__c

#- statement: print educationHistoryInfoToInsert
  - statement: (educationHistoryInfoToInsertCount) => SELECT COUNT(*) FROM educationHistoryInfoToInsert
  #- statement: print educationHistoryInfoToInsertCount
#  - statement: INSERT educationHistoryInfoToInsert INTO hedEducationHistoryWriter

#### End - Insert hed__Education_History__c ####

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