2022-01-27 / Add-ons / ,
Questetra BPMS: Process, Batch Extract as TSV

PAGE UPDATED

Questetra BPMS: Process, Batch Extract as TSV
Extracts process data as a multi-line TSV string from date-filtered processes. Items are specified in CSV format such as “string:0,date:2,select:3”. Tab code and line feed code are deleted. Discussion, Table, Guide-Panel type not supported.
Configs
  • A: Select HTTP_Authz Setting *
  • B: Set WF Platform URL (${var[applicationRoot]} or https: //../) *#{EL}
  • C: Set Workflow-App ID ( e.g., “123” ) *#{EL}
  • C-target: Set Date-Type DataItem ID ( e.g., “5” )#{EL}
  • C-from: Set Filter Range FROM ( e.g., “2019-12-01” )#{EL}
  • C-to: Set Filter Range TO ( e.g., “2019-12-31” )#{EL}
  • C-sort: Set DESC or ASC ( default “DESC” )#{EL}
  • D: Set DataItem IDs to Extract ( eg “string:0,date:2,select:3” ) *#{EL}
  • E: Select STRING DATA for Extracted TSV (update)
  • F: Select STRING DATA for Extracted TSV (append)
Script (click to open)
// GraalJS Script (engine type: 2)
// about Questetra Workflow API
// See https://online-demo-ja.questetra.net/s/swagger/index.html?urls.primaryName=Workflow%20API
// or "https://{YOUR}.questetra.net/s/swagger/index.html"

// about Questetra OAuth2 (for System Admin)
// Get ID&Secret: System Settings > API Clients > OAuth2 Clients > Add OAuthw Client
// (config "Redirect URL" = "https://s.questetra.net/oauth2callback" )
// (Scope: "read" or "any" )
// Set ID&Secret: Workflow App > Detail > OAuth2 Setting > (Get Refresh Token)

// Notes:
// Weekly and monthly reporting works can be automated with "Timer Start Event".
// Tab code and line feed code in Workflow data will be deleted.
// Extraction data is specified by "{type}:{itemid}" in CSV notation (eg "string:0,date:2,select:3")
// Data Type Notations: string decimal date datetime select file quser qgroup
// Specify "Title" to extract Title and "Id" to extract ID. ( e.g. "Title,Id,string:0,date:2,select:3" )
// If an undefined dataitem ID or Guide Panel ID is specified, "#N/A" is output.
// If an unsupported type is specified, "#TYPE!" is output.
// The file names are extracted for the File type. (Multiple: Space-separated)
// The display strings of the selected options are extracted for the Select type. (Multiple: Space-separated)
// If "C-target: Date-Type DataItem" is not specified, "processInstanceStartDatetime" will be the Filter target instead.
// Notes (ja):
// "タイマー開始イベント" と併用すれば、週次や月次の集計業務・レポート業務を自動化できます
// Workflowデータ内のタブコードと改行コードは抽出時に削除されます
// 抽出データの指定は "{type}:{itemid}" をCSV表記で行います ( 例 "string:0,date:2,select:3" )
// データ型の表記方法： string decimal date datetime select file quser qgroup
// 案件タイトルの抽出には "Title" を、IDの抽出には "Id" を指定します ( 例 "Title,Id,string:0,date:2,select:3" )
// 未定義のデータ項目IDやガイドパネルのIDが指定された場合は、"#N/A" が出力されます
// サポート外のデータ型が指定された場合は、"#TYPE!" が出力されます
// ファイル型を指定した場合、ファイル名が抽出されます（複数：スペース区切り）
// 選択型を指定した場合、表示テキストが抽出されます（複数：スペース区切り）
// "C-target: 日付型データ項目" が未指定の場合、"プロセス開始日時" がFilterターゲットとなります


//////// START "main()" ////////
main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const strAuthzSetting = configs.get( "AuthzConfA" );                // required
  engine.log( " AutomatedTask Config: Authz Setting: " + strAuthzSetting );
const applicationRoot = configs.get( "conf_ApplicationRoot" ) + ""; // required
const processModelId  = configs.get( "conf_ProcessModelId" ) + "";  // required
const targetDateId    = configs.get( "conf_TargetDateId" ) + "";    // not required
const targetFrom      = configs.get( "conf_TargetFrom" ) + "";      // not required
const targetTo        = configs.get( "conf_TargetTo" ) + "";        // not required
const targetSort      = configs.get( "conf_TargetSort" ) + "";      // not required
const dataitemIds     = configs.get( "conf_DataitemIds" ) + "";     // required
const dataIdE         = configs.get( "conf_DataIdE" )   + "";       // not required
const dataIdF         = configs.get( "conf_DataIdF" )   + "";       // not required
// 'java.lang.String' (String Obj) to javascript primitive 'string'

engine.log( " AutomatedTask Config: Application Root: " + applicationRoot );
engine.log( " AutomatedTask Config: Workflow App ID: " + processModelId );
engine.log( " AutomatedTask Config: Target Dataitem ID: " + targetDateId );
engine.log( " AutomatedTask Config: Filter Range From: " + targetFrom );
engine.log( " AutomatedTask Config: Filter Range To: " + targetTo );
engine.log( " AutomatedTask Config: Sort Order: " + targetSort );
engine.log( " AutomatedTask Config: Dataitem IDs: " + dataitemIds );

const yyyyMMddReg = /^\d{4}-\d{2}-\d{2}$/;
if( targetFrom !== "" ){
  if( ! yyyyMMddReg.test( targetFrom ) ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {Target From} is not YYYY-MM-DD \n" );
  }
}
if( targetTo !== "" ){
  if( ! yyyyMMddReg.test( targetTo ) ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {Target To} is not YYYY-MM-DD \n" );
  }
}


//// == Data Retrieving / ワークフローデータの参照 ==
let strBeforeAppend = "";
if( dataIdF !== "" ){
  strBeforeAppend = engine.findDataByNumber( dataIdF ) + "";
}

//// == Calculating / 演算 ==
// define Filter Criteria (for Questetra ProcessInstance Search)
let criteria = "<process-instance-criteria>";
criteria    += "<process-model-info-id>" + processModelId +"</process-model-info-id>";
if( targetDateId === "" ){
  if( targetFrom === "" ){
    criteria  += "<start-date-from>#today.addDays(-7)</start-date-from>";
  }else{
    criteria  += "<start-date-from>" + targetFrom + "</start-date-from>";
  }
  if( targetTo === "" ){
    criteria  += "<start-date-to>#today</start-date-to>";
  }else{
    criteria  += "<start-date-to>" + targetTo + "</start-date-to>";
  }
}
criteria  += "<data>";
if( targetDateId !== "" ){
  criteria  += "<date>";
  criteria  += "<data-definition-number>" + targetDateId + "</data-definition-number>";
  if( targetFrom === "" ){
    criteria  += "<after-equals /><value>#today.addDays(-7)</value>";
  }else{
    criteria  += "<after-equals /><value>" + targetFrom + "</value>";
  }
  criteria  += "</date><date>";
  criteria  += "<data-definition-number>" + targetDateId + "</data-definition-number>";
  if( targetTo === "" ){
    criteria  += "<before-equals /><value>#today</value>";
  }else{
    criteria  += "<before-equals /><value>" + targetTo + "</value>";
  }
  criteria  += "</date>";
}
const arrDataitemIds = dataitemIds.split(","); // e.g. "Title,Id,string:0,date:2,select:3"
for( let i = 0; i < arrDataitemIds.length; i++ ){
  if( arrDataitemIds[i] === "Title" ){
  }else if( arrDataitemIds[i] === "Id" ){
  }else{
    const arrViewTypeId = arrDataitemIds[i].split(":");
    criteria  += "<" + arrViewTypeId[0] + ">";  // e.g. "<string>"
    criteria  += "<data-definition-number>" + arrViewTypeId[1] + "</data-definition-number>";
    criteria  += "<view />";
    criteria  += "</" + arrViewTypeId[0] + ">";  // e.g. "</string>"
  }
}
criteria  += "</data>";
if( targetSort === "ASC" ){
  criteria  += "<sort-direction>ASC</sort-direction>"; 
}
criteria    += "</process-instance-criteria>";
engine.log( " AutomatedTask Criteria: \n" + criteria );

// get ProcessInstance via Questetra Workflow API
// com.questetra.bpms.core.event.scripttask.HttpClientWrapper
let apiRequest = httpClient.begin(); // HttpRequestWrapper
    apiRequest = apiRequest.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
    // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper
    apiRequest = apiRequest.queryParam( "limit", "1000" );
    apiRequest = apiRequest.queryParam( "criteria", criteria );
const apiUri = applicationRoot + "API/OR/ProcessInstance/list";

// request to API
engine.log( " AutomatedTask Trying: GET " + apiUri );
const response = apiRequest.get( apiUri );
const responseCode = response.getStatusCode() + "";
engine.log( " AutomatedTask ApiResponse: Status " + responseCode );
if( responseCode !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
         responseCode + "\n" + response.getResponseAsString() + "\n" );
}
const responseStr = response.getResponseAsString() + "";
const responseObj = JSON.parse( responseStr );
//engine.log( responseStr ); //test

