Date #TSV: Create Pivot by Month

// Script Example of Business Process Automation
// for 'engine type: 3' ("GraalJS standard mode")
// cf. 'engine type: 2' ("GraalJS Nashorn compatible mode") (renamed from "GraalJS" at 20230526)


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

//// == Config Retrieving / 工程コンフィグの参照 ==
const strTsv         = configs.get        ( "StrConfA" );     // REQUIRED
  if( strTsv === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A: TSV} is empty \n" );
  }
  const arr2dTsv     = parseAsRectangular ( strTsv );  // [row,col]
const strColIdDate   = configs.get( "StrConfB1" );            // REQUIRED
const strColIdsNum   = configs.get( "StrConfB2" );            // not required
const strColIdX      = configs.get( "StrConfB3" );            // REQUIRED
const strFyMonth     = configs.get( "StrConfC2" );            // not required
const strPocketC3    = configs.getObject ( "SelectConfC3" );  // not required
const strPocketC4    = configs.getObject ( "SelectConfC4" );  // not required
const strPocketC5    = configs.getObject ( "SelectConfC5" );  // not required
const numPocketD1    = configs.getObject ( "SelectConfD1" );  // not required
const numPocketD2    = configs.getObject ( "SelectConfD2" );  // not required
const numPocketD3    = configs.getObject ( "SelectConfD3" );  // not required




//// == Data Retrieving / ワークフローデータの参照 ==
// (Nothing. Retrieved via Expression Language in Config Retrieving)




//// == Calculating / 演算 ==
// パラメータのパースとバリデーション
const idxDate = parseInt(strColIdDate, 10);
if (isNaN(idxDate) || idxDate < 0) {
  throw new Error("\n AutomatedTask ConfigError: Invalid Date Column ID in B1 \n");
}

const idxX = parseInt(strColIdX, 10);
if (isNaN(idxX) || idxX < 0) {
  throw new Error("\n AutomatedTask ConfigError: Invalid X-axis Column ID in B3 \n");
}

let idxNum = NaN;
if (strColIdsNum !== "") {
  idxNum = parseInt(strColIdsNum.trim(), 10);
  if (isNaN(idxNum) || idxNum < 0) {
    throw new Error("\n AutomatedTask ConfigError: Invalid Numeric Column ID in B2 \n");
  }
}

let yeMonth = 0;
if (strFyMonth !== "") {
  yeMonth = parseInt(strFyMonth, 10);
  if (isNaN(yeMonth) || yeMonth < 0 || yeMonth > 12) {
    throw new Error("\n AutomatedTask ConfigError: Invalid YE Month in C2 (must be 1-12) \n");
  }
}

// ヘッダー行の処理とチェック
if (arr2dTsv.length === 0) {
  throw new Error("\n AutomatedTask DataError: Input TSV is empty \n");
}
const headerRow = arr2dTsv[0];
if (idxDate >= headerRow.length) {
  throw new Error(`\n AutomatedTask ConfigError: Date Column ID (${idxDate}) is out of bounds \n`);
}
if (idxX >= headerRow.length) {
  throw new Error(`\n AutomatedTask ConfigError: X-axis Column ID (${idxX}) is out of bounds \n`);
}
if (!isNaN(idxNum) && idxNum >= headerRow.length) {
  throw new Error(`\n AutomatedTask ConfigError: Numeric Column ID (${idxNum}) is out of bounds \n`);
}

// X軸の一意な値を抽出し、全体の合計値（数値列の総和、または行数）で降順ソートするための準備
const xLabelsSet = new Set();
const mapXTotal = new Map();

for (let i = 1; i < arr2dTsv.length; i++) {
  const row = arr2dTsv[i];
  const strDate = row[idxDate];
  if (idxX < row.length) {
    const xVal = row[idxX].trim();
    if (xVal !== "") {
      xLabelsSet.add(xVal);
      
      // 有効なデータ行（日付が空でない）の場合のみソート用合計にカウント
      if (strDate && strDate.trim() !== "") {
        if (!mapXTotal.has(xVal)) {
          mapXTotal.set(xVal, 0);
        }
        
        if (!isNaN(idxNum) && idxNum < row.length) {
          const cellVal = row[idxNum];
          if (cellVal !== "") {
            const numValStr = cellVal.replace(/[^0-9.-]/g, ''); 
            const numVal = Number(numValStr);
            if (numValStr !== "" && !isNaN(numVal)) {
              mapXTotal.set(xVal, mapXTotal.get(xVal) + numVal);
            }
          }
        } else {
          mapXTotal.set(xVal, mapXTotal.get(xVal) + 1);
        }
      }
    }
  }
}

