Peoplesoft to Salesforce: Degree Picklist

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

# Project Name - Peoplesoft to Salesforce: Degree Picklist

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

# Description - 3 - Peoplesoft to Salesforce: Degree Picklist
                # Peoplesoft to Salesforce Academic Plans
                # Insert the academic plans from Peoplesoft to the Account table in Salesforce.
                # Reads from a web service on Peoplesoft
                # This will retrieve academic parents record type data for parent ID's.
                # Inserts or updates the Account table in Salesforce for SF Academic Programs

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

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

## Salesforce Objects Used:
#1. Accounts

# 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 Academic Plan 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: peopleSoftAcadPlanData
  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": 500,
          "pageNumber": 1,
          "noEffectiveDateLogic": false,
          "noEffectiveStatusLogic": true,
          "includeFieldTypes": false,
          "records": [
            {
              "recordName": "ACAD_PLAN_TBL",
              "sqlWhereClause": " INSTITUTION = '{{env.inst-code}}'"
            }
          ]
        }
  schema:
    fields:
      - name: 'ACAD_CAREER'
        type: 'string'
      - name: 'ACAD_PLAN'
        type: 'string'
      - name: 'ACAD_PLAN_TYPE'
        type: 'string'
      - name: 'ACAD_PROG'
        type: 'string'
      - name: 'CIP_CODE'
        type: 'string'
      - name: 'DEGREE'
        type: 'string'
      - name: 'DESCR'
        type: 'string'
      - name: 'DESCRLONG'
        type: 'string'
      - name: 'DESCRSHORT'
        type: 'string'
      - name: 'DIPLOMA_DESCR'
        type: 'string'
      - name: 'DIPLOMA_INDENT'
        type: 'long'
      - name: 'DIPLOMA_PRINT_FL'
        type: 'string'
      - name: 'EFFDT'
        type: 'string'
      - name: 'EFF_STATUS'
        type: 'string'
      - name: 'EVALUATE_PLAN'
        type: 'string'
      - name: 'FA_ELIGIBILITY'
        type: 'string'
      - name: 'FIRST_TERM_VALID'
        type: 'string'
      - name: 'HEGIS_CODE'
        type: 'string'
      - name: 'INSTITUTION'
        type: 'string'
      - name: 'PLN_REQTRM_DFLT'
        type: 'string'
      - name: 'REQUIRED_GPA'
        type: 'long'
      - name: 'SAA_WHIF_DISP_ADVR'
        type: 'string'
      - name: 'SAA_WHIF_DISP_PREM'
        type: 'string'
      - name: 'SAA_WHIF_DISP_STD'
        type: 'string'
      - name: 'SCC_SFP_AY_I_UNTS'
        type: 'long'
      - name: 'SCC_SFP_AY_UNIT'
        type: 'string'
      - name: 'SCC_SFP_AY_UNTS'
        type: 'long'
      - name: 'SCC_SFP_FIRST_PROF'
        type: 'string'
      - name: 'SCC_SFP_OPEID'
        type: 'string'
      - name: 'SCC_SFP_PLAN_TYPE'
        type: 'string'
      - name: 'SCC_SFP_PROG_UNIT'
        type: 'string'
      - name: 'SCC_SFP_REQ_UNTS'
        type: 'long'
      - name: 'SFA_SPEC_PROG_FLG'
        type: 'string'
      - name: 'SSR_LAST_ADM_TERM'
        type: 'string'
      - name: 'SSR_LAST_PRS_DT'
        type: 'string'
      - name: 'SSR_NSC_CRD_LVL'
        type: 'string'
      - name: 'SSR_NSC_INCL_PLAN'
        type: 'string'
      - name: 'SSR_PROG_LENGTH'
        type: 'long'
      - name: 'SSR_PROG_LEN_TYPE'
        type: 'string'
      - name: 'SSR_SFP_BBAY_IND'
        type: 'string'
      - name: 'SSR_SFP_BBY_TRM_AY'
        type: 'long'
      - name: 'SSR_SFP_PROJ_CRSE'
        type: 'string'
      - name: 'STUDY_FIELD'
        type: 'string'
      - name: 'TRANSCRIPT_LEVEL'
        type: 'string'
      - name: 'TRNSCR_DESCR'
        type: 'string'
      - name: 'TRNSCR_INDENT'
        type: 'long'
      - name: 'TRNSCR_PRINT_FL'
        type: 'string'
      - name: 'rowNumber'
        type: 'long'
      - name: 'errorMessages'
        type: 'string'
      - name: 'meta'
        type: 'object'
        objectSchema:
          name: objectSchemaSchema
          fields:
            - name: 'currentUser'
              type: 'string'
            - name: 'dbType'
              type: 'string'
            - name: 'dbname'
              type: 'string'
            - name: 'debugMessages'
              type: 'string'
            - name: 'psftTransactionId'
              type: 'string'
            - name: 'responseDTTM'
              type: 'string'
            - name: 'toolsVer'
              type: 'string'
      - name: 'pageNumber'
        type: 'long'
      - name: 'responseCode'
        type: 'long'

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

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