// retrieve JSON
engine.log( " AutomatedTask Parsed: Number of Process Instances: " + responseObj.count );
let strNewline = "";
for( let h = 0; h < responseObj.processInstances.length; h++ ){
  for( let i = 0; i < arrDataitemIds.length; i++ ){
    if( arrDataitemIds[i] === "Id" ){
      strNewline += responseObj.processInstances[h].processInstanceId;
    }else if( arrDataitemIds[i] === "Title" ){
      if( responseObj.processInstances[h].processInstanceTitle !== null ){
        strNewline += responseObj.processInstances[h].processInstanceTitle;
      }
    }else{
      const dataId = arrDataitemIds[i].replace( /(\w+):(\w+)/, '$2' );
      if( responseObj.processInstances[h].data[ dataId ] == undefined ){
        strNewline += "#N/A";
      }else if( responseObj.processInstances[h].data[ dataId ].dataType === "STRING" || 
                responseObj.processInstances[h].data[ dataId ].dataType === "DECIMAL" || 
                responseObj.processInstances[h].data[ dataId ].dataType === "DATE" || 
                responseObj.processInstances[h].data[ dataId ].dataType === "DATETIME" || 
                responseObj.processInstances[h].data[ dataId ].dataType === "QUSER" || 
                responseObj.processInstances[h].data[ dataId ].dataType === "QGROUP" ){
        if( responseObj.processInstances[h].data[ dataId ].value !== null ){
          strNewline += responseObj.processInstances[h].data[ dataId ].value
                        .replace( /\n/g, '' ).replace( /\t/g, '' );
        }
      }else if( responseObj.processInstances[h].data[ dataId ].dataType === "SELECT" ){
        if( responseObj.processInstances[h].data[ dataId ].value !== null ){
          for( let j = 0; j < responseObj.processInstances[h].data[ dataId ].value.length; j++ ){
            strNewline += responseObj.processInstances[h].data[ dataId ].value[j].display
                          .replace( /\n/g, '' ).replace( /\t/g, '' );
            if( j !== responseObj.processInstances[h].data[ dataId ].value.length - 1 ){
              strNewline += " "; // (Multiple: Space-separated)
            }
          }
        }
      }else if( responseObj.processInstances[h].data[ dataId ].dataType === "FILE2" ){
        if( responseObj.processInstances[h].data[ dataId ].value !== null ){
          for( let j = 0; j < responseObj.processInstances[h].data[ dataId ].value.length; j++ ){
            strNewline += responseObj.processInstances[h].data[ dataId ].value[j].name
                          .replace( /\n/g, '' ).replace( /\t/g, '' );
            if( j !== responseObj.processInstances[h].data[ dataId ].value.length - 1 ){
              strNewline += " "; // (Multiple: Space-separated)
            }
          }
        }
      }else{
        strNewline += "#TYPE!";
      }
    }
    if( i !== arrDataitemIds.length - 1 ){
      strNewline += "\t";
    }
  }
  if( h !== responseObj.processInstances.length - 1 ){
    strNewline += "\n";
  }
}


