Questetra BPMS #Cases: Extract by Report ID

// Script for 'engine type: 3' ("GraalJS standard mode")
// cf 'engine type: 2' renamed from "GraalJS" to "GraalJS Nashorn compatible mode" (at 20230526)


//////// START "main()" /////////////////////////////////////////////////////////////////

main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const objAuthzSetting     = configs.getObject( "AuthzConfU" );   /// REQUIRED ///////////
  engine.log( " AutomatedTask Config: Authz Setting: " + objAuthzSetting.getName() );
const strTargetUrl        = configs.get      ( "StrConfA1" );    /// REQUIRED ///////////
  if( strTargetUrl      === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A1: TargetPlatformURL} is empty \n" );
  }
  if( ! strTargetUrl.endsWith( "/" ) ){
    strTargetUrl += "/";
  }
const strReportId         = configs.get      ( "StrConfA2" );    /// REQUIRED ///////////
  if( strReportId       === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A2: ReportID} is empty \n" );
  }
let   strLimit            = configs.get      ( "StrConfA3" );    // NotRequired /////////
  if( strLimit          === "" ){
      strLimit = "1000";
  }else if( isNaN( parseInt(strLimit) ) || parseInt(strLimit) < 0 ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A3: Max Num of Extraction} must be a positive integer \n" );
  }else if( parseInt(strLimit) > 1000 ){
    engine.log( " AutomatedTask ConfigWarning: " +
                " {A3: Max Num of Extraction} is limited to 1000" );
    strLimit = "1000";
  }
const strColIds           = configs.get      ( "StrConfB0" );    /// REQUIRED ///////////
const strPocketTsv        = configs.getObject( "SelectConfB1" ); /// REQUIRED ///////////
const numPocketExtracted  = configs.getObject( "SelectConfB2" ); // NotRequired /////////
const numPocketFiltered   = configs.getObject( "SelectConfB3" ); // NotRequired /////////



//// == Data Retrieving / ワークフローデータの参照 ==
// (Nothing. Some workflow data is referenced via Expression Language in Config.)



//// == Calculating / 演算 ==
/// Get Processes
/// Questetra Workflow API
/// https://questetra.zendesk.com/hc/en-us/articles/360014835832-M418
/// https://questetra.zendesk.com/hc/ja/articles/360014835832-M418
// request1, prepare
let request1Uri = strTargetUrl + "API/OR/ProcessInstance/list";
let request1    = httpClient.begin(); // HttpRequestWrapper
    request1    = request1.authSetting( objAuthzSetting ); // with "Authorization: Bearer XX"
    request1    = request1.queryParam( "reportId", strReportId );
    request1    = request1.queryParam( "limit", strLimit );
    // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper
// request1, try
const response1     = request1.get( request1Uri ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest1 Start: " + request1Uri );
const response1Code = response1.getStatusCode() + "";
const response1Body = response1.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response1Code );
if( response1Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response1Code + "\n" + response1Body + "\n" );
}
// response1, parse
/* 

{
  "count": 120,
  "processInstances": [
    {
      "activeTokenNodeName": [
        "シグナル待機",
        "停止依頼待機"
      ],
      "data": {
        "0": {
          "dataType": "SELECT",
          "formType": "SELECT",
          "id": 144747204,
          "processDataDefinitionNumber": 0,
          "subType": null,
          "value": [
            {
              "display": "Professional",
              "value": "PROFESSIONAL"
            }
          ],
          "viewOrder": 8
        },
        "25": {
          "dataType": "STRING",
          "formType": "TEXTFIELD",
          "id": 144747206,
          "processDataDefinitionNumber": 25,
          "subType": null,
          "value": "株式会社四次元データ",
          "viewOrder": 11
        },
        "3": {
          "dataType": "STRING",
          "formType": "TEXTFIELD",
          "id": 144747207,
          "processDataDefinitionNumber": 3,
          "subType": null,
          "value": "suzuki@example.net",
          "viewOrder": 9
        },
      },
      "parentProcessInstanceId": null,
      "processInstanceDebug": false,
      "processInstanceEndDatetime": null,
      "processInstanceId": 2974547,
      "processInstanceIdForView": "p2974547",
      "processInstanceInitQgroupId": null,
      "processInstanceInitQgroupName": null,
      "processInstanceInitQuserId": null,
      "processInstanceInitQuserName": null,
      "processInstanceSequenceNumber": 30,
      "processInstanceStartDatetime": "2026-05-27T11:25:34+0900",
      "processInstanceState": "STARTED",
      "processInstanceTitle": "株式会社四次元データ",
      "processModelId": 144353892,
      "processModelInfoCategory": "6-マーケティング部",
      "processModelInfoId": 2078,
      "processModelInfoName": "611-問合対応プロセス",
      "processModelVersion": 222,
      "starred": false
    },
    {
      ...
    },
    {
      ...
    }
  ]
}
*/