# This will get us the existing Salesforce Academic Programs
-
  name: sfAcademicProgramReader
  type: salesforceReader
  delayedRead: false
  properties:
    useBearerToken: true
    query: 
      SELECT             
        PeopleSoft_Academic_Program_ID__c
        ,Id,Academic_Program_Inactive__c
      FROM Account                
    schema:
      fields:
      - name: 'Id'
        type: 'string'     
      - name: 'PeopleSoft_Academic_Program_ID__c'
        type: 'string'
      - nane: 'Academic_Program_Inactive__c' 
        type: 'boolean'

# this will get us the acedemic parents record type rows for parent ids.
-
  name: sfAcademicProgramParentIDReader
  type: salesforceReader
  delayedRead: false
  properties:
    useBearerToken: true
    query: 
      SELECT       
        Name
        ,PeopleSoft_Academic_Parent_Code__c
        ,Id
      FROM Account
    schema:
      fields: 
        - name: 'PeopleSoft_Academic_Parent_Code__c'
          type: 'string'
        - name: 'Name'
          type: 'string'
        - name: 'Id'
          type: 'string'

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

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

-
    name: sfAcadPlanWriter
    type: salesforceBulkWriter
    onError: continue
    isSerial: true
    properties:
      useBearerToken: true
      sfObject: Account
      batchSize: 100

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

###### Start - Local Writer connectors ######

# log file
-
    name: ProgramLogFile
    type: localFileWriter
    format: excelFormat
    properties:
      fileName: "PStoSF_AcadPlan.xlsx"

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

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

# # FORMATS specify how files should be processed as they are being read or written to

writeFormats:
-
    name: excelFormat
    type: excel
    properties:
      worksheets: "ProgramDataSent"
      useHeader: "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 Plan data from Peoplesoft
#- statement: print peopleSoftAcadPlanData
- statement: (peopleSoftAcadPlanDataCount) => SELECT COUNT(*) as Num FROM peopleSoftAcadPlanData
#- statement: print peopleSoftAcadPlanDataCount

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

#- statement: (err)=> select errorMessages from peopleSoftAcadPlanData
#- statement: print err
#- statement: (code)=> select responseCode from peopleSoftAcadPlanData
#- statement: print code

# Get the acedemic programs already loaded in PS
- statement: (sfAcademicProgramReader) => Select * from sfAcademicProgramReader WHERE RecordTypeId = '{{env.vars.academicPlanProgramRecordType}}'
#- statement: print sfAcademicProgramReader
# - statement: printschema sfAcademicProgramReader
# - statement: (progFromSFCount) => SELECT COUNT(*) as Num FROM sfAcademicProgramReader
# - statement: print progFromSFCount

# This will get us the acedmemic program parent IDs.
- statement: (sfAcademicProgramParentIDReader) => Select * from sfAcademicProgramParentIDReader WHERE RecordTypeId = '{{env.vars.academicParentRecordType}}' ORDER BY Name   
#- statement: print sfAcademicProgramParentIDReader
# - statement: printschema sfAcademicProgramReader
- statement: (academicParentCount)  => SELECT COUNT(*) as Num FROM sfAcademicProgramParentIDReader
#- statement: print academicParentCount

