Form to search for cases

From pmusers
Jump to: navigation, search

A Dynaform to search for cases, then select one case from a dropdown box and display information about that case below in text boxes.


Dynaform: Search for case.json (right click and select Save Link As)
Author: Amos Batto <amos[en]processmaker[dot]com>
Version: 1.0 (2019-04-09)
Tested in: PM 3.3.6 Community


Create the following Dynaform:

SearchCasesInDesigner.png

This form has the following fields:

  • Dropdown with ID "selectProcess" which is populated with the sql:
    SELECT PRO_UID, PRO_TITLE FROM PROCESS
    
  • Dropdown with ID "selectTask" which is a dependent field populated with the sql:
    SELECT TAS_UID, TAS_TITLE FROM TASK WHERE PRO_UID=@@selectProcess
    
  • Dropdown with ID "caseStatus" which is populated with the following options:
    OptionsSelectStatus.png
  • Dropdown with ID "assignedUser" which is populated with the sql:
    SELECT USR_UID, CONCAT(USR_FIRSTNAME, ' ', USR_LASTNAME, ' (', USR_USERNAME, ')') FROM USERS
    
  • Datetime with ID "fromDate"
  • Datetime with ID "toDate"
  • Text with ID "selectText"
  • Dropdown with ID "selectCase" which is a dependent field populated with the sql:
    SELECT CONCAT(APP_UID, '|', MAX(DEL_INDEX)), 
      CONCAT(APP_NUMBER, '|', APP_TITLE, '|', APP_PRO_TITLE, '|', APP_TAS_TITLE, '|', APP_CURRENT_USER, '|', APP_STATUS)
      FROM APP_CACHE_VIEW @#caseQueryString GROUP BY APP_NUMBER LIMIT 100;
    
  • Text with ID "caseTitle" which is a dependent field populated with the sql:
    SELECT APP_TITLE, APP_TITLE FROM APP_CACHE_VIEW WHERE APP_UID=SUBSTRING(@@selectCase, 1, 32) AND 
      DEL_INDEX=CAST(SUBSTRING(@@selectCase, 34) AS UNSIGNED INTEGER)
    
  • Text with ID "caseCreated" which is a dependent field populated with the sql:
    SELECT APP_CREATE_DATE, APP_CREATE_DATE FROM APP_CACHE_VIEW 
      WHERE APP_UID=SUBSTRING(@@selectCase, 1, 32) AND 
      DEL_INDEX=CAST(SUBSTRING(@@selectCase, 34) AS UNSIGNED INTEGER)
    
  • Text with ID "caseUpdated" which is a dependent field populated with the sql:
    SELECT APP_UPDATE_DATE, APP_UPDATE_DATE FROM APP_CACHE_VIEW 
      WHERE APP_UID=SUBSTRING(@@selectCase, 1, 32) AND 
      DEL_INDEX=CAST(SUBSTRING(@@selectCase, 34) AS UNSIGNED INTEGER)
    
  • Textarea with ID "caseQueryString" which is hidden with JavaScript and is the independent field used by "selectCase" to hold the WHERE clause in its SQL query.


Then, add the following JavaScript to the Dynaform:

$("#caseQueryString").hide(); //comment out to debug

var formId = $("form").prop("id");
var aQueryFields = ["selectProcess", "selectTask", "caseStatus", "assignedUser", "fromDate", "toDate", "searchText"];

//function to escape strings for database queries 
function _escape(val) {
  val = val.replace(/[\0\n\r\b\t\\'"\x1a]/g, function (s) {
    switch (s) {
      case "\0":
        return "\\0";
      case "\n":
        return "\\n";
      case "\r":
        return "\\r";
      case "\b":
        return "\\b";
      case "\t":
        return "\\t";
      case "\x1a":
        return "\\Z";
      case "'":
        return "''";
      case '"':
        return '""';
      default:
        return "\\" + s;
    }
  });

  return val;
}

//function to construct the SQL query to find cases in the APP_CACHE_VIEW table:
function setCaseQuery() {
    var where   = '';
    var processId  = $("#selectProcess").getValue();
    var taskId     = $("#selectTask").getValue();
    var caseStatus = $("#caseStatus").getValue();
    var userId     = $("#assignedUser").getValue();
    var fromDate   = _escape( $("#fromDate").getValue() );
    var toDate     = _escape( $("#toDate").getValue() );
    var searchText = _escape( $("#searchText").getValue() );
    
    if (processId) {
      where = "PRO_UID='"+processId+"'";
    }
    
    if (taskId) {
      where += (where ? ' AND ' : '') + "TAS_UID='"+taskId+"'";
    }

    if (caseStatus) {
      where += (where ? ' AND ' : '') + "APP_STATUS='"+caseStatus+"'";
    }

    if (userId) {
      where += (where ? ' AND ' : '') + "USR_UID='"+userId+"'";
    }
    
    if (fromDate) {
      where += (where ? ' AND ' : '') + "DATE(DEL_DELEGATE_DATE) >= DATE('"+fromDate+"')";
    }

    if (toDate) {
      where += (where ? ' AND ' : '') + "DATE(IFNULL(DEL_FINISH_DATE, CURDATE())) <= DATE('"+toDate+"')";
    }

    if (searchText) {
      var searchNo = parseInt(searchText);
      txtQuery = '';
      
      if (isNaN(searchNo) === false) {
        txtQuery = "APP_NUMBER="+searchNo;
      }
      
      where += (where ? ' AND ' : '') + "(" + (txtQuery ? ' OR ' : '') +
        "APP_TITLE LIKE '%"+searchText+"%' OR APP_CURRENT_USER LIKE '%"+searchText+"%' OR "+
        "APP_PRO_TITLE LIKE '%"+searchText+"%' OR APP_TAS_TITLE LIKE '%"+searchText+"%')";
    }
    
    if (where) {
      where = "WHERE " + where;
    }
    
    $("#caseQueryString").setValue(where);
}

//if the changed field is one of the aQueryFields, then set the SQL search string
//in the "caseQueryString" field, so the "selectCase" dropdown will be populated 
//as a dependent field
$("#"+formId).setOnchange( function(fieldId, newVal, oldVal) {
  
  if (aQueryFields.indexOf(fieldId) != -1) {
    setCaseQuery();
  } 
}); 

setCaseQuery(); //execute when the Dynaform loads


When the Dynaform is first displayed it will find all cases:

SelectCaseNoSearchCriteria.png

Search criteria can be entered to find a particular case:

SearchCasesWithSearchCriteria.png