Knowledge Base
  • Login
Font Size:

How to develop a Form Submission Statistics Generator (MySQL)

In this tutorial, you will be following the guideline of developing a plugin to develop your Form Submission Statistics Generator. Please also refer to the very first tutorial How to develop a Bean Shell Hash Variable for more details steps.

1. What is the problem?

You have 2 SQL Chart menus (the queries used are dependent on MySQL) to show form data submission statistics and these two menus always need to replicate for different forms.

2. What is your idea to solve the problem?

You can develop a Generator Plugin to ease the process of replicating the 2 SQL Chart menus for other forms.

3. What is the input needed for your plugin?

To develop a Generator Plugin for your 2 SQL Chart menus, you can consider providing the following as input.

  1. Userview ID: Which userview to add these 2 SQL menus
  2. Some options for label change in the Category label, Menu label, and Chart label

4. What is the output and expected outcome of your plugin?

2 SQL Chart menus will be added to the selected userview under a new category. One of the menus will show the monthly submission chart and another one will show the daily submission chart based on the year and month filter.

5. Is there any resources/API that can be reused?

First, you can build your two SQL Chart menus in one of the existing userview. Then, copy the JSON definition of the category that contains your 2 SQL Chart menus from the ADVANCED: JSON Definition section at the bottom of the UI Builder.

You will get your JSON definition of the category as follows. Please note that the queries used in the 2 SQL Chart menus are dependent on the MySQL database.

