Ellucian Ethos to Saleforce EDA ( Student Academic Programs )

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

# Project Name -  ELLUCIAN ETHOS TO SALEFORCE EDA ( STUDENT ACADEMIC PROGRAMS )

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

# Description - This recipe retrieves Ellucian Ethos Academic Programs and writes back to Salesforce custom object (hed__Program_Enrollment__c)

# Industry - Higher Ed
# Business Process - Graduate Reporting

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

# Data Models
## Ethos Models Used:
#1. student-academic-programs
#2. student-grade-point-averages

## Salesforce Objects
#1. Account
#1. Contact
#1. hed__Program_Enrollment__c

# Data Sets
# Academic Programs & hed__Program_Enrollment__c

# 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 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: ethosStudentAcademicProgramReader
    type: ethosReader
    delayedRead: true
    properties:
      path: /api/student-academic-programs
      dataModelVersion: 17

-
    name: ethosStudentGPAReader
    type: ethosReader
    parameterizedBy: studentAcademicProgramInformation
    delayedRead: true
    properties:
      path: /api/student-grade-point-averages?criteria=%7B%22student%22%3A%20%7B%22id%22%3A%20%22{{var.person_id}}%22%7D%7D
      dataModelVersion: 1

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

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

- 
    name: sfAccountReader
    type: salesforceReader
    delayedRead: true
    properties: 
      useBearerToken: true
      query: SELECT Id, External_GUID__c FROM Account WHERE RecordType.Name = 'Academic Program' AND External_GUID__c != NULL
    schema:
      fields:
        - name: 'Id'
          type: 'string'  
        - name: 'External_GUID__c'
          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: sfhedProgramEnrollmentReader
    type: salesforceReader
    delayedRead: true
    properties: 
      useBearerToken: true
      query: SELECT Id, hed__Contact__c, hed__Account__c FROM hed__Program_Enrollment__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: sfProgramEnrollmentWriter
    type: salesforceBulkWriter
    properties:
      onError: continue
      sfObject: hed__Program_Enrollment__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 ethosStudentAcademicProgramReader
  - statement: (ethosStudentAcademicProgramReaderCount) => SELECT COUNT(*) FROM ethosStudentAcademicProgramReader
  #- statement: print ethosStudentAcademicProgramReaderCount

# date functions:
   # First - converts the person dates (date of birth and date deceased) 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
   # YEAR - will get exclusively the year of the date provided

- statement: |
      (ethosStudentAcademicProgramInfo) =>
          SELECT
            id                                          `ethos_id`,
            student.id                                  `person_id`,
            program.id                                  `program_id`,
            date_format(to_date(startOn), 'yyyy-MM-dd')
                                                        `start_on`,
            date_format(to_date(endOn), 'yyyy-MM-dd')
                                                        `end_on`,
            YEAR(to_date(expectedGraduationDate))       `expected_graduation_year`

FROM ethosStudentAcademicProgramReader

#- statement: print ethosStudentAcademicProgramInfo

- 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: |
      (studentAcademicProgramInformation) =>
          SELECT
            p.ethos_id,
            p.start_on                                        `hed__Start_Date__c`,
            p.end_on                                          `hed__End_Date__c`,
            p.expected_graduation_year                        `hed__Graduation_Year__c`,
            p.person_id                                       `person_id`,
            p.program_id                                      `program_id`,

a.Id                                              `hed__Account__c`,
            c.Id                                              `hed__Contact__c`

FROM ethosStudentAcademicProgramInfo p
          INNER JOIN sfAccountReader a ON p.program_id = a.External_GUID__c
          INNER JOIN sfContactReader c ON p.person_id = c.External_GUID__c

#- statement: print studentAcademicProgramInformation
  - statement: (studentAcademicProgramInformationCount) => SELECT COUNT(*) FROM studentAcademicProgramInformation
  #- statement: print studentAcademicProgramInformationCount

- statement: REFRESH ethosStudentGPAReader
  - statement: (ethosStudentGPAReaderCount) => SELECT COUNT(*) FROM ethosStudentGPAReader
  #- statement: print ethosStudentGPAReaderCount

- statement: |
      (ethosStudentGPAInformation) =>
          SELECT
            student.id                    `person_id`,
            a.earnedDegrees.*
          FROM ethosStudentGPAReader
          lateral view explode(earnedDegrees) a as earnedDegrees

#- statement: print ethosStudentGPAInformation

