Questetra BPMS: 案件, 指定データTSV一括抽出 (Questetra BPMS: Process, Batch Extract as TSV)
日付フィルタされた複数の案件データを複数行のTSV文字列として抽出します。抽出項目は “string:0,date:2,select:3″ のようなCSV書式で指定します。Tabコードと改行コードは、自動的に削除されます。”ファイル型” はファイル名が、”選択型” は選択された選択肢の表示テキストが抽出されます。掲示板型・テーブル型・ガイドパネル型は抽出できません(Warningがログ出力されます)
2019-11-20 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/ja/addons/questetra-bpms-process-batch-extract-as-tsv/
Configs
- A: 通信許可の設定名([OAuth2.0設定]メニュー)を選択してください *
- B: ワークフロー基盤URLをセットしてください(${var[applicationRoot]} or https://../) * #{EL}
- C: ワークフローアプリのIDをセットしてください ( 例 “123” ) * #{EL}
- C-target: 日付型データ項目のIDをセットしてください ( 例 “5” ) #{EL}
- C-from: フィルタ範囲の開始日をセットしてください ( 例 “2019-12-01” ) #{EL}
- C-to: フィルタ範囲の終了日をセットしてください ( 例 “2019-12-31” ) #{EL}
- C-sort: 降順(DESC)もしくは昇順(ASC)をセットしてください ( デフォルト “DESC” ) #{EL}
- D: 抽出するデータ項目のIDをセットしてください ( 例 “string:0,date:2,select:3” ) * #{EL}
- E: 抽出されたTSV文字列が格納される文字列型データを選択してください (更新)
- F: 抽出されたTSV文字列が格納される文字列型データを選択してください (追記)
Script
// 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 oauth2 = configs.get( "conf_OAuth2" ) + ""; // required
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 = engine.findDataByNumber( dataIdF );
//// == Calculating / 演算 ==
// obtain Access Token
const token = httpClient.getOAuth2Token( oauth2 );
// 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.bearer( token );
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 !== null ){
engine.setDataByNumber( dataIdF, strBeforeAppend + "\n" + strNewline );
}else{
engine.setDataByNumber( dataIdF, strNewline );
}
}
} //////// END "main()" ////////

Download
Capture
Notes
- “タイマー開始イベント” と併用すれば、週次や月次の集計業務・レポート業務を自動化できます
- 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ターゲットとなります
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,
.....
}]
}
See also
Related