⏰ Send 30/60/90-Day Expiry Reminders in Joget with BeanShell

1. Overview

Policy, contract, SOP, and certificate records often need reminders before they expire. This article shows a Joget BeanShell pattern for checking records at 90, 60, 30, and 0 days before expiry.

The script reads configured form tables, calculates expiry based on the published date, updates expired records, and leaves a clear place to plug in notification logic.

2. How It Works

• Define reminder checkpoints: 90, 60, 30, and 0 days.
• Loop through the target Joget form tables.
• Compare each record's published date against the current date.
• Mark records as expired when the expiry date is reached.
• Add email notification logic where the placeholder methods are defined.

3. Where to Use in Joget

• Workflow Builder: as a Tool script for scheduled or process-driven checks.
• Scheduler Plugin: for daily expiry scanning.
• Form Builder: when expiry fields are stored in app form tables.

4. Full Code

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.joget.apps.app.model.AppDefinition;
import org.joget.apps.app.service.AppPluginUtil;
import org.joget.plugin.base.ApplicationPlugin;
import org.joget.plugin.base.Plugin;
import org.joget.plugin.base.PluginManager;
import org.joget.plugin.property.model.PropertyEditable;

public void checkExpiryDate() {
    logInfo("Inside checkExpiryDate Method");
    int[] expiryDates = {90, 60, 30, 0};

    for (int expiryDate : expiryDates) {
        fetchRecordsByExpiryDate("app_fd_policy_records", expiryDate);
        fetchRecordsByExpiryDate("app_fd_work_instruction_records", expiryDate);
        fetchRecordsByExpiryDate("app_fd_sop_records", expiryDate);
    }
}

public void fetchRecordsByExpiryDate(String tableName, int expiryDate) {
    logInfo("Inside fetchRecordsByExpiryDate method for table: " + tableName + " with expiry date: " + expiryDate + " days");
    try {
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
        Connection conn = ds.getConnection();

        // If expiryDate is 0, call the update method
        if (expiryDate == 0) {
            updateExpirationStatus(tableName);
        }

        String query1 = "SELECT * FROM " + tableName +
                " WHERE DATE_ADD(STR_TO_DATE(c_published_date, '%d/%m/%Y'), INTERVAL 3 YEAR) - INTERVAL " + expiryDate + " DAY = CURRENT_DATE()";

        PreparedStatement preparedStmt1 = conn.prepareStatement(query1);
        ResultSet rs1 = preparedStmt1.executeQuery();

        while (rs1.next()) {
            logInfo("Processing record with Req Type: " + rs1.getString("c_request_type"));
            // Additional processing can continue here...
        }
        conn.close();
    } catch (Exception e) {
        logInfo("Error in fetchRecordsByExpiryDate method: " + e.getMessage());
    }
}

public void updateExpirationStatus(String tableName) {
    logInfo("Updating expiration status for table: " + tableName);
    try {
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
        Connection conn = ds.getConnection();
        String updateQuery = "UPDATE " + tableName +
                             " SET c_expired = 'Yes' " +
                             "WHERE DATE_ADD(IF(TRIM(c_published_date) != '', STR_TO_DATE(c_published_date, '%d/%m/%Y'), NULL), INTERVAL 3 YEAR) = CURRENT_DATE()";

        PreparedStatement pstmt = conn.prepareStatement(updateQuery);
        int updatedRows = pstmt.executeUpdate();
        logInfo(updatedRows + " rows updated to 'Expired' in " + tableName);
        conn.close();
    } catch (Exception e) {
        logInfo("Error in updating expiration status: " + e.getMessage());
    }
}

public String calculateExpiryDate(String publishedDate, int daysToAdd) {
    // Assuming c_published_date is in the format 'dd/MM/yyyy'
    java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("dd/MM/yyyy");
    java.util.Date date = sdf.parse(publishedDate);
    java.util.Calendar calendar = java.util.Calendar.getInstance();
    calendar.setTime(date);
    calendar.add(java.util.Calendar.DAY_OF_MONTH, daysToAdd);
    return sdf.format(calendar.getTime());
}

public void sendEmail(String toEmail, String subject, String messageTemplate, Map dynamicContent) {
    // Sending email logic should be implemented here
}

public String formatEmailMessage(String template, Map dynamicContent) {
    // Formatting email message should be implemented here
    return template;  // Placeholder return
}

public String getEmailTemplate(Map dynamicContent, String expiryDate) {
    // Email template logic should be implemented here
    return "Email template content";  // Placeholder return
}

private void logInfo(String message) {
    LogUtil.info("", message);
}

checkExpiryDate();  // Calling the main method to trigger the flow

5. Example Use Cases

• Policy expiry reminders.
• Contract renewal alerts.
• SOP review notifications.
• License or certificate expiry tracking.

6. Customization Tips

• Replace table names with your own Joget form table names.
• Adjust the expiry duration from 3 years to your real business rule.
• Implement sendEmail, ormatEmailMessage, and getEmailTemplate using your app's email settings.
• Keep date formats consistent with your stored form data.

7. Key Benefits

• Centralized expiry checking across multiple forms.
• Easy reminder checkpoints.
• Keeps expired status updated automatically.
• Works with Joget's existing datasource.

8. Security Note

Do not hardcode real recipient lists, SMTP passwords, or internal table names in public code. Use app variables, environment values, or Joget plugin properties for sensitive settings.

9. Final Thoughts

This pattern is useful when expiry tracking must run reliably without manual review. Keep the reminder rules simple, log each step, and test with sample records before enabling production notifications.