- statement: |
      (finalStudentGPAInformation) =>
          SELECT
            gpa.*,
            ROW_NUMBER() OVER (PARTITION BY gpa.person_id, gpa.academicProgram.id ORDER BY gpa.person_id DESC) AS Row
          FROM ethosStudentGPAInformation gpa
          INNER JOIN studentAcademicProgramInformation p ON gpa.academicProgram.id = p.ethos_id
            AND gpa.academicSource = 'all'

#- statement: print finalStudentGPAInformation
  - statement: (finalStudentGPAInformationCount) => SELECT COUNT(*) FROM finalStudentGPAInformation
  #- statement: print finalStudentGPAInformationCount

- statement: |
      (programEnrollmentInfo) =>
          SELECT
            p.hed__Start_Date__c,
            p.hed__End_Date__c,
            p.hed__Graduation_Year__c,
            p.hed__Account__c,
            p.hed__Contact__c,

gpa.attemptedCredits                              `hed__Credits_Attempted__c`,
            gpa.earnedCredits                                 `hed__Credits_Earned__c`,
            gpa.value                                         `hed__GPA__c`

FROM studentAcademicProgramInformation p
          LEFT JOIN finalStudentGPAInformation gpa ON p.ethos_id = gpa.academicProgram.id AND gpa.ROW = 1

#- statement: print programEnrollmentInfo
  - statement: (programEnrollmentInfoCount) => SELECT COUNT(*) FROM programEnrollmentInfo
 #- statement: print programEnrollmentInfoCount

- statement: REFRESH sfhedProgramEnrollmentReader
  - statement: (sfhedProgramEnrollmentReaderCount) => SELECT COUNT(*) FROM sfhedProgramEnrollmentReader
  - statement: print sfhedProgramEnrollmentReaderCount

#### Start - Update hed__Program_Enrollment__c ####

- statement: |
      (programEnrollmentInfoToUpdate) =>
          SELECT
            sf.Id,
            p.hed__Start_Date__c,
            p.hed__End_Date__c,
            p.hed__Graduation_Year__c,
            p.hed__Credits_Attempted__c,
            p.hed__Credits_Earned__c,
            p.hed__GPA__c

FROM programEnrollmentInfo p
          INNER JOIN sfhedProgramEnrollmentReader sf ON p.hed__Contact__c = sf.hed__Contact__c 
            AND p.hed__Account__c = sf.hed__Account__c

#- statement: print programEnrollmentInfoToUpdate
  - statement: (programEnrollmentInfoToUpdateCount) => SELECT COUNT(*) FROM programEnrollmentInfoToUpdate
  #- statement: print programEnrollmentInfoToUpdateCount
  - statement: UPDATE programEnrollmentInfoToInsert INTO sfProgramEnrollmentWriter

#### End - Update hed__Program_Enrollment__c ####

#### Start - Insert hed__Program_Enrollment__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: |
      (programEnrollmentInfoToInsert) =>
          SELECT
            p.*

FROM programEnrollmentInfo p
          LEFT ANTI JOIN sfhedProgramEnrollmentReader sf ON p.hed__Contact__c = sf.hed__Contact__c 
            AND p.hed__Account__c = sf.hed__Account__c

#- statement: print programEnrollmentInfoToInsert
  - statement: (programEnrollmentInfoToInsertCount) => SELECT COUNT(*) FROM programEnrollmentInfoToInsert
  #- statement: print programEnrollmentInfoToInsertCount

- statement: INSERT programEnrollmentInfoToInsert INTO sfProgramEnrollmentWriter

#### End - Insert hed__Program_Enrollment__c ####

##LOG
  # 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: |
      (notFoundContact) =>
          SELECT
            p.*
          FROM ethosStudentAcademicProgramReader p
          LEFT ANTI JOIN sfContactReader c ON p.person_id = c.External_GUID__c

#- statement: print notFoundContact
  - statement: (dataCount) => SELECT COUNT(*) FROM notFoundContact
  #- statement: print dataCount

- statement: |
        (noFoundAccount) =>
          SELECT   
            p.*
          FROM ethosStudentAcademicProgramReader p
          LEFT ANTI JOIN sfAccountReader a ON p.program_id = a.External_GUID__c

#- statement: print noFoundAccount
  - statement: (dataCount) => SELECT COUNT(*) FROM noFoundAccount
  #- statement: print dataCount

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