// 全体の合計値が大きい順（降順）にX軸ラベルをソート
const xLabels = Array.from(xLabelsSet).sort((a, b) => {
  const totalA = mapXTotal.get(a) || 0;
  const totalB = mapXTotal.get(b) || 0;
  return totalB - totalA; 
});

// 集計処理の準備
// mapPivot: key = yLabel + "\t" + xLabel, value = { count: 0, sum: 0 }
const mapPivot = new Map();
let minAbsM = Infinity;
let maxAbsM = -Infinity;

// データ行のループ（1行目はヘッダーとしてスキップ）
for (let i = 1; i < arr2dTsv.length; i++) {
  const row = arr2dTsv[i];
  const strDate = row[idxDate];
  const strX = row[idxX] ? row[idxX].trim() : "";

  // 日付またはX軸の値が空ならスキップ
  if (!strDate || strDate.trim() === "" || strX === "") continue;

  let dateObj;
  try {
    dateObj = parseAsLocalDate(strDate);
  } catch (e) {
    engine.log(`Row ${i + 1} skipped: Failed to parse date "${strDate}".`);
    continue;
  }

  // ラベルと絶対月番号の取得
  const mInfo = getMonthInfo(dateObj, yeMonth);
  const yLabel = mInfo.label;
  const absM = mInfo.absM;

  // 最小・最大の月を記録
  if (absM < minAbsM) minAbsM = absM;
  if (absM > maxAbsM) maxAbsM = absM;

  const key = yLabel + "\t" + strX;
  if (!mapPivot.has(key)) {
    mapPivot.set(key, { count: 0, sum: 0 });
  }

  const data = mapPivot.get(key);
  data.count++;

  // 数値列の加算
  if (!isNaN(idxNum) && idxNum < row.length) {
    const cellVal = row[idxNum];
    if (cellVal !== "") {
      const numValStr = cellVal.replace(/[^0-9.-]/g, ''); 
      const numVal = Number(numValStr);
      
      if (numValStr !== "" && !isNaN(numVal)) {
        data.sum += numVal;
      } else if (cellVal.trim() !== "") {
        engine.log(`Row ${i + 1} Col ${idxNum} warning: Unparseable number "${cellVal}".`);
      }
    }
  }
}

// Y軸の月次ラベルのリスト（トビの穴埋めを含む）
const yLabels = [];
if (minAbsM !== Infinity) {
  for (let m = minAbsM; m <= maxAbsM; m++) {
    yLabels.push(getLabelFromAbsoluteMonth(m, yeMonth));
  }
}

// ==== 出力用TSVの構築 ====
const arrCountTsv = [];
const arrSumTsv = [];
const arrAvgTsv = [];

// ヘッダー設定
const xHeaderName = headerRow[idxX] ? `(${headerRow[idxX]})` : "(Source)";
const tsvHeader = [xHeaderName].concat(xLabels).join("\t");

arrCountTsv.push(tsvHeader);
if (!isNaN(idxNum)) {
  arrSumTsv.push(tsvHeader);
  arrAvgTsv.push(tsvHeader);
}

// データ行の構築
for (const yLabel of yLabels) {
  const rowCount = [yLabel];
  const rowSum = [yLabel];
  const rowAvg = [yLabel];

  for (const xLabel of xLabels) {
    const key = yLabel + "\t" + xLabel;
    const data = mapPivot.get(key);

    const count = data ? data.count : 0;
    const sum = data ? data.sum : 0;
    
    const avg = (data && data.count > 0) ? Number((data.sum / data.count).toFixed(2)) : 0;

    rowCount.push(String(count));
    if (!isNaN(idxNum)) {
      rowSum.push(String(sum));
      rowAvg.push(String(avg));
    }
  }

  arrCountTsv.push(rowCount.join("\t"));
  if (!isNaN(idxNum)) {
    arrSumTsv.push(rowSum.join("\t"));
    arrAvgTsv.push(rowAvg.join("\t"));
  }
}




//// == Data Updating / ワークフローデータへの代入 ==

if ( strPocketC3 !== null ){ 
  engine.setData( strPocketC3, arrCountTsv?.join( '\n' ) ?? "" );
}
if ( strPocketC4 !== null ){ 
  engine.setData( strPocketC4, !isNaN(idxNum) ? (arrSumTsv?.join( '\n' ) ?? "") : "" );
}
if ( strPocketC5 !== null ){ 
  engine.setData( strPocketC5, !isNaN(idxNum) ? (arrAvgTsv?.join( '\n' ) ?? "") : "" );
}

