🛡️ Stock Open and Closed Validation in Joget

#java #nocode #sql #tutorial

Overview

Managing stock levels across multiple branches requires strict controls to prevent data duplication. This script provides a validation layer that ensures business rules are followed before a new entry is saved.

How It Works

The script acts as a BeanShell Validator that executes two primary database checks:

Open Status Check: It queries the database to see if any entry for the selected branch is currently marked with a status of 'Open'.
Daily Duplicate Check: It verifies if a record has already been generated for the branch on the current calendar date using the CURDATE() SQL function.

Where to Use in Joget

Form Builder: Attach this script to the Branch Selection field (or the primary identifying field) using the BeanShell Validator plugin.

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.apps.form.model.Element;
import org.joget.apps.form.model.FormData;
import org.joget.commons.util.LogUtil;
import org.joget.apps.form.service.FormUtil;

public boolean validate(Element element, FormData formData, String[] values) {
    boolean validationPassed = true; 
    Connection conn = null;
    PreparedStatement preparedStmt = null;
    ResultSet rs = null;

    // Masked Table Name for Security
    String tableName = "app_fd_your_table_name"; 

    try {
        String elementId = FormUtil.getElementParameterName(element);
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
        conn = ds.getConnection();

        // 1. Check if a record is already 'Open'
        String query = "SELECT COUNT(*) FROM " + tableName + " WHERE c_status = 'Open' AND c_branch_name = ?";
        preparedStmt = conn.prepareStatement(query);
        preparedStmt.setString(1, values[0]);
        rs = preparedStmt.executeQuery();

        if (rs.next() && rs.getInt(1) > 0) {
            formData.addFormError(elementId, "Validation failed: A register is already open for this branch. Please close it first.");
            validationPassed = false;
        }

        // 2. Check for daily duplicate creation
        String checkAlreadyCreated = "SELECT COUNT(*) FROM " + tableName + " WHERE c_branch_name = ? AND DATE(dateCreated) = CURDATE()";
        preparedStmt = conn.prepareStatement(checkAlreadyCreated);
        preparedStmt.setString(1, values[0]);
        rs = preparedStmt.executeQuery();

        if (rs.next() && rs.getInt(1) > 0) {
            formData.addFormError(elementId, "A record has already been created for today.");
            validationPassed = false;
        }

    } catch (Exception e) {
        LogUtil.error("Validation Script", "Error occurred during execution.");
        validationPassed = false; 
    } finally {
        try {
            if (rs != null) rs.close();
            if (preparedStmt != null) preparedStmt.close();
            if (conn != null) conn.close();
        } catch (Exception e) {
            // Resource cleanup
        }
    }
    return validationPassed;
}

return validate(element, formData, values);

Customization Tips

⚙️ Table Name: Update the tableName variable at the start of the script with your actual Joget Table ID.
⚙️ Status Match: If your application uses 'Active' or 'In-Progress' instead of 'Open', update the SQL string accordingly.

🔒 Security Note

Table Masking: By using a variable for the table name, we prevent leaking the database schema in the logic description.
SQL Injection: We use PreparedStatement with ? placeholders to ensure that user input is handled safely.
Managed Connections: The script uses the Joget setupDataSource bean, ensuring credentials are never exposed in the script.

DEV Community