const response1Obj = JSON.parse( response1Body );
const arrInstances = response1Obj.processInstances || [];

engine.log( " AutomatedTask Parsed: Number of Received Instances: " + response1Obj.processInstances.length );
engine.log( " AutomatedTask Parsed: Number of Total Instances: "    + response1Obj.count );

const arrColIds = parseColumnIds( strColIds );
const strTsv    = buildTsv( arrInstances, arrColIds );


//// == Data Updating / ワークフローデータへの代入 ==
engine.setData(   strPocketTsv,       strTsv );
if(               numPocketExtracted  !== null ){
  engine.setData( numPocketExtracted,  new java.math.BigDecimal( arrInstances.length ) );
}
if(               numPocketFiltered   !== null ){
  engine.setData( numPocketFiltered,   new java.math.BigDecimal( response1Obj.count ) );
}


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


//// == Function Definitions / 関数定義 ================================================

/**
 * Parses B0 CSV.
 * B0 is required and accepts Data Item IDs and the process-property names listed in the notes.
 */
function parseColumnIds( strColIds ){
  if( strColIds === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B0: TSV Column Order} is empty \n" );
  }

  const arrColIds = strColIds.split( "," ).map(function( strColId ){
    return strColId.trim();
  });

  for( let i = 0; i < arrColIds.length; i++ ){
    if( arrColIds[i] === "" ){
      throw new Error( "\n AutomatedTask ConfigError:" +
                       " Config {B0: TSV Column Order} contains an empty column ID \n" );
    }
  }
  return arrColIds;
}

/**
 * Builds TSV lines according to the B0 column order.
 */
function buildTsv( arrInstances, arrColIds ){
  const arrLines = [];
  for( let i = 0; i < arrInstances.length; i++ ){
    const objInstance = arrInstances[i];
    const arrCells    = [];
    for( let j = 0; j < arrColIds.length; j++ ){
      arrCells.push( stringifyColumn( objInstance, arrColIds[j] ) );
    }
    arrLines.push( arrCells.join( "\t" ) );
  }
  return arrLines.join( "\n" );
}

function stringifyColumn( objInstance, strColId ){
  if( isProcessPropertyName( strColId ) ){
    return sanitizeTsvCell( objInstance[strColId] );
  }

  const objDataItems = objInstance.data || {};
  if( objDataItems[strColId] === undefined ){
    return "";
  }
  return stringifyDataItem( objDataItems[strColId] );
}

