Error when using bean shell form binder to store data

Hi everyone! I use bean shell form binder to store data but got "Error executing script" when submit. What I am trying to do is

1) Requester will select a few PIC using grid form and their names and plants will be stored in a text field as an array.

2) In PIC inbox, everyone can see the request submitted but only PIC that name contains in the array can do checking else the system will show message.

3) After one PIC do checking he can still see the inbox. Since a few PIC need to do checking, I want to avoid those who have submitted to submit again as it will cause duplicate ticket number.

The request status will be controlled by requester. If the status still 'Active' all PIC can see the inbox, however, if 'Inactive' then only the inbox will disappear.

4) The logic in coding that I am trying to do is:

After requester submit, the initial ticket number will be like "RBT - 01".
After one PIC do checking, the ticket number will be updated. Ticket number + plant.
Eg "RBT - 01 : Plant A"
So if the PIC do checking again (we assume he forgot that he already checked), the code will check if the plant selected and PIC name equals as in the database, system will show error.

So far what I have done

import java.sql.Connection;
import javax.swing.*;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import java.text.SimpleDateFormat;  
import java.util.Calendar;
import java.util.Date;  
import java.time.format.DateTimeFormatter;  
import java.time.LocalDateTime; 
import org.joget.apps.app.service.AppUtil;
import org.joget.apps.form.model.Form;
import org.joget.apps.form.model.Element;
import org.joget.apps.form.model.FormData;
import org.joget.apps.form.model.FormRow;
import org.joget.apps.form.model.FormRowSet;
import org.joget.apps.form.service.FormUtil;
import org.joget.commons.util.UuidGenerator;
import java.text.DecimalFormat;
import org.joget.apps.form.model.FormStoreBinder;
import org.joget.plugin.base.PluginManager;

public FormRowSet store(Element element, FormRowSet rows, FormData formData) {
    String ticketNumberRBT, date, requestorName, requestorEmail, companyName, itemDescription, quarter, attachment, list_plant, list_PIC, picName, picEmail, plant, Action;
    
    Boolean x = true;
    Boolean y = false;
    
    Iterator i= rows.iterator(); // Iterating grid rows
    
    while (i.hasNext()) {
        FormRow row = (FormRow) i.next();
        plant = row.get("plant");
        ticketNumberRBT = row.get("ticketNumberRBT");
        date = row.get("date");
        requestorName = row.get("requestorName");
        requestorEmail = row.get("requestorEmail");
        companyName = row.get("companyName");
        itemDescription = row.get("itemDescription");
        quarter = row.get("quarter");
        attachment = row.get("attachment");
        list_plant = row.get("list_plant");
        list_PIC = row.get("list_PIC");
        picName = row.get("picName");
        picEmail = row.get("picEmail");
        Action = row.get("Action");

    }
    
    //check the rows is not empty before store it
    if (rows != null && !rows.isEmpty()) {
               try{
                DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
                con = ds.getConnection();
                
                if(!con.isClosed()) {
                    
                    String sql = "SELECT * FROM app_fd_rebateR WHERE c_requestorName = ? AND c_ticketNumberRBT LIKE '?%' and c_plant = ? and c_picName = ?";
                    PreparedStatement stmt = con.prepareStatement(sql);
                    stmt.setString(1, requestorName);
                    stmt.setString(2, ticketNumberRBT);
                    stmt.setString(3, plant);
                    stmt.setString(4, picName);
                    ResultSet results = stmt.executeQuery();
                    
                    while(results.next()){
                        y = true;
                    
                        // String rplant = results.getString("c_plant");
                        // String rpicEmail  =results.getString("c_picEmail");
                        // String rrequestorName = results. getString("c_requestorName")
                        // if (rplant.equals(plant) && rpicEmail.equals(picEmail) && rrequestorName.equals(requestorName) {
                        //     y = true;
                        //     break;
                        // }
                    }
                    
                    if(y){
                            String id = FormUtil.getElementParameterName(element);
                            formData.addFormError(id, "Sorry, duplicate ticket number and plant ");
                               
                    }    
                    else{
                            PluginManager pluginManager = (PluginManager) AppUtil.getApplicationContext().getBean("pluginManager");
                            FormStoreBinder binder = (FormStoreBinder) pluginManager.getPlugin("org.joget.apps.form.lib.WorkflowFormBinder");
                            binder.store(element, rows, formData);
                        }
                }
                }
                catch (Exception ex) {
                        LogUtil.error("Your App/Plugin Name", ex, "Error storing using jdbc");
                }
                finally{
                    
                    try {
                    if(con != null) {
                        con.close();
                      }
                    }    
                    catch(SQLException e) {/* ignored */}
                
                } 
            }
    else{
            String id2 = FormUtil.getElementParameterName(element);
            formData.addFormError(id2, "Hi,You do not fill up the required field");
        }

 

     
    return rows;
}
return store(element, rows, formData);

Really need help, been doing this for days but still error. Thank you!

Error when using bean shell form binder to store data

1 answer