2.3 - OverdueBookAlert Function

Last updated 13/06/2023

The OverdueBookAlert function will be attached to the Do Run event for the Overdue Book process and will execute when the process runs.

The OverdueBookAlert function selects data from the database about each member borrowing books. The query in the function, is filtering the results to only include records where the number of days borrowed is greater than thirty, no overdue notice has previously been sent, and there is no return date in the database. The executeQuery() function retrieves the members that need to be notified based on the above criteria.

The executeAction() function is used to execute the mail merge and the context is supplied which contains information Five can use and data that can be merged into the Overdue Book mail merge text. Two of the parameters being passed are Five's SMTPToEmail and SMTPToName, these allow Five to configure the envelope of the email. The other two parameters are on the variable property on the Five object and are merged with your merge text as the body of your email.

Navigate to the Code Editor

1. Select Logic in the menu.

2. Select Code Editor in the sub-menu.

Figure 1 - Code Editor menu item

Add the OverdueBookAlert Function

1. Click the Add New Code button.

Figure 2 - Add New Code button

2. Type OverdueBookAlert in the Function ID field.

3. Click the lookup icon in the Language field and select JavaScript.

4. Click the OKAY button.

Figure 3 - Add the OverdueBookAlert function

5. Highlight and delete the template in the Code Editor.

Figure 4 - Clean the Code Editor

6. Click the Copy button on the code block below.

OverdueBookAlert()

function OverdueBookAlert(five, context, result)  {
    const sql = `SELECT 
        BorrowHistoryKey, iUser.FullName AS FullName, iUser.Email AS Email, DateDiff(Now(), BorrowHistory.Date) AS DaysBorrowed, OverDueNotice, DateReturned, Book.Title AS Title 
            FROM 
        borrowhistory
        JOIN iUser ON iUser.iUserKey = BorrowHistory.MemberKey
        INNER JOIN Book ON BorrowHistory.BookKey = Book.BookKey
        HAVING
            DaysBorrowed > 30 AND OverDueNotice IS NULL AND DateReturned IS NULL`;

    const queryResults = five.executeQuery(sql, 0);
    if (queryResults.isOk() === false) {
        return five.createError(queryResults);
    }

    for (let i = 0; i < queryResults.records.length; i++) {
        const BorrowHistoryKey = queryResults.records[i].BorrowHistoryKey;
        const FullName = queryResults.records[i].FullName;
        const Email = queryResults.records[i].Email;
        const Title = queryResults.records[i].Title;

        const mailMergeContext = {SMTPToEmail: Email, SMTPToName: FullName, Name: FullName, Title: Title};
        const mailResult = five.executeAction('OverdueBook', mailMergeContext);
        if (mailResult.isOk() === true) {
            const db = five.getDatabaseConnectionByID('BookMembershipDB');   
            const tx = five.startTransaction(db);
            const updateResult = five.executeQuery(tx, `UPDATE borrowhistory SET OverDueNotice = NOW() WHERE BorrowHistoryKey = ?`, 0, BorrowHistoryKey);            
            if (updateResult.isOk() === false) {
                return five.createError(updateResult);
            }

            five.commit(tx);
        } 

        return five.success(result);
    }

    return five.success(result);
}

7. Paste the code into the Code Editor.

8. Click the Save Current Tab button.

Figure 5 - Save the OverdueBookAlert function