# Grab out the values we need for salesforce
# 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: (progsToWorkWith) => select '{{env.vars.academicPlanProgramRecordType}}' AS RecordTypeID
                                          , ap.ACAD_PLAN as PeopleSoft_Academic_Program_ID__c  
                                          , ap.DESCR as Name 
                                          , ap.ACAD_PROG  
                                          ,case ap.EFF_STATUS
                                            when 'A' then 0
                                            else 1
                                         end as  University_Department_Inactive__c,
                                          parentIdReader.Id as ParentId,

case ap.EFF_STATUS 
                                            when 'A' then 'false'
                                            when 'I' then 'true'
                                          else '' end as Academic_Program_Inactive__c
                                    FROM peopleSoftAcadPlanData ap
                                    left join sfAcademicProgramParentIDReader parentIdReader on parentIdReader.PeopleSoft_Academic_Parent_Code__c = ap.ACAD_PROG

#- statement: print progsToWorkWith

# Insert the programs needed from Peoplesoft into a local file as a log
- statement: insert progsToWorkWith into ProgramLogFile

# Print and count the programs from Peoplesoft needed
- statement: (workingSetCount) => SELECT COUNT(*) as Num FROM progsToWorkWith
#- statement: print workingSetCount

# Retrieve the data of the programs that need are already in Salesforce and needs to be updated
- statement: (updatePlans) => select prog.RecordTypeID
                                  , prog.University_Department_Inactive__c
                                  , prog.Name
                                  , prog.ParentId as ParentID
                                  , prog.PeopleSoft_Academic_Program_ID__c
                                  , prog.Academic_Program_Inactive__c
                                  , sfReader.Id as Id

from progsToWorkWith as prog
                                inner join sfAcademicProgramReader as sfReader on prog.PeopleSoft_Academic_Program_ID__c = sfReader.PeopleSoft_Academic_Program_ID__c

# Print and count the plans that needs to be updated
#- statement: print updatePlans
- statement: (numUpdatesCount) => SELECT COUNT(*) as Num FROM updatePlans
#- statement: print numUpdatesCount

# Update the plans that needs to be updated in Salesforce
- statement: (success,error)=>update updatePlans into sfAcadPlanWriter
#- statement: print success
#- statement: print error

# Retrieve the records from Peoplesoft that is not found in Salesforce academic program
# 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: (insertPlans) => select prog.RecordTypeID
                                  , prog.University_Department_Inactive__c
                                  , prog.Name
                                  , prog.ParentId as ParentID
                                  , prog.PeopleSoft_Academic_Program_ID__c    
                                  , prog.Academic_Program_Inactive__c                             
                                from progsToWorkWith as prog
                                LEFT ANTI join sfAcademicProgramReader as sfReader on prog.PeopleSoft_Academic_Program_ID__c = sfReader.PeopleSoft_Academic_Program_ID__c

# Print and count the plans that needs to be inserted
#- statement: print insertPlans
- statement: (numInsertCount) => SELECT COUNT(*) as Num FROM insertPlans
#- statement: print numInsertCount

# Insert the plans that needs to be inserted in Salesforce
- statement: (success,error)=>insert insertPlans into sfAcadPlanWriter
#- statement: print success
#- statement: print error

###################################################### Final Logging of results ###############################################

# - statement: (countSummation) => select concat('Number of academic programs from PeopleSoft = ', Num) from acadPlanDataCount
#                                   UNION
#                                     select concat('Number of academic departments  from PeopleSoft = ', Num) from acadProgDataCount
#                                   UNION
#                                     select concat('Number of academic programs  from SalesForce = ', Num) from progFromSFCount
#                                   UNION
#                                     select concat('Number of academic departments  from SalesForce = ', Num) from universityDepartmentsCount
#                                   UNION
#                                     select concat('Number of programs from Our Working set = ', Num) from workingSetCount
#                                   UNION
#                                     select concat('Number of programs set for update = ', Num) from numUpdatesCount
#                                   UNION
#                                     select concat('Number of programs set for insert = ', Num) from numInsertCount

# - statement: print countSummation

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