//// == Data Updating / ワークフローデータへの代入 ==
if( strNewline === "" ){
  engine.log( " AutomatedTask DataUpdating: Extracted Text empty" );
}
if( dataIdE !== "" ){
  engine.setDataByNumber( dataIdE, strNewline );
}
if( dataIdF !== "" ){
  if( strBeforeAppend !== "" ){
    engine.setDataByNumber( dataIdF, strBeforeAppend + "\n" + strNewline );
  }else{
    engine.setDataByNumber( dataIdF, strNewline );
  }
}

} //////// END "main()" ////////

Download

2021-07-01 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/questetra-bpms-process-batch-extract-as-tsv-2021/
The Addon-import feature is available with Professional edition.

Notes

  1. Weekly and monthly reporting works can be automated with “Timer Start Event”.
  2. Tab code and line feed code in Workflow data will be deleted.
  3. Extraction data is specified by “{type}:{itemid}” in CSV notation (eg “string:0,date:2,select:3”)
  4. Data Type Notations: string decimal date datetime select file quser qgroup
  5. Specify “Title” to extract Title and “Id” to extract ID. ( e.g. “Title,Id,string:0,date:2,select:3” )
  6. If an undefined dataitem ID or Guide Panel ID is specified, “#N/A” is output.
  7. If an unsupported type is specified, “#TYPE!” is output.
  8. The file names are extracted for the File type. (Multiple: Space-separated)
  9. The display strings of the selected options are extracted for the Select type. (Multiple: Space-separated)
  10. If “C-target: Date-Type DataItem” is not specified, “processInstanceStartDatetime” will be the Filter target instead.