if ( numPocketD1 !== null ){ 
  engine.setData( numPocketD1, new java.math.BigDecimal( strTsv.split("\n").length ) );
}
if ( numPocketD2 !== null ){ 
  engine.setData( numPocketD2, new java.math.BigDecimal( arr2dTsv.length ) );
}
if ( numPocketD3 !== null ){ 
  engine.setData( numPocketD3, new java.math.BigDecimal( arrCountTsv.length ) );
}

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

/**
 * 日付と決算月から月次ラベルと絶対月番号（ソート・補間用）を生成する
 * @param {Date} dateObj - JavaScript Date object
 * @param {number} yeMonth - Year End Month (0 for standard month, 1-12 for FY)
 * @returns {Object} { label: string, absM: number }
 */
function getMonthInfo(dateObj, yeMonth) {
  const y = dateObj.getFullYear();
  const m = dateObj.getMonth() + 1; // 1-12

  if (yeMonth === 0) {
    // 通常の月次 (YYYY-MM)
    const absM = y * 12 + (m - 1);
    const mStr = m < 10 ? `0${m}` : `${m}`;
    return { label: `${y}-${mStr}`, absM: absM };
  } else {
    // FYベースの月次 (FYYYY-MM)
    let fy = y;
    if (m > yeMonth) {
      fy = y + 1;
    }
    let shiftedM = m - yeMonth;
    if (shiftedM <= 0) {
      shiftedM += 12;
    }
    const absM = fy * 12 + (shiftedM - 1);
    const mStr = m < 10 ? `0${m}` : `${m}`;
    return { label: `FY${fy}-${mStr}`, absM: absM };
  }
}

/**
 * 絶対月番号と決算月から、ラベル文字列を復元する（トビの穴埋め用）
 * @param {number} absM - Absolute Month Number
 * @param {number} yeMonth - Year End Month
 * @returns {string} Month label
 */
function getLabelFromAbsoluteMonth(absM, yeMonth) {
  if (yeMonth === 0) {
    const y = Math.floor(absM / 12);
    const m = (absM % 12) + 1;
    const mStr = m < 10 ? `0${m}` : `${m}`;
    return `${y}-${mStr}`;
  } else {
    const fy = Math.floor(absM / 12);
    const shiftedM = (absM % 12) + 1;
    let m = shiftedM + yeMonth;
    if (m > 12) {
      m -= 12;
    }
    const mStr = m < 10 ? `0${m}` : `${m}`;
    return `FY${fy}-${mStr}`;
  }
}


/**
 * Parses TSV string as a two-dimensional rectangular data matrix and creates a 2D array.
 * @param {string} strTsv - The input TSV string
 * @returns {Array<Array<string>>} Rectangular 2D array
 */
function parseAsRectangular( strTsv ){
  const arrTsv = strTsv.split("\n");
  let numMinWidth   = Infinity;
  let numMaxWidth   = 0;
  let numBlanklines = 0;
  
  const parsedRows = [];
  for( let i = 0; i < arrTsv.length; i++ ){
    const line = arrTsv[i];
    if( line === "" ){
      numBlanklines++;
      continue;
    }
    const arrCells = line.split("\t");
    const len = arrCells.length;
    if( len < numMinWidth ){ numMinWidth = len; }
    if( len > numMaxWidth ){ numMaxWidth = len; }
    parsedRows.push( arrCells );
  }
  
  if( numMinWidth === Infinity ){
    numMinWidth = 0;
  }
  engine.log( " AutomatedTask TsvDataCheck:" + 
              " MinWidth:" + numMinWidth +
              " MaxWidth:" + numMaxWidth +
              " Lines:" + arrTsv.length +
              " (BlankLines:" + numBlanklines + ")" );

  for( let i = 0; i < parsedRows.length; i++ ){
    const row = parsedRows[i];
    while( row.length < numMaxWidth ){
      row.push( "" );
    }
  }
  return parsedRows;
}


/**
 * Parses a Date or Datetime string and returns a JavaScript `Date` object as local time.
 * Timezone information in the string (such as "Z" or "+09:00") is forcefully ignored.
 * Supports various formats: "YYYYMMDD", "YYYY-MM-DD", ISO 8601, Japanese localized formats, etc.
 * * @param {string} str - The date or datetime string to parse.
 * @returns {Date} A local Date object.
 * @throws {Error} Throws an error if the string is empty or contains fewer than 2 numeric parts.
 */