{
    "className": "org.joget.apps.userview.model.UserviewCategory",
    "properties": {
        "id": "category-8722A52FFBB64D058E2CD41174922807",
        "label": "Proposal Form Statistics"
    },
    "menus": [{
        "className": "org.joget.plugin.enterprise.SqlChartMenu",
        "properties": {
            "id": "807F165BFA9C4BB589E5B52E4C071250",
            "customId": "crm_proposal_monthly",
            "label": "Monthly Submission Chart",
            "chartType": "bar",
            "title": "Proposal Form Monthly Submission Chart",
            "categoryAxisLabel": "Month",
            "xAxisDisplayAS": "",
            "valueAxisLabel": "Number",
            "yaxisPrefix": "",
            "showLegend": "",
            "showValueLabel": "true",
            "stack": "",
            "horizontal": "",
            "chartWidth": "100%",
            "chartHeight": "80%",
            "colors": "",
            "query": "SELECT DATE_FORMAT(STR_TO_DATE(m.monthYear, '%c-%Y'),'%b %y') as monthYear, COUNT(fd.dateCreated) AS 'Number'\nFROM \n(\n    SELECT my.month, CONCAT(my.month, '-', IF(('#requestParam.year?sql#' REGEXP '^[0-9]{4}$'), '#requestParam.year?sql#' , '#date.yyyy?sql#')) AS monthYear FROM (\n        SELECT 1 AS month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12\n    ) my\n) m\nLEFT JOIN \n(\n\t  select dateCreated FROM app_fd_crm_proposal\n) fd\nON m.monthYear=DATE_FORMAT(fd.dateCreated,'%c-%Y')\nGROUP BY monthYear\nORDER BY m.month",
            "customHeader": "<div class=\"filter\">\n    <form action=\"?\" method=\"GET\">\n        <label>Year: <\/label><input name=\"year\" value=\"#requestParam.year?html#\"\/>\n        <input type=\"submit\" value=\"Show\"\/>\n    <\/form>\n<\/div>\n<script>\n    $(function(){\n        if ($(\"[name='year']\").val() === \"\") {\n            $(\"[name='year']\").val(\"#date.yyyy#\");\n        }\n    });\n<\/script>\n<br\/>\n<br\/>",
            "customFooter": "",
            "datasource": "default",
            "keyName": ""
        }
    }, {
        "className": "org.joget.plugin.enterprise.SqlChartMenu",
        "properties": {
            "id": "39E2163319D84FD693D164D92FA93C06",
            "customId": "crm_proposal_daily",
            "label": "Daily Submission Chart",
            "chartType": "bar",
            "title": "Proposal Form Daily Submission Chart",
            "categoryAxisLabel": "Date",
            "xAxisDisplayAS": "",
            "valueAxisLabel": "Number",
            "yaxisPrefix": "",
            "showLegend": "",
            "showValueLabel": "true",
            "stack": "",
            "horizontal": "true",
            "chartWidth": "100%",
            "chartHeight": "80%",
            "colors": "",
            "query": "SELECT d.date_field, COUNT(fd.dateCreated) AS 'Number'\nFROM\n(\n    SELECT\n        MAKEDATE(IF(('#requestParam.year?sql#' REGEXP '^[0-9]{4}$'), '#requestParam.year?sql#' , '#date.yyyy?sql#'),1) +\n        INTERVAL (IF(('#requestParam.month?sql#' REGEXP '^[0-9]{2}$'), '#requestParam.month?sql#' , '#date.MM?sql#') -1) MONTH +\n        INTERVAL daynum DAY date_field\n    FROM\n    (\n        SELECT t*10+u daynum\n        FROM\n            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,\n            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3\n            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7\n            UNION SELECT 8 UNION SELECT 9) B\n        ORDER BY daynum\n    ) AA\n) d\nLEFT JOIN \n(\n\t  select dateCreated FROM app_fd_crm_proposal\n) fd\nON d.date_field=DATE_FORMAT(fd.dateCreated,'%Y-%m-%d')\nWHERE DATE_FORMAT(d.date_field,'%m') = IF(('#requestParam.month?sql#' REGEXP '^[0-9]{2}$'), '#requestParam.month?sql#' , '#date.MM?sql#')\nGROUP BY d.date_field\nORDER BY d.date_field desc",
            "customHeader": "<div class=\"filter\">\n    <form action=\"?\" method=\"GET\">\n        <label>Year: <\/label><input name=\"year\" value=\"#requestParam.year?html#\"\/>&nbsp;&nbsp;&nbsp;\n        <label>Month: <\/label><select name=\"month\"\/>\n            <option value=\"01\">Jan<\/option>\n            <option value=\"02\">Feb<\/option>\n            <option value=\"03\">Mar<\/option>\n            <option value=\"04\">Apr<\/option>\n            <option value=\"05\">May<\/option>\n            <option value=\"06\">Jun<\/option>\n            <option value=\"07\">Jul<\/option>\n            <option value=\"08\">Aug<\/option>\n            <option value=\"09\">Sep<\/option>\n            <option value=\"10\">Oct<\/option>\n            <option value=\"11\">Nov<\/option>\n            <option value=\"12\">Dec<\/option>\n        <\/select>\n        <input type=\"submit\" value=\"Show\"\/>\n    <\/form>\n<\/div>\n<script>\n    $(function(){\n        if ($(\"[name='year']\").val() === \"\") {\n            $(\"[name='year']\").val(\"#date.yyyy#\");\n        }\n        if ($(\"[name='month']\").val() !== \"#requestParam.month?javascript#\" \n            && '#requestParam.month?javascript#' !== \"\"\n            && $(\"[name='month'] option[value='#requestParam.month?javascript#']\").length > 0 ) {\n            $(\"[name='month']\").val('#requestParam.month?javascript#');\n        } else {\n            $(\"[name='month']\").val(\"#date.MM#\");\n        }\n    });\n<\/script>\n<br\/>\n<br\/>",
            "customFooter": "",
            "datasource": "default",
            "keyName": ""
        }
    }]
}
 

After that, you can utilize the GeneratorUtil to add the category JSON definition to your selected userview JSON definition.

6. Prepare your development environment

You need to always have your Joget DX Source Code ready and built by following this guideline. 

The following tutorial is prepared with a Macbook Pro and Joget Source Code version 5.0.0. Please refer to Guideline for Developing a Plugin for other platform commands.

Let's say your folder directory is as follows. 

- Home
  - joget
    - plugins
    - jw-community

The plugins directory is the folder you will create and store all your plugins and the jw-community directory is where the Joget DX Source code is stored.

Run the following command to create an Apache Maven project in the plugins directory. 

cd joget/plugins/
~/joget/jw-community/wflow-plugin-archetype/create-plugin.sh org.joget.tutorial form_submission_statistics_generator 8.0-Snapshot

Then, the shell script will ask us to key in a version for your plugin and ask us for confirmation before generating the Apache Maven project.

Define value for property 'version':  1.0-SNAPSHOT: : 8.0-Snapshot
[INFO] Using property: package = org.joget.tutorial
Confirm properties configuration:
groupId: org.joget.tutorial
artifactId: form_submission_statistics_generator
version: 5.0.0
package: org.joget.tutorial
Y: : y

You should get a "BUILD SUCCESS" message shown in your terminal and a form_submission_statistics_generator folder created in the plugins folder.