function stringifyDataItem( objDataItem ){
  if( objDataItem === null || objDataItem === undefined ){
    return "";
  }
  if( objDataItem.value === null || objDataItem.value === undefined ){
    return "";
  }

  const strDataType = objDataItem.dataType + "";
  const value       = objDataItem.value;

  if( strDataType === "SELECT" ){
    return sanitizeTsvCell( joinValuesByProperty( value, "display" ) );
  }
  if( strDataType === "FILE" || strDataType === "FILE2" ){
    return sanitizeTsvCell( joinValuesByProperty( value, "name" ) );
  }
  if( strDataType === "QUSER" ){
    return sanitizeTsvCell( stringifyQuserOrQgroupValue( value ) );
  }
  if( strDataType === "QGROUP" ){
    return sanitizeTsvCell( stringifyQuserOrQgroupValue( value ) );
  }
  if( strDataType === "STRING" ||
      strDataType === "DECIMAL" ||
      strDataType === "DATE" ||
      strDataType === "DATETIME" ||
      strDataType === "DISCUSSION" ){
    return sanitizeTsvCell( value );
  }

  // Table type data and other unsupported data types are not extracted.
  return "N/A";
}

function joinValuesByProperty( value, strPropertyName ){
  if( ! Array.isArray( value ) ){
    if( typeof value === "object" && value !== null ){
      return value[strPropertyName] || "";
    }
    return value;
  }

  const arrValues = [];
  for( let i = 0; i < value.length; i++ ){
    if( value[i] !== null && value[i] !== undefined ){
      if( typeof value[i] === "object" ){
        arrValues.push( value[i][strPropertyName] || "" );
      }else{
        arrValues.push( value[i] );
      }
    }
  }
  return arrValues.join( " " );
}

function stringifyQuserOrQgroupValue( value ){
  if( Array.isArray( value ) ){
    const arrValues = [];
    for( let i = 0; i < value.length; i++ ){
      arrValues.push( stringifyQuserOrQgroupValue( value[i] ) );
    }
    return arrValues.join( " " );
  }
  if( typeof value !== "object" || value === null ){
    return value;
  }

  if( value.name    !== undefined && value.name    !== null ){ return value.name;    }
  if( value.display !== undefined && value.display !== null ){ return value.display; }
  if( value.qname   !== undefined && value.qname   !== null ){ return value.qname;   }
  if( value.email   !== undefined && value.email   !== null ){ return value.email;   }

  return JSON.stringify( value );
}

function sanitizeTsvCell( value ){
  if( value === null || value === undefined ){
    return "";
  }
  return ( value + "" ).replace( /\r?\n/g, " " ).replace( /\t/g, " " );
}

function isProcessPropertyName( strColId ){
  const objProcessProperties = {
    "parentProcessInstanceId": true,
    "processInstanceDebug": true,
    "processInstanceEndDatetime": true,
    "processInstanceId": true,
    "processInstanceIdForView": true,
    "processInstanceInitQgroupId": true,
    "processInstanceInitQgroupName": true,
    "processInstanceInitQuserId": true,
    "processInstanceInitQuserName": true,
    "processInstanceSequenceNumber": true,
    "processInstanceStartDatetime": true,
    "processInstanceState": true,
    "processInstanceTitle": true,
    "processModelId": true,
    "processModelInfoCategory": true,
    "processModelInfoId": true,
    "processModelInfoName": true,
    "processModelVersion": true,
    "starred": true
  };
  return objProcessProperties[strColId] === true;
}