function parseAsLocalDate ( str ){ // strDateOrDatetime
  if( str === "" ){
    throw new Error( "\n AutomatedTask ParseDateError:" +
                     " String is empty \n" );
  }
  const arrNumParts = str.match( /\d+/g );
  if( arrNumParts === null ){
    throw new Error( "\n AutomatedTask ParseDateError:" +
                     " No numeric characters in: " + str + "\n" );
  }

  /// function default
  let numYear    = 0; // limit in QBPMS: 1900 to 2100 (asof 2026-06)
  let indexMonth = 0;
  let numDay     = 1; // 日付が省略されている場合は「1日」にフォールバック (Default to 1st)
  let numHours   = 0;
  let numMinutes = 0;

  /// case: startsWith YYYYMMDD (8 digits)
  if( arrNumParts[0].length === 8 ){
    numYear      = parseInt( arrNumParts[0].slice( 0, 4 ), 10 );
    indexMonth   = parseInt( arrNumParts[0].slice( 4, 6 ), 10 ) - 1;
    numDay       = parseInt( arrNumParts[0].slice( 6, 8 ), 10 );
    if (arrNumParts.length > 1) {
      if (arrNumParts[1].length >= 4) {
        // like "2359" or "235959Z"
        numHours = parseInt(arrNumParts[1].slice(0, 2), 10);
        numMinutes = parseInt(arrNumParts[1].slice(2, 4), 10);
      } else {
        // like "23:59" -> arrNumParts is ['...', '23', '59']
        numHours = parseInt(arrNumParts[1], 10);
        if (arrNumParts.length > 2) {
          numMinutes = parseInt(arrNumParts[2], 10);
        }
      }
    }

  /// case: startsWith YYYYMM (6 digits)
  } else if ( arrNumParts[0].length === 6 ){
    numYear      = parseInt( arrNumParts[0].slice( 0, 4 ), 10 );
    indexMonth   = parseInt( arrNumParts[0].slice( 4, 6 ), 10 ) - 1;
    // numDay remains 1

  /// case: not startsWith YYYYMMDD, like "2026-12-31 23:59", "2025/06", "2025年6月"
  } else {
    // 必須パーツを「3」から「2（年月）」に緩和
    if( arrNumParts.length < 2){
      throw new Error( "\n AutomatedTask ParseDateError:" +
                       " At least 2 Parts (Year, Month) are needed in: " + str + "\n" );
    }
    numYear      = parseInt( arrNumParts[0], 10 );
    indexMonth   = parseInt( arrNumParts[1], 10 ) - 1;
    
    // 日付が存在する場合のみ上書き
    if (arrNumParts.length > 2) {
      numDay       = parseInt( arrNumParts[2], 10 );
    }
    if (arrNumParts.length > 3) {
      numHours = parseInt(arrNumParts[3], 10);
    }
    if (arrNumParts.length > 4) {
      numMinutes = parseInt(arrNumParts[4], 10);
    }
  }

  /// return JavaScript `Date` object
  const localDate = new Date(numYear, indexMonth, numDay, numHours, numMinutes);
  localDate.setFullYear(numYear);
  return localDate;
}