Open the Apache Maven project with your IDE of choice. This tutorial uses NetBeans.

7. Just code it!

a. Extending the abstract class of a plugin type

Create a FormSubmissionStatisticsGenerator class under org.joget.tutorial package. Then, extend the class with the org.joget.apps.generator.model.GeneratorPlugin abstract class. Please refer to Generator Plugin.

b. Implement all the abstract methods

As usual, you have to implement all the abstract methods. You will use the AppPluginUtil.getMessage method to support i18n and use the constant variable MESSAGE_PATH for the message resource bundle directory.

Implementation of all basic abstract methods
package org.joget.tutorial;
import org.joget.apps.app.service.AppPluginUtil;
import org.joget.apps.app.service.AppUtil;
import org.joget.apps.generator.model.GeneratorPlugin;
import org.joget.apps.generator.model.GeneratorResult;
public class FormSubmissionStatisticsGenerator extends GeneratorPlugin {
     
    private final static String MESSAGE_PATH = "messages/FormSubmissionStatisticsGenerator";
     
    public String getName() {
        return "File Link Datalist Formatter";
    }
  
    public String getVersion() {
        return "5.0.0";
    }
     
    public String getClassName() {
        return getClass().getName();
    }
  
    public String getLabel() {
        //support i18n
        return AppPluginUtil.getMessage("org.joget.tutorial.FormSubmissionStatisticsGenerator.pluginLabel", getClassName(), MESSAGE_PATH);
    }
     
    public String getDescription() {
        //support i18n
        return AppPluginUtil.getMessage("org.joget.tutorial.FormSubmissionStatisticsGenerator.pluginDesc", getClassName(), MESSAGE_PATH);
    }
  
