JetFormBuilder - Custom call hook - Check a specific data sum values in the form records before processing the submit

code

// place the code in the child theme functions.php or a custom code snippets plugin, like FluentSnippets
// What do this code
// This custom hook action checks the value of a specific meta field in JetFormBuilder records. 
// It only calculates values where the given field value is greater than 0 and searches only for forms 
// where the specified field exists. Additionally, it iterates only through successfully submitted forms.

// There is a predefined limit value. When a form is submitted, the system sums the values from previously successfully submitted
// forms based on the query. If the value of the submitted form is equal to or less than the limit, the form submission is allowed 
// to proceed with further actions. If not, an error message is displayed with the relevant data, and the form submission is halted.

// When you create the call hook action in the form, enter this value in the field: check-meta-values (or add your own name)
// HELP IMAGE: https://prnt.sc/RqXulDQIsfKP

// Change $limit = 5; to set the maximum value that cannot be exceeded. Replace 'price' with your specific form field name if necessary:

// 'price', // Replace 'price' with your form field name if necessary
// 'success' // Ensure this matches the status field in your records table
// Replace 'price' with the name of your custom form field (form field name!) that needs to be monitored. This field name must be consistent in both the form and the form records.

// The 'success' ensures that only successfully submitted forms are considered, but you can specify additional statuses if needed.

// Everywhere in the code where 'price' appears, replace it with your custom field name, e.g., 'mycustomfieldname'.


use Jet_Form_Builder\Exceptions\Action_Exception;

add_filter('jet-form-builder/custom-filter/check-meta-values', function($result, $request, $action_handler) {
    global $wpdb;

    // Maximum allowed limit
    $limit = 5;

    // Table names (ensure these match your database structure)
    $records_table = $wpdb->prefix . 'jet_fb_records';
    $fields_table = $wpdb->prefix . 'jet_fb_records_fields';

    // Query to get the sum of all `price` field values where the value is greater than 0 and the status is `success`
    $query = $wpdb->prepare(
        "SELECT SUM(CAST(f.field_value AS UNSIGNED)) as total_value
        FROM $records_table r
        INNER JOIN $fields_table f ON r.id = f.record_id
        WHERE f.field_name = %s AND CAST(f.field_value AS UNSIGNED) > 0 AND r.status = %s",
        'price', // Replace 'price' with your form field name if necessary
        'success' // Ensure this matches the status field in your records table
    );

    $current_total = $wpdb->get_var($query);

    // If no valid data is found, set the current total to 0
    if (is_null($current_total)) {
        $current_total = 0;
    }

    // Check if the `price` field is present in the submitted form data
    if (isset($request['price'])) { // Replace 'price' with the form field name being checked
        $submission_value = intval($request['price']);

        // Calculate the new total value including the submitted value
        $new_total = $current_total + $submission_value;

        // If the new total exceeds the limit, throw an exception
        if ($new_total > $limit) {
            throw new Action_Exception(
                'The current total is ' . $current_total . 
                ', the maximum allowed limit is ' . $limit . 
                ', and the submitted value is ' . $submission_value . '.', 
                'failed'
            );
        }

        // If the new total is less than or equal to the limit, allow submission
        return $result;
    } else {
        // Throw an exception if the `price` field is missing from the submission
        throw new Action_Exception('The "price" field is required but not provided.', 'failed');
    }

}, 10, 3);