Spec Details

Criteria Example

<process-instance-criteria>
  <process-model-info-id>1439</process-model-info-id>
  <data>
    <date>
      <data-definition-number>3</data-definition-number>
      <after-equals /><value>2019-11-01</value>
    </date>
    <date>
      <data-definition-number>3</data-definition-number>
      <before-equals /><value>2019-11-02</value>
    </date>
    <quser>
      <data-definition-number>2</data-definition-number>
      <view />
    </quser>
    <select>
      <data-definition-number>4</data-definition-number>
      <view />
    </select>
    <datetime>
      <data-definition-number>6</data-definition-number>
      <view />
    </datetime>
  </data>
</process-instance-criteria>

Reference: Search Criteria XML for Process Instances

Response Example

{
  "count":8,
  "processInstances":[{
    "activeTokenNodeName":null,
    "data":{
      "2":{
        "dataType":"QUSER",
        "id":33591338,
        "processDataDefinitionNumber":2,
        "subType":null,
        "value":"SUZUKI Ichiro <suzuki@questetra.com>",
        "viewOrder":3
      },
      "3":{
        "dataType":"DATE",
        "id":33591339,
        "processDataDefinitionNumber":3,
        "subType":"DATE_YMD",
        "value":"2019-11-01",
        "viewOrder":4
      },
      "4":{
        "dataType":"SELECT",
        "id":33591340,
        "processDataDefinitionNumber":4,
        "subType":null,
        "value":[{"display":"勤務時間確認済","value":"勤務時間確認済"}],
        "viewOrder":5
      },
      "6":{
        "dataType":"DATETIME",
        "id":33591342,
        "processDataDefinitionNumber":6,
        "subType":null,
        "value":"2019-11-01 08:39",
        "viewOrder":7
      }
    },
    "processInstanceDebug":false,
    "processInstanceEndDatetime":"2019-11-02T14:09:02",
    "processInstanceId":998525,
    "processInstanceIdForView":"p998525",
    "processInstanceInitQgroupId":1,
    "processInstanceInitQgroupName":"全社",
    "processInstanceInitQuserId":14,
    "processInstanceInitQuserName":"SUZUKI Ichiro",
    "processInstanceSequenceNumber":8990,
    "processInstanceStartDatetime":"2019-11-01T07:00:00+0900",
    "processInstanceState":"ENDED",
    "processInstanceTitle":"2019-11-01(金): SUZUKI Ichiro",
    "processModelInfoCategory":"2-管理部",
    "processModelInfoId":1439,
    "processModelInfoName":"242-出退勤報告フロー",
    "processModelVersion":19
  },
  {
    "activeTokenNodeName":null,
    "data":{
      "2":{
        "dataType":"QUSER",
        "id":33591323,
        "processDataDefinitionNumber":2,
        .....
      }
    }
    .....
    "processInstanceId":998524,
    .....
  }]
}

Capture

Extracts process data as a multi-line TSV string from date-filtered processes. Items are specified in CSV format such as "string:0,date:2,select:3". Tab code and line feed code are deleted. Discussion, Table, Guide-Panel type not supported.

See also

%d