Peoplesoft to Salesforce: Terms

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

# Project Name - Peoplesoft to Salesforce: Terms

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

# Description - 2 - Peoplesoft to Salesforce: Terms
              # This recipe retrieves terms from Peoplesoft and writes back to Salesforce
              # Select all Terms from TERM_TBL in PeopleSoft where INSTITUTION = '<InstCod>' AND ACAD_CAREER = 'UGRD'
              # Select account from salesforce where SF.Name = 'Inst-Name'
              # If record match by PeopleSoft_Term_ID__c then update else insert.
              # Insert term requested fields with their SF id in the Lingk environment file.
              # Insert all processed terms into the Lingk env file.

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

# Data Models
## Peoplesoft Models Used:
#1. Terms

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

# Add Recipe notes / Change log information here!

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

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

connectors:

# Configure HTTP & Salesforce credentials in your Environment before running this recipe
  # HTTP Setup - https://help.lingk.io/en/articles/304-http-connector-setup-guide
  # Salesforce Setup - https://help.lingk.io/en/articles/267-salesforce-connector-setup-guide

###### Start - HTTP Reader connectors ######

# PeopleSoft Terms Connector
  ## HTTP V2 connector in comparison with V1, handles pagination through the connector properties
  ## Authentication and URL values are provided via environment variables  
    # documentation - https://help.lingk.io/en/articles/202-custom-environment-variables
- 
    name: peopleSoftTermData
    type: httpV2Reader 
    properties: 
      httpMethod: POST
      headerJson: {
        "Authorization":"Basic {{env.vars.apiAccessToken}}",
        "Content-Type":"application/json"
      }   
      acceptInvalidCerts: false     
      url: '{{env.vars.midStateInstanceURL}}'
      paginationType: RequestBody
      body: {
        "isDebugMode": false,
        "rowLimit": 200,
        "pageNumber": 1,
        "noEffectiveDateLogic": false,
        "noEffectiveStatusLogic": false,
        "includeFieldTypes": false,
        "records": [
          {
            "recordName": "TERM_TBL",
            "sqlWhereClause": "INSTITUTION = '<InstCod>' AND ACAD_CAREER = 'UGRD'"
          }
        ]
      }    
    schema:
      fields:
        - name: 'ACAD_CAREER'
          type: 'string'
        - name: 'ACAD_YEAR'
          type: 'string'
        - name: 'DESCR'
          type: 'string'
        - name: 'DESCRSHORT'
          type: 'string'
        - name: 'INSTITUTION'
          type: 'string'
        - name: 'STRM'
          type: 'string'
        - name: 'TERM_BEGIN_DT'
          type: 'string'
        - name: 'TERM_CATEGORY'
          type: 'string'
        - name: 'TERM_END_DT'
          type: 'string'
        - name: 'TRANSCIPT_DT_PRT'
          type: 'string'

###### End - HTTP Reader connectors ######

###### Start - Salesforce Reader connectors ######
  ## Salesforce Account Reader for 'Mid State College'
-
    name: sfAccountReader
    type: salesforceReader
    delayedRead: true
    properties:
      useBearerToken: true
      query: 
        SELECT 
          Id,
          Name
        FROM Account        
    schema:
      fields: 
        - name: 'Id'
          type: 'string'
        - name: 'Name'
          type: 'string'

## Salesforce Term Reader
-
    name: sfTermReader
    type: salesforceReader
    delayedRead: true
    properties:
      useBearerToken: true
      query: 
        SELECT 
          Id,
          Name,
          PeopleSoft_Term_ID__c,
          hed__Start_Date__c,
          hed__End_Date__c,
          hed__Type__c,
          hed__Account__c,
          Case_Safe_ID_Term__c
        FROM hed__Term__c
    schema:
      fields:
        - name: 'Id'
          type: 'string'
        - name: 'Name'
          type: 'string'
        - name: 'PeopleSoft_Term_ID__c'
          type: 'string'
        - name: 'hed__Start_Date__c'
          type: 'string'
        - name: 'hed__End_Date__c'
          type: 'string'
        - name: 'hed__Type__c'
          type: 'string'
        - name: 'hed__Account__c'
          type: 'string'
        - name: 'Case_Safe_ID_Term__c'
          type: 'string'

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

###### Start - Salesforce Writer connectors ######
  ## Salesforce Term Writer
