Ellucian Ethos to SFTP

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

# Project Name - ELLUCIAN ETHOS TO SFTP

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

# Description - This recipe joins Ellucian Ethos data into flattened table and writes to SFTP as CSV

# Industry - Higher Ed
# Business Process - Graduate Reporting

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

# Data Models
## Ethos Models Used:
#1. academics-programs
#2. student-academic-programs

# Add Recipe notes / Change log information here!

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

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

connectors:

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

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

-
    name: academicProgramsReader
    type: ethosReader
    properties:
      path: /api/academic-programs
      dataModelVersion: 6

-
    name: studentAcademicProgramsReader
    type: ethosReader
    properties:
      path: /api/student-academic-programs # add criteria to reduce records
      dataModelVersion: 6
      maxRecords: 100 # remove to get the full recordset
-
    name: studentReader
    type: ethosReader
    properties:
      path: /api/persons # add criteria to reduce records
      dataModelVersion: 12.1.0
      maxRecords: 100 # remove to get the full recordset

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

###### Start - SFTP Writer connectors ######

-
    name: studentProgramWriter
    type: sftpWriter
    format: csv
    properties:
      path: /organizations/ellucian-services/export/output_{{ func:date_format(func:current_datetime(), 'yyyy-MM-dd_HH-mm-ss') }}.csv

###### End - SFTP Writer connectors ######

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

# Configures the format of the file written to SFTP

readFormats:
-
    name: csv
    type: delimited
    properties:
      quoteAllFields: true
      delimiter: ','
      header: true

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

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

# These are simple readers and counters used for debugging purposes. They are not needed for the actual data flow
  - statement: (getPrograms) => select * from academicProgramsReader
  #- statement: print getPrograms
  - statement: (getProgramsCount) => select count(*) from academicProgramsReader
  #- statement: print getProgramsCount

- statement: (getStudentPrograms) => select * from studentAcademicProgramsReader
  #- statement: print getStudentPrograms
  - statement: (getStudentProgramsCount) => select count(*) from studentAcademicProgramsReader
  #- statement: print getStudentProgramsCount

- statement: (getStudent) => select * from studentReader
  #- statement: print getStudent
  - statement: (getStudentCount) => select count(*) from studentReader
  #- statement: print getStudentCount

# Credentials are stored in a nested array. Explode the results to make the credentials a table  
  # explode function in the query below splits the phones array in different rows and creates a lateral view
  - statement: |
      (bannerCredentials) => select 
                            student.id student_id,
                            a.credentials.* 
                       from studentReader student
                       lateral view explode(student.credentials) a as credentials 
  #- statement: print bannerCredentials

# Emails are stored in a nested array. Explode the results to make the emails a table  
  # explode function in the query below splits the phones array in different rows and creates a lateral view
  - statement: |
      (emails) => select 
                            student.id student_id,
                            a.emails.* 
                       from studentReader student
                       lateral view explode(student.emails) a as emails 
  #- statement: print emails

###
# Query that sets up headers and values of the CSV
# Joins source and pre-processed data into a single flattened view
###
  - statement: |
      (joinedDataset) => 
        select 
               bannerCredentials.value                  banner_id,
               emails.address                           primary_email,
               student.id                               student_id,
               student.names[0].fullname                full_name,
               student.names[0].firstName               first_name,
               student.names[0].lastName                last_name,
               program.id                               program_id,
               program.code                             program_code,
               program.title                            program_title,
               studentProgram.enrollmentStatus.status   program_status
        from studentAcademicProgramsReader studentProgram 
          INNER JOIN academicProgramsReader program on program.id = studentProgram.program.id
          INNER JOIN studentReader student on student.id = studentProgram.student.id  
          INNER JOIN bannerCredentials on 
                      bannerCredentials.student_id = studentProgram.student.id and 
                      bannerCredentials.type = "bannerId" 
          LEFT JOIN emails on 
                      emails.student_id = studentProgram.student.id and 
                      emails.preference = "primary"

# Debug
  #- statement: print joinedDataset
  - statement: (joinedDatasetCount) => select count(*) from joinedDataset
  #- statement: print joinedDatasetCount

###### Start - Insert Contacts ######

# Write file to SFTP
  - statement: insert joinedDataset into studentProgramWriter

###### End - Insert Contacts ######

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