Using SQL date functions to get new values

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

# Project Name - Using SQL date functions to get new values

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

# Description -   This recipe shows you how to utilize SQL logic to find a date differential from the current timestamp to a recipe run start time.

## To use this recipe we need to configure below environment variables:
                  ## TimeType (Ex.Hour or hr.)
                  ## TimePeriod (Ex.8 or 10)
                  ## EmailAddress (Ex. abc@lingk.io or abc@lingk.io,xyz@lingk.io)
                  ## NotificationObjectType(eventObject) (Ex. university-of-pennsylvania.online-registration.emailnotify)

## Recipe steps:
                  ## 1. Configure environment variables
                  ## 2. Use logReader connector to read the logs and signalWriter connector to call send notification recipe.
                  ## 3. Get logs from logReader
                  ## 4. Calculate start_date_time and end_date_time using time period in env variable to fetch the logs.
                  ## 5. Get logs between the time interval using message filter and fetch the count of registrants from the message.
                  ## 6. Filter logs which have registrations (number of registrants are more than 0).
                  ## 7. Set the notification message to send in the email, if there is no registrations in given time interval and call the Send Notification event.

# Systems - 
# Connectors - logReader, Lingk
# Data Flows - Single Direction
# Connection Type -

# Add Recipe notes / Change log information here!

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

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

connectors:

-
    name: logReader
    type: logReader

- 
    name: signalWriter
    type: lingkWriter
    properties: 
      apiEndpoint: https://www.lingkapis.com/v1/@self/events

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

# STATEMENTS specify how the data should be processed while in memory

statements:
  - statement: print logReader 
  - statement: (logReaderCount) => select count(*) from logReader
  - statement: print logReaderCount

###### Start - Insert Logs ######

# CASE function works as a dictionary and retrieve a specific value depending to the type field value pulled from the table as long as it is not null.
  - statement: |
      (timeIntervelforLog) => SELECT
                                CASE WHEN IFNULL(UPPER(TRIM('{{env.vars.TimeType}}')), '') =  "HOUR" OR IFNULL(UPPER(TRIM('{{env.vars.TimeType}}')), '') LIKE "H%"
                                        THEN date_format(current_timestamp() - INTERVAL {{env.vars.TimePeriod}} hours, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") 
                                     WHEN IFNULL(UPPER(TRIM('{{env.vars.TimeType}}')), '') =  "DAYS" OR IFNULL(UPPER(TRIM('{{env.vars.TimeType}}')), '') LIKE "D%"
                                        THEN date_format(date_sub(current_timestamp(), {{env.vars.TimePeriod}}), "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") 
                                ELSE '' 
                                END                                                                          `start_date_time`,

date_format(current_timestamp(), "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")             `end_date_time`

- statement: print timeIntervelforLog
  - statement: INSERT timeIntervelforLog INTO executionContext

###### End - Insert Logs ######

- statement: stop

# Records between the date range
  - statement: (LogReaderInfo) => SELECT * FROM logReader
                                  WHERE (category = 'RegistrationsCount'
                                  OR `message` LIKE '%Total number of registrant in Learning Stream:%')
                                  AND to_timestamp(`createdDate`) 
                                       BETWEEN to_timestamp("{{executionContext.timeIntervelforLog.start_date_time}}") 
                                       AND to_timestamp("{{executionContext.timeIntervelforLog.end_date_time}}")
                                  ORDER BY `createdDate` DESC

- statement: PRINT LogReaderInfo
  - statement: (LogReaderInfoCount) => SELECT COUNT(*) FROM LogReaderInfo
  - statement: PRINT LogReaderInfoCount

# Numbers of registrants
  - statement: (registrationCountInfo) => SELECT
                                       recipeName,
                                       createdDate,
                                       environmentKey,
                                       message,
                                       int(substring_index(`message`, ':', -1))      `no_of_registrants`
                                     FROM LogReaderInfo
  - statement: PRINT registrationCountInfo

# Records if there is any registrations 
  - statement: (LogsWithRegistrations) => SELECT
                                            createdDate,
                                            no_of_registrants,
                                            message,
                                            recipeName
                                          FROM registrationCountInfo
                                          WHERE no_of_registrants > 0

- statement: PRINT LogsWithRegistrations
  - statement: (LogsWithRegistrationsCount) => SELECT COUNT(*) FROM LogsWithRegistrations
  - statement: PRINT LogsWithRegistrationsCount

# CASE function works as a dictionary and retrieve a specific value depending to the type field value pulled from the table as long as it is not null.
  - statement: (NotificationMsgToSend) => SELECT
                                CASE WHEN (SELECT COUNT(*) FROM LogsWithRegistrations) = 0 
                                      THEN "Hello, \\n\\nNo LearningStream course registrations occurred in last {{env.vars.TimePeriod}} '{{env.vars.TimeType}}'."
                                  ELSE '' END                                         `message`

- statement: PRINT NotificationMsgToSend

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