/*
### Sample Data 1 (date:id1, num:4, x-axis:2)

ID	DATE	Source-Channel	Rank	Total
L-001	2026/01/15	JaSite	A	\ 150,000
L-002	2026-02-10	Gmail	B	30,000
L-003	2026.02.28	EnSite	A	$ 1,200
L-004	20260305	JaSite	C	50,000
L-005	2026/04/12	Gmail	A	120,000
L-006	2026-05-20	EnSite	B	85,000
L-007	2026/06/01	JaSite	A	210,000
L-008	2026-07-15	Gmail	A	95,000
L-009	2026/08/22	EnSite	C		
L-010	2026/09	JaSite	B	130,000

### Sample Data 2

取引No	取引日	担当部門	勘定科目	金額（円）	インボイス
1	2026/04/01	管理部	消耗品費	5,500	適格
2	2026/04/02	営業部	旅費交通費	1,320	適格
3	2026-05-15	マーケティング部	広告宣伝費	55,000	適格
4	2026/06/03	管理部	通信費	8,800	適格
5	2026/07/05	マーケティング部	広告宣伝費	110,000	適格
6	2026-08-12	営業部	旅費交通費	24,500	適格
7	2026/09/01	管理部	福利厚生費	12,000	適格
8	2026/11/20	営業部	交際費	18,000	非適格
9	2027-01-06 12:30	マーケティング部	広告宣伝費	33,000	適格
10	2027/02/15	管理部	消耗品費	4,200	適格


### NOTES-en

* When a case reaches this [Automated Step], the TSV pivot table creation process is automatically executed.
    * The first row of the input TSV is recognized as the header row.
* The input TSV string is parsed as a simple tab-separated values string.
    * Double quotes and other specific characters within the TSV string are retained as is.
    * Blank lines within the TSV string are ignored.
    * For rows lacking the expected number of cells, empty string ("") cells are automatically appended.
    * The number of rows in the input/output TSV string can be stored as Case Data.
* The date column used as the Y-axis aggregation key is specified by its column ID.
    * It does not necessarily have to be Column A (id=0).
    * Set this to "1" if Column B (id=1) is to serve as the aggregation key.
* The column used as the X-axis aggregation key is specified by its column ID.
    * If the computation time becomes excessive (eg, due to an extremely large number of columns), a timeout may occur.
    * The columns on the X-axis are dynamically sorted in descending order of occurrence frequency or total value.


### NOTES-ja

* 案件（ケース）がこの［自動工程］に到達すると、Pivot生成処理が自動実行されます。
    * 入力TSVの1行目は、ヘッダー行として認識されます。
* 入力TSV文字列は、シンプルなタブ区切り文字列としてパースされます。
    * TSV文字列内のダブルクオート文字等も、そのまま保持されます。
    * TSV文字列内の空行は、無視されます。
    * TSV文字列内でセル数の足りない行は、空文字（""）のセルが自動的に補完されます。
    * 入出力TSVの行数は、ケースデータとして格納可能です。
* Y集計キーとなる日付列はIDで指定します
    * 必ずしもA列（id=0）でなくても構いません。
    * B列（id=1）が集計キーとなる場合、"1" とセットします。
* X集計キーとなる列はIDで指定します
    * 計算時間が大きくなる（極端に列数が多いなど）と、タイムアウトする可能性があります。
    * ※X軸の列は、出現頻度または合計値の降順で動的に並び替わります。


### APPENDIX-en
* The default format for the month label is `2026-04`.
    * By setting the Year-End (YE) Month, you can switch to the FY format (Fiscal Year basis / End-Year style).
        * Note) End-Year style: FY2027 = 2026/04/01 to 2027/03/31
        * e.g.: "3"; `2026-04` → `FY2027-04`
        * e.g.: "12"; `2026-04` → `FY2026-04`　(Since Jan-Mar is Q1)
* Cells in the specified numeric columns are parsed as numbers as much as possible. *(Excluding the first header row)*
    * Any characters other than periods (`.`), minus signs (`-`), and digits (`0-9`) are stripped out.
        * such as commas acting as digit separators or currency symbols
    * The resulting string is evaluated using `Number()`.
    * If parsing fails, the value is treated as `0`.
    * e.g.: `$ 1,500.50 usd` ⇒ `1500.5`
* If there are missing periods (gaps) between the oldest and newest months in the input data, they are automatically filled with `0`.
    * Both the row count and the numeric sums will be output as `0`.
    * e.g.: If data only exists for `2026-04` and `2026-06`, the missing `2026-05` will automatically be generated and output with a count of `0`.


### APPENDIX-ja
* 月次ラベルのデフォルトは `2026-04` です。
    * 決算月をセットすればFY方式（会計年度ベース／終了年方式）に変更可能です。
        * ※ 終了年方式： FY2027 = 2026/04/01 ～ 2027/03/31
        * 例: "3"; `2026-04` → `FY2027-04`
        * 例: "12"; `2026-04` → `FY2026-04`
* 数値列として指定された列の各データは、可能な限り数値として解析されます。※先頭1行目（ヘッダー行）を除く
    * 「ピリオド(.)」「マイナス記号(-)」「数字(0～9)」以外の文字が存在する場合は、それらが除去された上で解析されます。
        * 桁区切りカンマや通貨記号など
    * 除去後の文字列は `Number()` によって数値化されます。
    * 解析に失敗した場合は `0` として加算されます。
    * 例: `$ 1,500.50 usd` ⇒ `1500.5`
* 入力データにおける「最も古い月」から「最も新しい月」までの間に、データが存在しない月（トビ）がある場合、その月は自動的に穴埋め（補完）されます。
    * カウントおよび数値列の合計は `0` として出力されます。
    * 例: `2026-04` と `2026-06` のデータのみが存在する場合、間の `2026-05` はカウント `0` の行として自動生成されます。
*/