/*
Notes-en:
- When a Case (Process Instance) arrives at this Automated Step, an extraction request is sent.
    - The target Workflow Platform is set via URL. (REST API connection destination)
        - `https://karasuma-oike-000.questetra.net/`
        - Note that a trailing `/` is required.
    - If the same Platform, you can substitute the URL by using a [System Variable].
        - `${var[applicationRoot]}`
- The extracted TSV is saved as the "simplest tab-separated string".
    - TAB codes and newline codes within each data field are converted to " " (half-width spaces).
    - Double quotes are also retained in an unescaped state.
- Only the data displayed in the list specified by the `ReportID` will be stored. (However, the column order is random).
    - Configure the TSV column order in B0 using Data Item IDs (`processDataDefinitionNumber`) as comma-separated values (CSV).
        - Example: `0,47,2,8`
    - To store property data of the Case (Process Instance) in a TSV column, set the following variable names instead of the Data Item ID. (Example: `processInstanceId,0,47,2,8`)
        - Parent App Process Instance ID: `parentProcessInstanceId`
        - Debug Mode: `processInstanceDebug` ("false" or "true")
        - Issue End Datetime: `processInstanceEndDatetime`
        - Issue ID: `processInstanceId` (eg: "1234")
        - Issue ID (with 'p'): `processInstanceIdForView` (eg: "p1234")
        - Starting User's Organization ID: `processInstanceInitQgroupId` (eg: "null")
        - Starting User's Organization Name: `processInstanceInitQgroupName` (eg: "null")
        - Starting User's User ID: `processInstanceInitQuserId` (eg: "null")
        - Starting User's User Name: `processInstanceInitQuserName` (eg: "null")
        - Sequence Number (Variable managed at the App level): `processInstanceSequenceNumber` (eg: "29")
        - Issue Start Datetime: `processInstanceStartDatetime` (eg: "2026-05-26T08:55:40+0900")
        - State: `processInstanceState` (eg: "STARTED")
        - Title: `processInstanceTitle` (eg: "Inquiry OHTANI, Shohei")
        - App ID (Process Model ID): `processModelId` (eg: "144353892")
        - App Category Name: `processModelInfoCategory` (eg: "6-Marketing-Dept")
        - App Category ID: `processModelInfoId` (eg: "2078")
        - App Name (Process Model Name): `processModelInfoName` (eg: "611-Adv-Process")
        - App Version: `processModelVersion` (eg: "222")
        - App Starred (Favorited): `starred` (eg: "false")

Notes-ja:
- このアドオン自動工程にケース（案件）が到達した際、抽出リクエストが投げられます。
    - ターゲットとなるワークフロー基盤は URL で設定します。（REST API 接続先）
        - `https://karasuma-oike-000.questetra.net/`
        - 末尾の `/` が必要である点に注意
    - ターゲットとなるワークフロー基盤が同一基盤の場合、［システム変数］による設定で代用可能利です。
        - `${var[applicationRoot]}`
- 抽出TSVは「もっともシンプルなタブ区切り文字列」として格納されます。
    - 各データ内のTABコードや改行コードは " " （半角スペース）に変換されます。
    - ダブルクオート文字も、エスケープされていない状態で保持されます。
- ReportID は、リスト表示時のURI末尾を参照します。
    - 
- ReportID で指定したリストに表示されているデータだけが格納されます。（ただし列順不同）
    - TSV列順はデータ項目ID（processDataDefinitionNumber）を用いたCSVで設定します。
        - 例: `0,47,2,8`
    - TSV列にケース（案件）のプロパティデータを格納させたい場合、データ項目IDの代わりに次の変数名を設定します。 （例: `processInstanceId,0,47,2,8`）
        - 親アプリのケースID: `parentProcessInstanceId`
        - デバッグモード: `processInstanceDebug` ("false" or "true")
        - ケース終了時刻: `processInstanceEndDatetime`
        - ケースID: `processInstanceId` (eg:"1234")
        - ケースID(p付): `processInstanceIdForView` (eg:"p1234")
        - 開始ユーザの所属組織ID: `processInstanceInitQgroupId` (eg:"null")
        - 開始ユーザの所属組織名: `processInstanceInitQgroupName` (eg:"null")
        - 開始ユーザのユーザID: `processInstanceInitQuserId` (eg:"null")
        - 開始ユーザのユーザ名: `processInstanceInitQuserName` (eg:"null")
        - ケース連番(アプリレベルで管理される変数): `processInstanceSequenceNumber` (eg: "29")
        - ケース開始日時: `processInstanceStartDatetime` (eg:"2026-05-26T08:55:40+0900")
        - 状態: `processInstanceState` (eg:"STARTED")
        - 件名: `processInstanceTitle` (eg:"問い合わせ OHTANI, Shohei")
        - アプリID: `processModelId` (eg:"144353892")
        - アプリのカテゴリ名: `processModelInfoCategory` (eg:"6-マーケティング部")
        - アプリのカテゴリID: `processModelInfoId` (eg:"2078")
        - アプリ名: `processModelInfoName` (eg:"611-広告出稿プロセス")
        - アプリ名: `processModelVersion` (eg:"222")
        - アプリの★有無: `starred` (eg:"false")


APPENDIX-en
- If the number of processes of the filter extraction result is 1000 or more, the data after 1001 will not be stored.
    - Even if 1000 or less, there is a possibility of timeout when there are many data items.
    - To control the number of extraction results
        - Extract only the processes whose status status is `Running`
        - Extract only the date data of the previous month
            - After: `#today.addMonths(-1).getFirstTimeInMonth()`
            - Before: `#today.addMonths(-1).getLastTimeInMonth()`
- "HTTP Authorization Setting" is required for automatic communication (OAuth2 API connection).
    - Register [API Client] (OAuth2) in advance as the connection destination. (Requires System Admin privileges)
        - Scope
            - Read-only API Access
        - Redirect URL
            - `https://s.questetra.net/oauth2callback`
        - Make a __note__ of your registration details.
            - Client ID
            - Client Secret (Consumer Secret)
            - Authorization Endpoint URL
            - Token Endpoint URL
    - Set "HTTP Authorization Setting" in the App as client. (Select this setting name in Addon config)
        - Authorization Endpoint URL:
            - _(See your note above)_
            - Example: `https://example.questetra.net/oauth2/authorize`
        - Token Endpoint URL:
            - _(See your note above)_
            - Example: `https://example.questetra.net/oauth2/token`
        - Scope:
            - `read`
        - Client ID
            - _(See your note above)_
        - Client Secret:
            - _(See your note above)_
- Table type data is not supported for extraction.
    - If an unsupported type, "`N/A`" 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)


APPENDIX-ja:
- フィルタ抽出結果の案件数（プロセス数）が1000件以上ある場合、1001件以降のデータは格納されません。
    - 抽出結果が1000件以下となるケースでも、データ項目の数が多い場合など、タイムアウトする可能性があります。
    - 抽出結果の数をコントロールする工夫
        - 状態ステータスが `未終了` の案件（プロセス）に絞って抽出する
        - 案件内の日付データが前月のものに絞って抽出する
            - 以降/After: `#today.addMonths(-1).getFirstTimeInMonth()`
            - 以前/Before: `#today.addMonths(-1).getLastTimeInMonth()`
- 自動通信（OAuth2 API接続）のために "HTTP認証設定" が必要です。
    - あらかじめ接続先に、［APIクライアント］（OAuth2）を登録します。（システム管理者権限が必要です）
        - スコープ（Scope）
            - データを取得する API へのアクセス / Read-only API Access
        - リダイレクトURL（Redirect URL）
            - `https://s.questetra.net/oauth2callback`
        - 登録内容を __メモ__ します。
            - クライアントID（Client ID）
            - クライアントシークレット（Consumer Secret）
            - 認可エンドポイントURL（Authorization Endpoint URL）
            - トークンエンドポイントURL（Token Endpoint URL）
    - 通信元であるワークフローアプリにて［HTTP認証設定］を設定します。（この設定名をAddonコンフィグで選択）
        - 認可エンドポイント URL:
            - (メモを参照)
            - 例： `https://example.questetra.net/oauth2/authorize`
        - トークンエンドポイント URL:
            - (メモを参照)
            - 例： `https://example.questetra.net/oauth2/token`
        - スコープ:
            - `read`
        - クライアントID
            - (メモを参照)
        - クライアントシークレット:
            - (メモを参照)
- テーブル型データ項目内のデータは抽出サポート外です。
    - サポート外のデータ型は "`N/A`" が出力されます
    - ファイル型を指定した場合、ファイル名が抽出されます（複数：スペース区切り）
    - 選択型を指定した場合、表示テキストが抽出されます（複数：スペース区切り）

*/