    public String getPropertyOptions() {
        return AppUtil.readPluginResource(getClassName(), "/properties/formSubmissionStatisticsGenerator.json", null, true, MESSAGE_PATH);
    }
    @Override
    public String getExplanation() {
        //support i18n
        return AppPluginUtil.getMessage("generator.formSubmissionStatistics.explanation", getClassName(), MESSAGE_PATH);
    }
    @Override
    public GeneratorResult generate() {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}

Then, you have to do a UI for the admin user to provide inputs for your plugin. In the getPropertyOptions method, you already specify your Plugin Properties Options definition file is located at /properties/formSubmissionStatisticsGenerator.json. Let's create a resources/properties directory under the form_submission_statistics_generator/src/main directory. After creating the directory, create a file named formSubmissionStatisticsGenerator.json in the properties folder.

In the properties definition options file, you will need to provide options as below. Please note that you can use the @@message.key@@ syntax to support i18n in your properties options.

[{
    title : '@@generator.formSubmissionStatistics.config@@',
    properties : [
    {
        name : 'userviewId',
        label : '@@generator.formSubmissionStatistics.userview@@',
        type : 'selectbox',
        value: '[default_userviewId]',
        options_ajax : '[CONTEXT_PATH]/web/json/console/app[APP_PATH]/userview/options'
    }]
},
{
    title : '@@generator.formSubmissionStatistics.advanced@@',
    properties : [
    {
        label : '@@generator.formSubmissionStatistics.label.options@@',
        type : 'header'
    },
    {
        name : 'monthlyChartTitle',
        label : '@@generator.formSubmissionStatistics.monthlyChartTitle@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.monthlyChartTitle.value@@'
    },
    {
        name : 'monthlyXAxisLabel',
        label : '@@generator.formSubmissionStatistics.monthlyXAxisLabel@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.monthlyXAxisLabel.value@@'
    },
    {
        name : 'dailyChartTitle',
        label : '@@generator.formSubmissionStatistics.dailyChartTitle@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.dailyChartTitle.value@@'
    },
    {
        name : 'dailyXAxisLabel',
        label : '@@generator.formSubmissionStatistics.dailyXAxisLabel@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.dailyXAxisLabel.value@@'
    },
    {
        name : 'yAxisLabel',
        label : '@@generator.formSubmissionStatistics.yAxisLabel@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.yAxisLabel.value@@'
    },
    {
        name : 'yearLabel',
        label : '@@generator.formSubmissionStatistics.yearLabel@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.yearLabel.value@@'
    },
    {
        name : 'monthLabel',
        label : '@@generator.formSubmissionStatistics.monthLabel@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.monthLabel.value@@'
    },
    {
        name : 'showLabel',
        label : '@@generator.formSubmissionStatistics.showLabel@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.showLabel.value@@'
    },
    {
        label : '@@generator.formSubmissionStatistics.useriewMenu.options@@',
        type : 'header'
    },
    {
        name : 'categoryLabel',
        label : '@@generator.formSubmissionStatistics.categoryLabel@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.categoryLabel.value@@'
    },
    {
        name : 'monthlyMenuId',
        label : '@@generator.formSubmissionStatistics.monthlyMenuId@@',
        type : 'textfield',
        required : 'true',
        regex_validation : '^[a-zA-Z0-9_]+$',
        validation_message : '@@generator.formSubmissionStatistics.menuId.invalidId@@',
        value : '[formId]_monthly'
    },
    {
        name : 'monthlyMenuLabel',
        label : '@@generator.formSubmissionStatistics.monthlyMenuLabel@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.monthlyMenuLabel.value@@'
    },
    {
        name : 'dailyMenuId',
        label : '@@generator.formSubmissionStatistics.dailyMenuId@@',
        type : 'textfield',
        required : 'true',
        regex_validation : '^[a-zA-Z0-9_]+$',
        validation_message : '@@generator.formSubmissionStatistics.menuId.invalidId@@',
        value : '[formId]_daily'
    },
    {
        name : 'dailyMenuLabel',
        label : '@@generator.formSubmissionStatistics.dailyMenuLabel@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.dailyMenuLabel.value@@'
    },
    {
        label : '@@generator.formSubmissionStatistics.createUserviewOptions@@',
        type : 'header',
        control_field: 'userviewId',
        control_value: '',
        control_use_regex: 'false',
    },   
    {
        name : 'userviewNewId',
        label : '@@generator.formSubmissionStatistics.userview.id@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.userview.id.value@@',
        regex_validation : '^[a-zA-Z0-9_]+$',
        validation_message : '@@generator.formSubmissionStatistics.userview.id.invalidId@@',
        control_field: 'userviewId',
        control_value: '',
        control_use_regex: 'false'
    },   
    {
        name : 'userviewName',
        label : '@@generator.formSubmissionStatistics.userview.name@@',
        type : 'textfield',
        required : 'true',
        value : '@@generator.formSubmissionStatistics.userview.name.value@@',
        control_field: 'userviewId',
        control_value: '',
        control_use_regex: 'false'
    },   
    {
        name : 'userviewDesc',
        label : '@@generator.formSubmissionStatistics.userview.description@@',
        type : 'textarea',
        rows : "3",
        control_field: 'userviewId',
        control_value: '',
        control_use_regex: 'false'
    }]
}]
In the plugin properties option, you may notice you are using [default_userviewId] in the userviewId property. You will use the [formName] and [formId] in some properties as well. So, you will need to modify the getPropertyOptions method to cater to these values.
public String getPropertyOptions() {
    String options = AppUtil.readPluginResource(getClassName(), "/properties/formSubmissionStatisticsGenerator.json", null, true, MESSAGE_PATH);
     
    //populate value like [formName] and [formId]
    options = GeneratorUtil.populateFormMeta(options, getFormId(), getAppDefinition());
     
    //populate value of [default_userviewId]
    options = options.replace("[default_userviewId]", GeneratorUtil.getFirstAvailableUserviewId(getAppDefinition()));
     
    return options;
}

Once you have done the properties option to collect input, you can work on the main method of the plugin which is the format method.

@Override
public GeneratorResult generate() {
    GeneratorResult result = new GeneratorResult();
    AppDefinition appDef = getAppDefinition();
 
    try {
        //get userview
        UserviewDefinitionDao userviewDefinitionDao = (UserviewDefinitionDao) AppUtil.getApplicationContext().getBean("userviewDefinitionDao");
        UserviewDefinition userviewDef = null;
        String json = null;
        String userviewId = getPropertyString("userviewId");
        String userviewName;
        String userviewDesc;
        if (userviewId != null && !userviewId.isEmpty()) {        
            userviewDef = userviewDefinitionDao.loadById(userviewId, appDef);
        }
        if (userviewDef != null) {
            userviewName = userviewDef.getName();
            userviewDesc = userviewDef.getDescription();
            json = userviewDef.getJson();
        } else {
            userviewId = getPropertyString("userviewNewId");
            int count = 0;
            while (isExist(userviewId, userviewDefinitionDao)) {
                count++;
                userviewId = userviewId + count;
            }
            userviewName = getPropertyString("userviewName");
            userviewDesc = getPropertyString("userviewDesc");
        }
        if (json == null || json.isEmpty()) {
            //create a new userview json
            json = GeneratorUtil.createNewUserviewJson(userviewId, userviewName, userviewDesc);
        }
         
        //add the category json to userview json
        json = GeneratorUtil.addCategoryJsonToUserviewJson(getCategoryJson(appDef), json);
         
        //Store the userview json
        if (userviewDef != null) {
            userviewDef.setJson(json);
            userviewDefinitionDao.update(userviewDef);
        }else {
            userviewDef = new UserviewDefinition();
            userviewDef.setJson(json);
            userviewDef.setId(userviewId);
            userviewDef.setName(userviewName);
            userviewDef.setAppDefinition(appDef);
            userviewDefinitionDao.add(userviewDef);
             
            //Set current published version
            final AppDefinition currentAppDef = appDef;
            TransactionTemplate transactionTemplate = (TransactionTemplate)AppUtil.getApplicationContext().getBean("transactionTemplate");
            transactionTemplate.execute(new TransactionCallback<Object>() {
                public Object doInTransaction(TransactionStatus ts) {
                    AppService appService = (AppService)AppUtil.getApplicationContext().getBean("appService");
                    appService.publishApp(currentAppDef.getId(), currentAppDef.getVersion().toString());
                    return null;
                }
            });
        }
         
        //show message to continue edit
        String editLink = WorkflowUtil.getHttpServletRequest().getContextPath() + "/web/console/app/"+appDef.getAppId()+"/"+appDef.getVersion()+"/userview/builder/"+userviewId;
        String msg = AppPluginUtil.getMessage("generator.formSubmissionStatistics.msg.success", getClassName(), MESSAGE_PATH);
        msg = msg.replace("[url]", editLink);
         
        result.setMessage(msg);
    } catch (Exception e) {
        result.setError(true);
        result.setMessage(AppPluginUtil.getMessage("generator.formSubmissionStatistics.msg.error", getClassName(), MESSAGE_PATH));
        LogUtil.error(getClassName(), e, "Not able to generate the menus");
    }
     
    return result;
}
 
/**
 * Retrieves the category JSON definition
 * @param appDef
 * @return
 */
protected String getCategoryJson(AppDefinition appDef) {
    Collection<String> args = new ArrayList<String>();
     
    AppService appService = (AppService)AppUtil.getApplicationContext().getBean("appService");
    String tabelName = appService.getFormTableName(appDef, getFormId());
     
    args.add(UuidGenerator.getInstance().getUuid()); //category id
    args.add(StringUtil.escapeString(getPropertyString("categoryLabel"), StringUtil.TYPE_JSON, null)); //category label
    args.add(UuidGenerator.getInstance().getUuid()); //monthly menu uuid
    args.add(StringUtil.escapeString(getPropertyString("monthlyMenuId"), StringUtil.TYPE_JSON, null)); //monthly menu custom id
    args.add(StringUtil.escapeString(getPropertyString("monthlyMenuLabel"), StringUtil.TYPE_JSON, null)); //monthly menu label
    args.add(StringUtil.escapeString(getPropertyString("monthlyChartTitle"), StringUtil.TYPE_JSON, null)); //monthly chart title
    args.add(StringUtil.escapeString(getPropertyString("monthlyXAxisLabel"), StringUtil.TYPE_JSON, null)); //monthly x-axis label
    args.add(StringUtil.escapeString(getPropertyString("yAxisLabel"), StringUtil.TYPE_JSON, null)); //monthly y-axis label
    args.add(StringUtil.escapeString(tabelName, StringUtil.TYPE_JSON, null)); //monthly form table name
    args.add(StringUtil.escapeString(getPropertyString("yearLabel"), StringUtil.TYPE_JSON, null)); //monthly year label
    args.add(StringUtil.escapeString(getPropertyString("showLabel"), StringUtil.TYPE_JSON, null)); //monthly show button label
    args.add(UuidGenerator.getInstance().getUuid()); //daily menu uuid
    args.add(StringUtil.escapeString(getPropertyString("dailyMenuId"), StringUtil.TYPE_JSON, null)); //daily menu custom id
    args.add(StringUtil.escapeString(getPropertyString("dailyMenuLabel"), StringUtil.TYPE_JSON, null)); //daily menu label
    args.add(StringUtil.escapeString(getPropertyString("dailyChartTitle"), StringUtil.TYPE_JSON, null)); //daily chart title
    args.add(StringUtil.escapeString(getPropertyString("dailyXAxisLabel"), StringUtil.TYPE_JSON, null)); //daily x-axis label
    args.add(StringUtil.escapeString(getPropertyString("yAxisLabel"), StringUtil.TYPE_JSON, null)); //daily y-axis label
    args.add(StringUtil.escapeString(tabelName, StringUtil.TYPE_JSON, null)); //daily form table name
    args.add(StringUtil.escapeString(getPropertyString("yearLabel"), StringUtil.TYPE_JSON, null)); //daily year label
    args.add(StringUtil.escapeString(getPropertyString("monthLabel"), StringUtil.TYPE_JSON, null)); //daily month label
    args.add(StringUtil.escapeString(getPropertyString("showLabel"), StringUtil.TYPE_JSON, null)); //daily show button label
     
    String json = AppUtil.readPluginResource(getClass().getName(), "/resources/category.json", args.toArray(), true, null);
     
    return json;
}
 
/**
 * Checks for a userview is already exist
 * @param id
 * @param userviewDefinitionDao
 * @return
 */
protected boolean isExist(String id, UserviewDefinitionDao userviewDefinitionDao) {
    Long count = userviewDefinitionDao.count("AND id = ?", new String[]{id}, getAppDefinition());
    return count > 0;
}

In the getCategoryJson method, you will retrieve the category JSON definition form /resources/category.json file. Let's create a resources/resources directory under the form_submission_statistics_generator/src/main directory. After creating the directory, create a file named category.json in the resources" folder. Then, copy the category JSON definition you created previously and paste it inside this file. You will need to replace some of the hardcoded values as variables and remember to escape those existing % to %% as the AppUtil.readPluginResource using String.format to inject values into the file.

{
    "className": "org.joget.apps.userview.model.UserviewCategory",
    "properties": {
        "id": "category-%s",
        "label": "%s"
    },
    "menus": [{
        "className": "org.joget.plugin.enterprise.SqlChartMenu",
        "properties": {
            "id": "%s",
            "customId": "%s",
            "label": "%s",
            "chartType": "bar",
            "title": "%s",
            "categoryAxisLabel": "%s",
            "xAxisDisplayAS": "",
            "valueAxisLabel": "%s",
            "yaxisPrefix": "",
            "showLegend": "",
            "showValueLabel": "true",
            "stack": "",
            "horizontal": "",
            "chartWidth": "100%%",
            "chartHeight": "80%%",
            "colors": "",
            "query": "SELECT DATE_FORMAT(STR_TO_DATE(m.monthYear, '%%c-%%Y'),'%%b %%y') as monthYear, COUNT(fd.dateCreated) AS 'Number'\nFROM \n(\n    SELECT my.month, CONCAT(my.month, '-', IF(('#requestParam.year?sql#' REGEXP '^[0-9]{4}$'), '#requestParam.year?sql#' , '#date.yyyy?sql#')) AS monthYear FROM (\n        SELECT 1 AS month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12\n    ) my\n) m\nLEFT JOIN \n(\n\t  select dateCreated FROM app_fd_%s\n) fd\nON m.monthYear=DATE_FORMAT(fd.dateCreated,'%%c-%%Y')\nGROUP BY monthYear\nORDER BY m.month",
            "customHeader": "<div class=\"filter\">\n    <form action=\"?\" method=\"GET\">\n        <label>%s: <\/label><input name=\"year\" value=\"#requestParam.year?html#\"\/>\n        <input type=\"submit\" value=\"%s\"\/>\n    <\/form>\n<\/div>\n<script>\n    $(function(){\n        if ($(\"[name='year']\").val() === \"\") {\n            $(\"[name='year']\").val(\"#date.yyyy#\");\n        }\n    });\n<\/script>\n<br\/>\n<br\/>",
            "customFooter": "",
            "datasource": "default",
            "keyName": ""
        }
    }, {
        "className": "org.joget.plugin.enterprise.SqlChartMenu",
        "properties": {
            "id": "%s",
            "customId": "%s",
            "label": "%s",
            "chartType": "bar",
            "title": "%s",
            "categoryAxisLabel": "%s",
            "xAxisDisplayAS": "",
            "valueAxisLabel": "%s",
            "yaxisPrefix": "",
            "showLegend": "",
            "showValueLabel": "true",
            "stack": "",
            "horizontal": "true",
            "chartWidth": "100%%",
            "chartHeight": "80%%",
            "colors": "",
            "query": "SELECT d.date_field, COUNT(fd.dateCreated) AS 'Number'\nFROM\n(\n    SELECT\n        MAKEDATE(IF(('#requestParam.year?sql#' REGEXP '^[0-9]{4}$'), '#requestParam.year?sql#' , '#date.yyyy?sql#'),1) +\n        INTERVAL (IF(('#requestParam.month?sql#' REGEXP '^[0-9]{2}$'), '#requestParam.month?sql#' , '#date.MM?sql#') -1) MONTH +\n        INTERVAL daynum DAY date_field\n    FROM\n    (\n        SELECT t*10+u daynum\n        FROM\n            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,\n            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3\n            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7\n            UNION SELECT 8 UNION SELECT 9) B\n        ORDER BY daynum\n    ) AA\n) d\nLEFT JOIN \n(\n\t  select dateCreated FROM app_fd_%s\n) fd\nON d.date_field=DATE_FORMAT(fd.dateCreated,'%%Y-%%m-%%d')\nWHERE DATE_FORMAT(d.date_field,'%%m') = IF(('#requestParam.month?sql#' REGEXP '^[0-9]{2}$'), '#requestParam.month?sql#' , '#date.MM?sql#')\nGROUP BY d.date_field\nORDER BY d.date_field desc",
            "customHeader": "<div class=\"filter\">\n    <form action=\"?\" method=\"GET\">\n        <label>%s: <\/label><input name=\"year\" value=\"#requestParam.year?html#\"\/>&nbsp;&nbsp;&nbsp;\n        <label>%s: <\/label><select name=\"month\"\/>\n            <option value=\"01\">Jan<\/option>\n            <option value=\"02\">Feb<\/option>\n            <option value=\"03\">Mar<\/option>\n            <option value=\"04\">Apr<\/option>\n            <option value=\"05\">May<\/option>\n            <option value=\"06\">Jun<\/option>\n            <option value=\"07\">Jul<\/option>\n            <option value=\"08\">Aug<\/option>\n            <option value=\"09\">Sep<\/option>\n            <option value=\"10\">Oct<\/option>\n            <option value=\"11\">Nov<\/option>\n            <option value=\"12\">Dec<\/option>\n        <\/select>\n        <input type=\"submit\" value=\"%s\"\/>\n    <\/form>\n<\/div>\n<script>\n    $(function(){\n        if ($(\"[name='year']\").val() === \"\") {\n            $(\"[name='year']\").val(\"#date.yyyy#\");\n        }\n        if ($(\"[name='month']\").val() !== \"#requestParam.month?javascript#\" \n            && '#requestParam.month?javascript#' !== \"\"\n            && $(\"[name='month'] option[value='#requestParam.month?javascript#']\").length > 0 ) {\n            $(\"[name='month']\").val('#requestParam.month?javascript#');\n        } else {\n            $(\"[name='month']\").val(\"#date.MM#\");\n        }\n    });\n<\/script>\n<br\/>\n<br\/>",
            "customFooter": "",
            "datasource": "default",
            "keyName": ""
        }
    }]
}

c. Manage the dependency libraries of your plugin

Your plugin is using javax.servlet.http.HttpServletRequest and javax.servlet.http.HttpServletResponse class, so you will need to add the jsp-api library to your POM file. 

<!-- Change plugin specific dependencies here -->
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>jsp-api</artifactId>
    <version>2.0</version>
</dependency>
<!-- End change plugin specific dependencies here -->

d. Make your plugin internationalization (i18n) ready

You are using i18n message key in the getLabel and getDescription method. You also used the i18n message key in your properties options definition as Youll. So, you will need to create a message resource bundle properties file for your plugin.Create directory resources/messages under form_submission_statistics_generator/src/main directory. Then, create a FormSubmissionStatisticsGenerator.properties file in the folder. In the properties file, let's add all the message keys and their labels as below.

org.joget.tutorial.FormSubmissionStatisticsGenerator.pluginLabel=Generate Form Submission Statistics
org.joget.tutorial.FormSubmissionStatisticsGenerator.pluginDesc=Generate 2 SQL Chart Menus for form submission statistics
generator.formSubmissionStatistics.explanation=Create 2 SQL Chart Menus based on the current form for submission statistics. Note: This is dependent for MySQL database.
generator.formSubmissionStatistics.config=Options
generator.formSubmissionStatistics.userview=Userview
generator.formSubmissionStatistics.advanced=Advanced
generator.formSubmissionStatistics.label.options=Label Options
generator.formSubmissionStatistics.categoryLabel=Category Label
generator.formSubmissionStatistics.categoryLabel.value=[formName] Statistics
generator.formSubmissionStatistics.menuId.invalidId=Only alpha-numeric and underscore characters allowed
generator.formSubmissionStatistics.monthlyMenuId=Monthly Menu id
generator.formSubmissionStatistics.monthlyMenuLabel=Montly Menu Label
generator.formSubmissionStatistics.monthlyMenuLabel.value=Monthly Submission Chart
generator.formSubmissionStatistics.monthlyChartTitle=Montly Chart Title
generator.formSubmissionStatistics.monthlyChartTitle.value=[formName] Monthly Submission Chart
generator.formSubmissionStatistics.monthlyXAxisLabel=Montly X-axis Label
generator.formSubmissionStatistics.monthlyXAxisLabel.value=Month
generator.formSubmissionStatistics.dailyMenuId=Daily Menu Id
generator.formSubmissionStatistics.dailyMenuLabel=Daily Menu Label
generator.formSubmissionStatistics.dailyMenuLabel.value=Daily Submission Chart
generator.formSubmissionStatistics.dailyChartTitle=Daily Chart Title
generator.formSubmissionStatistics.dailyChartTitle.value=[formName] Daily Submission Chart
generator.formSubmissionStatistics.dailyXAxisLabel=Daily X-axis Label
generator.formSubmissionStatistics.dailyXAxisLabel.value=Date
generator.formSubmissionStatistics.yAxisLabel=Y-axis Label
generator.formSubmissionStatistics.yAxisLabel.value=Number
generator.formSubmissionStatistics.yearLabel=Year Label
generator.formSubmissionStatistics.yearLabel.value=Year
generator.formSubmissionStatistics.monthLabel=Month Label
generator.formSubmissionStatistics.monthLabel.value=Month
generator.formSubmissionStatistics.showLabel=Show Label
generator.formSubmissionStatistics.showLabel.value=Show
generator.formSubmissionStatistics.useriewMenu.options=Userview Label Options
generator.formSubmissionStatistics.createUserviewOptions=Create New Userview Options
generator.formSubmissionStatistics.userview.id=Userview ID
generator.formSubmissionStatistics.userview.id.value=v
generator.formSubmissionStatistics.userview.id.invalidId=Only alpha-numeric and underscore characters allowed
generator.formSubmissionStatistics.userview.name=Userview Name
generator.formSubmissionStatistics.userview.name.value=[appName]
generator.formSubmissionStatistics.userview.description=Userview Description
generator.formSubmissionStatistics.msg.success=Menus generated. Click <a href="[url]" target="_blank">here</a> to continue edit in Userview Builder.
generator.formSubmissionStatistics.msg.error=Error during generating Form Submission Statistics Menus!

e. Register your plugin to Felix Framework

You will have to register your plugin class in the Activator class (auto-generated in the same class package) to tell Felix Framework that this is a plugin.

public void start(BundleContext context) {
    registrationList = new ArrayList<ServiceRegistration>();
    //Register plugin here
    registrationList.add(context.registerService(FormSubmissionStatisticsGenerator.class.getName(), new FormSubmissionStatisticsGenerator(), null));
}

f. Build it and testing

Let's build your plugin. Once the building process is done, you will find a form_submission_statistics_generator-5.0.0.jar file created under the form_submission_statistics_generator/target directory. 

Then, let's upload the plugin jar to Manage Plugins. After uploading the jar file, double-check if the plugin is uploaded and activated correctly. 

Now, you can open one for your form in Form Builder for testing the generator. Click the Generate App button on the top right of the Form Builder. Please refer to Generate App. 

Let's check the properties page for Advanced Generator. 

The image below shows what happens after the generation process is completed. 

A new category has been added to your userview. 

The SQL chart menus display as follows. 

8. Take a step further, share it or sell it

 You can download the source code from form_submission_statistics_generator.zip.

Created by Aadrian Last modified by Aadrian on Mar 12, 2025
Tags:
Previous How to develop a File Link Datalist Formatter NextHow to develop a Gantt Chart UI Menu