Ellucian Ethos to Salesforce Writer

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

# Project Name -  ELLUCIAN ETHOS TO SALESFORCE WRITER

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

# Description - # This recipe pulls programs from Ellucian Ethos and upserts them into the Salesforce Education Data Architecture (EDA) Academic Program Account object

# Industry - Higher Ed
# Business Process - Graduate Reporting

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

# Data Models
## Ethos Models Used:
#1. academic-programs

## Salesforce Objects Used:
#1. Account
#2. RecordType

# 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: academicProgramsReader
    type: ethosReader
    properties:
      path: /api/academic-programs
      dataModelVersion: 6

-
    name: sfAccountReader
    type: salesforceReader
    properties:
      useBearerToken: true
      query: select ID, Name, Ethos_Id__c from Account where RecordTypeId in (Select Id From RecordType where Name = 'Academic Program')
    schema:
      fields:
        - name: ID
          type: String
        - name: Name
          type: String
        - name: Ethos_Id__c
          type: String

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

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

-
    name: sfRecordTypeId
    type: salesforceReader
    properties:
      useBearerToken: true
      query: Select Id From RecordType where Name = 'Academic Program'

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

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

-
    name: sfAccountWriter
    type: salesforceBulkWriter
    properties:
      onError: continue
      useBearerToken: true
      sfObject: Account
      # batchSize: 50 # default 10000
      # upsert: true # default false - requires external Id in sfObject

###### 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: (getPrograms) => select * from academicProgramsReader
  #- statement: print getPrograms
  - statement: (getProgramsCount) => select count(*) from academicProgramsReader
  #- statement: print getProgramsCount

- statement: (getAccounts) => select * from sfAccountReader
  #- statement: print getAccounts
  - statement: (sfAccountReaderCount) => select count(*) from sfAccountReader
  #- statement: print sfAccountReaderCount

#### Start - Update Account ####

# 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: |
       (programsToUpdate) =>

select
         sfAccountReader.Id              Id,
         (select Id from sfRecordTypeId)  RecordTypeId,
         academicProgramsReader.title    Name,
         academicProgramsReader.id       Ethos_Id__c,
         academicProgramsReader.code     Program_Code__c,
         academicProgramsReader.startOn  Start_On__c,
         case 
          when academicProgramsReader.status = "active" then "Yes"
          else "No"
         end                             Active__c
       from  academicProgramsReader
         inner join sfAccountReader on academicProgramsReader.id = sfAccountReader.Ethos_Id__c

#- statement: print programsToUpdate

- statement: update programsToUpdate into sfAccountWriter

#### End - Update Account ####

#### Start - Insert Account ####

# 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.
  # 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: |
       (programsToInsert) => 
       select
         (select Id from sfRecordTypeId)  RecordTypeId,
         academicProgramsReader.title     Name,
         academicProgramsReader.id        Ethos_Id__c,
         academicProgramsReader.code      Program_Code__c,
         academicProgramsReader.startOn   Start_On__c,
         case 
          when academicProgramsReader.status = "active" then "Yes"
          else "No"
         end                              Active__c
       from  academicProgramsReader
         left anti join sfAccountReader on academicProgramsReader.id = sfAccountReader.Ethos_Id__c

#- statement: print programsToInsert

- statement: (results, errors) => insert programsToInsert into sfAccountWriter

#### End - Insert Account ####

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