-
    name: sfTermWriter
    type: salesforceBulkWriter
    onError: continue
    isSerial: true
    properties:
      onError: continue
      useBearerToken: true
      sfObject: hed__Term__c
      batchSize: 100

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

###### Start - Local Writer connectors ######
  # Local File Writer to writer processed terms
-   
    name: finalMappedRecordFile
    type: localFileWriter
    format: csvWriter
    properties:
      fileName: "finalMappedTermsRecords.csv"

###### End - Local Writer connectors ######

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

readFormats:
-
    name: text
    type: text

writeFormats:
-   
    name: csvWriter
    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 ***********************************************************************************************

# Print and count the peopleSoft term data
  - statement: REFRESH peopleSoftTermData
  #- statement: print peopleSoftTermData
  - statement: (peopleSoftTermDataCount) => SELECT COUNT(*) FROM peopleSoftTermData
  #- statement: print peopleSoftTermDataCount

- statement: () => Select * From sfAccountReader WHERE Name = 'Mid-State Technical College' LIMIT 1

# Select all information from peopleSoft term table
  - statement: |
      (terms) =>
          SELECT
            IFNULL(t.DESCR, '')                            `Name`,
            IFNULL(t.STRM, '')                             `PeopleSoft_Term_ID__c`,
            to_date(IFNULL(t.TERM_BEGIN_DT, ''))           `hed__Start_Date__c`,
            to_date(IFNULL(t.TERM_END_DT, ''))             `hed__End_Date__c`,
            "Semester"                                     `hed__Type__c`,
            a.Id                                           `hed__Account__c`                                      
          FROM peopleSoftTermData t
          INNER JOIN sfAccountReader a ON 1 = 1

#- statement: print terms
  - statement: (termsCount) => SELECT COUNT(*) FROM terms
  #- statement: print termsCount

- statement: REFRESH sfTermReader
  #- statement: print sfTermReader
  - statement: (sfTermReaderCount) => SELECT COUNT(*) FROM sfTermReader
  #- statement: print sfTermReaderCount

# Match SF terms by PeopleSoft_Term_ID__c, if match found then update else insert the term into Salesforce.
  - statement: |
      (termsToUpdate) =>
          SELECT
            sf.Id,
            t.Name,
            t.hed__Start_Date__c,
            t.hed__End_Date__c,
            t.hed__Type__c,
            t.hed__Account__c
          FROM terms t
          INNER JOIN sfTermReader sf ON IFNULL(TRIM(t.PeopleSoft_Term_ID__c), '') = IFNULL(TRIM(sf.PeopleSoft_Term_ID__c), '')

#- statement: print termsToUpdate
  - statement: (termsToUpdateCount) => SELECT COUNT(*) FROM termsToUpdate
  #- statement: print termsToUpdateCount

# Update Salesforce terms
  - statement: (success, error) => UPDATE termsToUpdate INTO sfTermWriter
  #- statement: print success
  #- statement: print error

# Retrieves the records that need to be inserted
  # 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: |
      (termsToInsert) =>
          SELECT
            t.*
          FROM terms t
          LEFT ANTI JOIN sfTermReader sf ON IFNULL(TRIM(t.PeopleSoft_Term_ID__c), '') = IFNULL(TRIM(sf.PeopleSoft_Term_ID__c), '')

# Print and count records to be inserted
  #- statement: print termsToInsert
  - statement: (termsToInsertCount) => SELECT COUNT(*) FROM termsToInsert
  #- statement: print termsToInsertCount

# Insert terms into Salesforce terms
  - statement: (success, error) => INSERT termsToInsert INTO sfTermWriter
  #- statement: print success
  #- statement: print error

#################################### LOGS - Debugging purpuses ######################################

#- statement: print peopleSoftTermDataCount
  #- statement: print termsCount
  #- statement: print sfTermReaderCount
  #- statement: print termsToUpdateCount
  #- statement: print termsToInsertCount

## Insert request with SF id in local file
  - statement: REFRESH sfTermReader

# Retrieves the data from peoplesoft that matches with the current records in Salesforce 
  - statement: |
      (existingTermInfo) =>
          SELECT
            sf.Id,
            t.*
          FROM terms t
          INNER JOIN sfTermReader sf ON IFNULL(TRIM(t.PeopleSoft_Term_ID__c), '') = IFNULL(TRIM(sf.PeopleSoft_Term_ID__c), '')

- statement: INSERT existingTermInfo INTO finalMappedRecordFile

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