業務スケジュール設計

## 関連記事
4つの記事に分けていますので参考にしてください
- [業務スケジュールパターン](https://qiita.com/mkuwan/items/ae03f5cc011c900fe2bc)
- **業務スケジュール設計(今回の記事)**
- [業務スケジュール実装](https://qiita.com/mkuwan/items/d1d40a5de29a012c69f9)
- [業務スケジュールテスト](https://qiita.com/mkuwan/items/ad905e7668117f97de84)

# 業務スケジュール生成システム 設計ドキュメント

## 概要

本システムは、Office Scripts for Excelを利用して、企業や組織における日常的な業務スケジュールを自動生成するためのツールです。業務一覧テーブルに定義された様々な条件に基づいて、特定の日付に実行すべき業務を判定し、スケジュールとして出力します。

この設計はAIに読ませることを前提にするため、AI(Github Copilot)に作成してもらっています。
内容については人間によるレビューをしていますが、複雑なパターンのロジックをここまで理解して作成できることに正直驚きました。

システムの背景には、以下のような課題があります：
- 日次・週次・月次・年次など様々な周期で実行される業務の管理が煩雑
- 手動での業務スケジュール作成に時間と労力がかかる
- 実行条件の複雑さ（営業日基準、暦日基準、曜日指定など）による漏れや誤りのリスク
- 祝日や休業日に当たる場合の振替処理の対応

これらの課題を解決するために、本システムでは以下の特徴を持つスケジュール生成機能を実現しています：

1. 多様な業務パターンへの対応（日次、週次、月次、年次、特定日付、特定曜日など）
2. 営業日・非営業日を考慮した業務スケジュール生成
3. 非営業日の場合の振替ルール（直前営業日、直後営業日、振替なし）
4. 詳細なデバッグ機能によるトラブルシューティングのサポート
5. Excelテーブルとの連携による使いやすいインターフェース

本システムの利用により、業務スケジュールの作成時間の大幅な削減、業務漏れの防止、業務実行の標準化と効率化を実現し、組織の業務品質向上に貢献します。

## システム構成

### データ構造

システムは以下の3つの主要なデータテーブルを使用します：

#### 1. カレンダーテーブル
- 日付ごとの営業日情報を格納
- 列構成：
  - 年月日（YYYY-MM-DD形式）：カレンダーの基準日
  - 営業日フラグ（TRUE/FALSE）：営業日かどうかを示すフラグ
  - 備考（祝日名など）：休日理由や特記事項
  - 曜日：月、火、水、木、金、土、日
  - 営業日：月内での営業日番号（1始まり）
  - 逆算営業日：月末からの営業日カウント（最終営業日=0）

#### 2. 業務一覧テーブル
- 業務の基本情報と実行条件を格納
- 列構成：
  - 業務ID（一意の識別子）：各業務を識別する一意のID
  - 業務名：業務の名称
  - 業務詳細：業務内容の詳細説明
  - 業務種別：「定常」「依頼」など業務の種類
  - 周期・頻度（日次/週次/月次/年次）：業務の実行周期
  - 基準：業務実行の基準日の決定方法
    - 暦日：暦上の日付による指定
    - 営業日：営業日カウントによる指定
    - 暦日(n日指定)：毎月特定の日（例：毎月15日）
    - 営業日(n日指定)：特定の営業日（例：第3営業日）
    - 暦日(月末逆算)：月末からの逆算（例：月末3日前）
    - 営業日(月末逆算)：月末からの営業日カウント（例：最終営業日から2営業日前）
    - 暦日(曜日)：特定の曜日（例：毎週月曜）
  - 月（特定月を指定、年次業務の場合）：1～12の月指定
  - 週番号（第何週か、1-5）：第何週かを指定
  - 曜日（月-日）：曜日指定
  - n日（日にちや営業日番号）：何日目かを指定
  - 非営業日振替規則：非営業日だった場合の振替方法
    - 直前営業日：非営業日なら直前の営業日に振替
    - 直後営業日：非営業日なら直後の営業日に振替
    - 振替しない：非営業日でも日付変更しない
  - 優先度：業務の優先順位（高/通常/低など）
  - 有効開始日：業務の有効期間開始日
  - 有効終了日：業務の有効期間終了日（空欄は無期限）
  - 備考：その他補足情報

#### 3. スケジュールテーブル
- 生成された業務スケジュールを格納
- 列構成：
  - スケジュールID（一意の識別子）：各スケジュールの一意のID
  - 業務ID（業務一覧の業務を参照）：業務一覧テーブルの業務ID
  - 予定日：業務の予定実施日
  - 作業者：担当者名（手動入力項目）
  - 開始予定時刻：予定開始時間（手動入力項目）
  - 実開始時刻：実際の開始時刻（手動入力項目）
  - 実終了時刻：実際の終了時刻（手動入力項目）
  - ステータス：「未実施」「進行中」「完了」など（手動入力項目）
  - メモ：作業メモや結果（手動入力項目）

#### テーブルの関係図

```mermaid
graph LR
    業務一覧["業務一覧テーブル"]
    業務スケジュール["業務スケジュールテーブル"]
    カレンダー["カレンダーテーブル"]

    業務一覧 -->|"1:多"| 業務スケジュール
    カレンダー -->|"日付参照"| 業務スケジュール

    class 業務一覧,業務スケジュール,カレンダー table;
```

### ファイル構成

- `scheduler.osts`: Office Scripts for Excelのメインスクリプトファイル
- Excelファイル: 上記3つのテーブルを含むExcelファイル

### システム全体構成図

```mermaid
flowchart TB
    subgraph "データソース"
        カレンダーCSV[カレンダー.csv]
        業務一覧テーブル[業務一覧テーブル.csv]
    end
    
    subgraph "処理・判定エンジン"
        日次処理[日次処理]
        週次処理[週次処理]
        月次処理[月次処理]
        年次処理[年次処理]
        営業日判定[営業日判定]
        非営業日振替処理[非営業日振替処理]
    end
    
    subgraph "出力"
        業務スケジュール[業務スケジュールテーブル]
        デバッグログ[デバッグログシート]
    end
    
    カレンダーCSV --> 営業日判定
    業務一覧テーブル --> 日次処理
    業務一覧テーブル --> 週次処理
    業務一覧テーブル --> 月次処理
    業務一覧テーブル --> 年次処理
    営業日判定 --> 日次処理
    営業日判定 --> 週次処理
    営業日判定 --> 月次処理
    営業日判定 --> 年次処理
    営業日判定 --> 非営業日振替処理
    日次処理 --> 非営業日振替処理
    週次処理 --> 非営業日振替処理
    月次処理 --> 非営業日振替処理
    年次処理 --> 非営業日振替処理
    非営業日振替処理 --> 業務スケジュール
    非営業日振替処理 --> デバッグログ
```

### データフロー概要

```mermaid
flowchart TD
    subgraph InputData[入力データ]
        Calendar[カレンダーシート]
        TaskList[業務一覧シート]
        InputDate[入力日付]
    end
    
    subgraph ProcessingData[処理中データ]
        CalendarMap[カレンダーマップ]
        TaskData[業務データ配列]
        Headers[ヘッダー情報]
        DateInfo[日付情報]
    end
    
    subgraph OutputData[出力データ]
        ScheduleSheet[業務スケジュールシート]
        DebugLogs[デバッグログシート]
    end
    
    InputData --> ProcessingData
    ProcessingData --> OutputData
    
    Calendar --> CalendarMap
    TaskList --> TaskData
    TaskList --> Headers
    InputDate --> DateInfo
    
    CalendarMap --> isBusinessDay
    DateInfo --> isTargetTask
    TaskData --> isTargetTask
    Headers --> isTargetTask
    
    isTargetTask --> ScheduleSheet
    debugLog --> DebugLogs
```

## 機能仕様

### 業務スケジュール生成機能

- **機能概要**: 指定された日付に実行すべき業務を判定し、スケジュールを生成
- **入力**: 対象日付（YYYY-MM-DD形式、YYYY/MM/DD形式、またはM/D/YYYY形式）
- **出力**: スケジュールテーブルへの業務登録
- **処理内容**:
  1. 入力日付の正規化（複数の日付形式に対応）
  2. カレンダー情報の読み込み
  3. 業務一覧データの取得
  4. 既存スケジュールの確認（同日のスケジュールが存在する場合は処理中断）
  5. 業務条件判定（通常判定）
  6. 振替判定（他の日付からの振替対象かどうか）
  7. 対象業務のスケジュールテーブルへの登録
  8. 処理結果のログ記録

### 日付処理機能

- **機能概要**: 様々な形式の日付を扱うためのユーティリティ関数群
- **主要機能**:
  - 日付形式変換（Excel日付⇔文字列）
  - 特定の日付の情報取得（年、月、日、曜日など）
  - 月末日の取得
  - 月の第n曜日の取得
  - 月のn営業日目の取得
  - 月末からn営業日前の日付取得

### 営業日判定機能

- **機能概要**: カレンダーデータに基づく営業日関連の処理
- **主要機能**:
  - 指定日が営業日かどうかの判定
  - 特定日の直前/直後の営業日の取得
  - 月内での営業日情報（何営業日目か、月末から何営業日前か）の取得

### 業務条件判定機能

- **機能概要**: 業務データの実行条件に基づく判定
- **対応条件**:
  1. **日次業務**
     - 暦日指定（毎日）
     - 営業日指定（営業日のみ）

  2. **週次業務**
     - 暦日(曜日)指定（特定の曜日）

  3. **月次業務**
     - 暦日(n日指定)（例：毎月10日）
     - 暦日(月末逆算)（例：月末、月末から3日前）
     - 営業日(n日指定)（例：毎月第3営業日）
     - 営業日(月末逆算)（例：毎月最終営業日）
     - 暦日(曜日)（例：毎月第2火曜日）

  4. **年次業務**
     - 特定月 + 上記の月次条件（例：3月の第2営業日）

- **追加条件判定**:
  - **有効期間**: 有効開始日～有効終了日の範囲内であることを確認
  - **オリジナル条件判定**: 振替先を判定するため、基本条件のみに基づいた判定も実施
  - **複合条件**: 複数の条件を組み合わせて（例：特定月の特定曜日）業務対象日を判定

### 振替規則処理機能

- **機能概要**: 非営業日に当たる業務の振替処理
- **振替規則**:
  - 直前営業日: 非営業日の場合、直前の営業日に振替
  - 直後営業日: 非営業日の場合、直後の営業日に振替
  - 振替しない: 非営業日でも振替を行わない
- **振替処理の方向**:
  1. 本来の実行日から振替日を算出（通常の条件判定）
  2. 振替先の日付から元の日付を検証（振替対象日判定）
     - これにより、もとの日付が検索対象外の場合でも振替が適切に処理される

### デバッグ機能

- **機能概要**: 処理中のログを記録
- **処理内容**:
  - デバッグシートの作成・管理
  - ログレベル（info/warning/error）に応じたログ記録
  - 処理の各ステップでの状態記録

## 処理フロー

### メイン処理フロー

```mermaid
flowchart TD
    A[開始] --> B[デバッグシート準備]
    B --> C[カレンダー情報取得]
    C --> D[業務スケジュールシート準備]
    D --> E[既存スケジュール情報取得]
    E --> F[入力日付取得・変換]
    F --> G[業務一覧データ取得]
    G --> H{同日のスケジュールが 既に存在するか?}
    H -- Yes --> I[警告表示して終了]
    H -- No --> J[業務データ処理開始]
    J --> K[業務ループ処理]
    K --> L{条件判定 isTargetTask}
    L -- 対象 --> M[スケジュールに追加]
    L -- 対象外 --> N{振替対象判定 isTargetDateForFurikae}
    N -- 振替対象 --> O[スケジュールに追加]
    N -- 振替対象外 --> P{全業務処理済み?}
    M --> P
    O --> P
    P -- No --> K
    P -- Yes --> Q[処理結果記録]
    Q --> R[終了]
```

### 業務条件判定フロー

```mermaid
flowchart TD
    A[業務条件判定開始 isTargetTask] --> B[有効期間チェック]
    B -- 期間外 --> C[対象外として終了]
    B -- 期間内 --> D{周期・頻度判定}
    
    D -- 日次 --> E{基準判定 isDailyTask}
    E -- 暦日 --> F[毎日対象]
    E -- 営業日 --> G{営業日判定 isBusinessDay}
    G -- Yes --> H[対象]
    G -- No --> I[対象外]
    
    D -- 週次 --> J{曜日判定 isWeeklyTask}
    J -- 一致 --> K[対象]
    J -- 不一致 --> L[対象外]
    
    D -- 月次 --> M{基準判定}
    M -- 暦日n日 --> N{日付一致判定 isCalendarDayNthTask}
    N -- 一致 --> O[対象]
    N -- 不一致 --> P[対象外]
    
    M -- 暦日月末逆算 --> Q{日付一致判定 isCalendarDayEndOfMonthTask}
    Q -- 一致 --> R[対象]
    Q -- 不一致 --> S[対象外]
    
    M -- 営業日n日 --> T{営業日・日付判定 isBusinessDayNthTask}
    T -- 一致 --> U[対象]
    T -- 不一致 --> V[対象外]
    
    M -- 営業日月末逆算 --> W{営業日・日付判定 isBusinessDayEndOfMonthTask}
    W -- 一致 --> X[対象]
    W -- 不一致 --> Y[対象外]
    
    M -- 暦日曜日 --> Z{曜日・週番号判定 isCalendarDayWeekDayTask}
    Z -- 一致 --> AA[対象]
    Z -- 不一致 --> AB[対象外]
    
    D -- 年次 --> AC{月判定}
    AC -- 一致 --> AD[月次と同様の条件判定]
    AC -- 不一致 --> AE[対象外]
    
    F --> AF[判定結果を返却]
    H --> AF
    I --> AF
    K --> AF
    L --> AF
    O --> AF
    P --> AF
    R --> AF
    S --> AF
    U --> AF
    V --> AF
    X --> AF
    Y --> AF
    AA --> AF
    AB --> AF
    AD --> AF
    AE --> AF
    
    AF --> AG[終了]
```

### 振替規則処理フロー

```mermaid
flowchart TD
    A[振替規則処理開始 applyFurikaeRule] --> B{振替規則の確認}
    B -- 振替しない --> C[元の日付をそのまま返却]
    B -- 直前営業日 --> D{対象日は営業日か? isBusinessDay}
    D -- Yes --> E[元の日付をそのまま返却]
    D -- No --> F[直前の営業日を検索 getPreviousBusinessDay]
    F --> G[直前営業日を返却]
    
    B -- 直後営業日 --> H{対象日は営業日か? isBusinessDay}
    H -- Yes --> I[元の日付をそのまま返却]
    H -- No --> J[直後の営業日を検索 getNextBusinessDay]
    J --> K[直後営業日を返却]
    
    C --> L[終了]
    E --> L
    G --> L
    I --> L
    K --> L
```

### 振替先の日付を検証するフロー

```mermaid
flowchart TD
    A[振替対象日判定開始 isTargetDateForFurikae] --> B{対象日は営業日か?}
    B -- No --> C[振替先になり得ないため対象外]
    B -- Yes --> D{振替規則の確認}
    D -- 振替しない --> E[対象外]
    D -- 直前営業日 --> F[翌日以降の非営業日で 業務条件に一致する日を検索]
    F --> G{条件一致する非営業日あり?}
    G -- No --> H[対象外]
    G -- Yes --> I{その直前営業日が対象日と一致?}
    I -- No --> J[対象外]
    I -- Yes --> K[振替対象として対象]
    
    D -- 直後営業日 --> L[前日以前の非営業日で 業務条件に一致する日を検索]
    L --> M{条件一致する非営業日あり?}
    M -- No --> N[対象外]
    M -- Yes --> O{その直後営業日が対象日と一致?}
    O -- No --> P[対象外]
    O -- Yes --> Q[振替対象として対象]
    
    C --> R[終了]
    E --> R
    H --> R
    J --> R
    K --> R
    N --> R
    P --> R
    Q --> R
```

## 型定義

システムで使用される主要な型定義：

```typescript
/**
 * カレンダー情報を保持する型
 * 日付文字列をキー、営業日フラグを値とするマップ
 */
type CalendarInfo = { [date: string]: boolean };

/**
 * 営業日情報を表す型
 */
type BusinessDayInfo = {
  nth: number;      // 月内での営業日番号（1始まり）
  total: number;    // 月内の営業日総数
  reverse: number;  // 月末から数えた営業日番号（最終営業日=0）
};

/**
 * 日付情報を表す型
 */
type DateInfo = {
  year: number;     // 年
  month: number;    // 月（1-12）
  day: number;      // 日
  dayOfWeek: number; // 曜日（0:日, 1:月, ..., 6:土）
  isBusinessDay: boolean; // 営業日かどうか
};
```

## 関数仕様

システムの主要関数は以下の通りです（抜粋）：

### メイン関数

- `main(workbook: ExcelScript.Workbook, targetDate: string = "")`: スクリプトのエントリーポイント。業務スケジュールの生成を実行します。

### 日付処理関数

- `excelDateToString(excelDate: string | number | Date | boolean): string`: Excel形式の日付を標準的な日付文字列（YYYY-MM-DD形式）に変換します。
- `formatDate(date: Date): string`: 日付オブジェクトをYYYY-MM-DD形式の文字列に変換します。
- `formatDateBySlash(date: Date): string`: 日付オブジェクトをYYYY/MM/DD形式の文字列に変換します。
- `getDateInfo(dateStr: string, calendar: CalendarInfo): DateInfo`: 日付文字列から日付の詳細情報を取得します。

### 営業日判定関数

- `isBusinessDay(inputDate: string, calendar: CalendarInfo): boolean`: 指定日が営業日かどうかを判定します。
- `getBusinessDayInfo(inputDate: string, calendar: CalendarInfo): BusinessDayInfo`: 指定日の営業日情報を取得します。
- `getPreviousBusinessDay(inputDate: string, calendar: CalendarInfo, workbook: ExcelScript.Workbook): string`: 指定日の直前営業日を取得します。
- `getNextBusinessDay(inputDate: string, calendar: CalendarInfo, workbook: ExcelScript.Workbook): string`: 指定日の直後営業日を取得します。
- `getNthBusinessDayOfMonth(year: number, month: number, n: number, calendar: CalendarInfo, workbook: ExcelScript.Workbook): string`: 指定月のn営業日目の日付を取得します。
- `getReverseNthBusinessDayOfMonth(year: number, month: number, n: number, calendar: CalendarInfo, workbook: ExcelScript.Workbook): string`: 指定月の末日からn営業日前の日付を取得します。

### 業務条件判定関数

- `isTargetTask(inputDate: string, taskData: (string | number | boolean)[], headers: string[], calendar: CalendarInfo, workbook: ExcelScript.Workbook): boolean`: 指定された業務が対象日に実行すべきかを判定します。
- `isDailyTask(inputDate: string, base: string, calendar: CalendarInfo): boolean`: 日次業務の判定を行います。
- `isWeeklyTask(inputDate: string, base: string, youbi: string): boolean`: 週次業務の判定を行います。
- `isCalendarDayNthTask(inputDate: string, n: number, furikae: string, calendar: CalendarInfo, workbook: ExcelScript.Workbook): boolean`: 暦日(n日指定)の判定を行います。
- `isCalendarDayEndOfMonthTask(inputDate: string, n: number, furikae: string, calendar: CalendarInfo, workbook: ExcelScript.Workbook): boolean`: 暦日(月末逆算)の判定を行います。
- `isBusinessDayNthTask(inputDate: string, n: number, calendar: CalendarInfo, workbook: ExcelScript.Workbook): boolean`: 営業日(n日指定)の判定を行います。
- `isBusinessDayEndOfMonthTask(inputDate: string, n: number, calendar: CalendarInfo, workbook: ExcelScript.Workbook): boolean`: 営業日(月末逆算)の判定を行います。
- `isCalendarDayWeekDayTask(inputDate: string, youbi: string, weekNum: number, furikae: string, calendar: CalendarInfo, workbook: ExcelScript.Workbook): boolean`: 暦日(曜日)の判定を行います。

### 振替規則処理関数

- `applyFurikaeRule(inputDate: string, rule: string, calendar: CalendarInfo, workbook: ExcelScript.Workbook): string`: 振替規則を適用して日付を計算します。
- `isTargetDateForFurikae(inputDate: string, taskData: (string | number | boolean)[], headers: string[], calendar: CalendarInfo, workbook: ExcelScript.Workbook): boolean`: 指定日が他の日付の振替先になっているかを判定します。

### ユーティリティ関数

- `prepareCalendarMap(workbook: ExcelScript.Workbook): CalendarInfo`: カレンダーデータを準備します。
- `getScheduleSheet(workbook: ExcelScript.Workbook)`: 業務スケジュールシートを準備します。
- `getExistingScheduleInfo(scheduleSheet: ExcelScript.Worksheet): { lastRowIndex: number, maxScheduleId: number }`: 既存のスケジュールデータの最終行と最大スケジュールIDを取得します。
- `debugLog(workbook: ExcelScript.Workbook, message: string, level: "info" | "warning" | "error" = "info"): void`: デバッグログを記録します。
- `create_debug_sheet(workbook: ExcelScript.Workbook, isClear = false)`: デバッグシートを作成します。

### エラー処理

システムは以下のエラー処理を実装しています：

1. **例外捕捉**: 主要な関数では例外をtry-catch文で捕捉し、エラー情報をデバッグログに記録
2. **エラーメッセージ**: 重要なエラーはエラーメッセージとして表示
3. **回復処理**: 一部のエラー（テーブル取得エラーなど）では代替手段での処理を試行
   - テーブルからのデータ取得失敗時にシートから直接取得を試行
   - 日付変換に複数の形式対応
4. **入力検証**: 日付の形式検証や有効性確認を実施
5. **デフォルト値**: エラー時には安全なデフォルト値を使用
6. **デバッグログ**: すべての主要な処理ステップとエラーを詳細にログに記録

## 拡張性と制限事項

### 拡張性

- **新しい業務条件**: 周期・頻度や基準の種類を追加することで、新しい業務条件への対応が可能
- **追加フィールド**: 業務一覧やスケジュールテーブルに新しいフィールドを追加可能
- **処理カスタマイズ**: 各種判定ロジックはモジュール化されており、カスタマイズが容易
- **将来の拡張可能性**:
  - **作業者マスター**: 担当者情報を管理するテーブルを追加して作業者の割り当て機能
  - **Power BI連携**: 生成されたスケジュールデータをPower BIで可視化
  - **複数条件対応**: 「第1と第3金曜日」のような複合条件への対応
  - **繰り返し情報**: 年次・月次等の識別情報をスケジュールに追加
  - **パフォーマンス指標**: 作業時間や遅延率などのKPI測定

### 制限事項

- **日付範囲**: カレンダーテーブルに登録されている日付範囲内でのみ正確に動作
- **テーブル構造**: 想定されるテーブル構造（列名など）に依存
- **処理速度**: 大量の業務データや長期間のスケジュール生成では処理時間が増加する可能性
- **非営業日判定**: カレンダーテーブルの営業日フラグに依存
- **既知の制限**:
  - 複数の条件を組み合わせた業務（例: 第1と第3金曜日）は別々のレコードとして登録が必要
  - 非定型的な業務（特定の日付のみ、不規則なパターンなど）への対応には個別レコードが必要
  - 依存関係のある業務（Aの完了後にBを実行など）のスケジューリングには非対応

## 運用方法

### 前提条件

- Excelファイルには、カレンダー、業務一覧、スケジュールの3つのシートが必要
- カレンダーデータは最新の休日情報で更新されていること
- 業務一覧には有効な業務データが登録されていること

### 使用手順

1. **準備**:
   - Excelファイルを開く
   - 必要に応じてカレンダーデータや業務データを更新

2. **スケジュール生成**:
   - 「業務スケジュール」シートのB1セルに対象日付を入力
   - スクリプトを実行

3. **結果確認**:
   - 生成されたスケジュールを確認
   - 必要に応じてデバッグログを確認

4. **カスタマイズ**:
   - 業務一覧を編集して対象業務を追加・変更
   - 振替規則や条件を調整

### 使用例

#### 例1: 月初の業務スケジュール生成
```
// B1セルに「2025-05-01」を入力
// スクリプトを実行
// → 月初に実行すべき業務（毎日業務、営業日業務、月初業務、第1営業日業務など）が抽出される
```

#### 例2: 月末の業務スケジュール生成
```
// B1セルに「2025-05-31」を入力
// スクリプトを実行
// → 月末に実行すべき業務（毎日業務、営業日業務、月末業務、最終営業日業務など）が抽出される
```

#### 例3: 連休前の一括スケジュール生成
```
// 連休期間の各日付を順に入力し、それぞれに対してスクリプトを実行
// → 連休期間中の全ての業務が抽出され、必要に応じて営業日に振り替えられる
```