---
# System prepended metadata

title: ADR-TVEDB-001

---

# ADR-TVEDB-001

**日期**

2024/07/20

**決議**

通過

**背景**

目前為協助技專校務資料庫系統升級至.net跨平台版本(目前為net8)，現今系統版本為ASP，需進行改進原因如下：

- 系統目前仍在新增需求與維護，並非凍結現有功能，仍需持續使用
- 目前系統版本開始與現今作業系統、工具脫節，在安全性與新增功能皆有疑慮

困境

- 系統作業期間為每年2個階段，約為三月與九月，造成維護與系統遷移會有重疊情形，使得新系統與舊系統需雙軌存在
- 因表冊眾多，目前系統人員為三人且分別需針對各自業務進行維護，無法一次性開發所有表冊並進行更換且新系統在上線前未曾有真正上線實務使用。若直接開發完整系統，即便有完善的測試，但在新舊系統切換仍有巨大的風險；若發生錯誤時，作業期可能需要面對所有學校在不同表冊上發生bug而無法使用，湧入大量電話使得系統人員需當下選擇立即修復新系統的壓力或新系統下架，復原至舊系統

```mermaid
gantt
  title 技專作業期間
    dateFormat Q
    axisFormat %m
  
  section 系統維運
  下學期:03, 90d
  上學期:09, 90d

  section 報表產出
  下學期: 05-, 45d
  上學期: 11, 45d
  
  section 報表需求與改善
  下學期: 12, 90d
  上學期: 06, 90d
```

**決策與解決**

```mermaid
gantt
  title 技專作業期間
    dateFormat Q
    axisFormat %m
  
  section 系統維運
  下學期:03, 90d
  上學期:09, 90d

  section 報表產出
  下學期: 05-, 45d
  上學期: 11, 45d
  
  section 報表需求與改善
  下學期: 12, 90d
  上學期: 06, 90d

  section 系統升級
  下學期: 04, 120d
  上學期: 09, 120d
```


避免上述情況發生，採用Strangler Pattern，逐一將表冊與功能取代，以API介接現有系統，先進行backend API進行開發與取代，最後舊系統(ASP)僅存view,前端操作功能，最後再進行前端開發更換

- 初期先針對表冊進行更換，有關帳號權限與管控仍有現有系統負責，API服務為內部服務不開放且限制只有現有系統開放呼叫
    - 各類表冊
    - 內部系統功能
    - 權限控管(解決狀態共享)，後期置換前端時前置作業
    - Proxy功能，透過代理轉接使新舊前端可逐一切換
- 逐步開發更換，儘量避免與新需求報表重疊，更換後進行版本標記，原有功能列為備用，維運正常後同報表發生新需求則僅在新系統上維護，舊版本時效三個月即棄用並標記，避免雙軌同時作業，卻僅有舊系統在production env實際運行。

- 仍維持現有資料庫綱要設計，但業務模型會進行重構，透過中介層物件轉換原資料物件層
- 單元測試與整合測試，測試案例由技專資料庫系統人員提供與驗證

## 系統架構

**第一階段**

- 針對表冊開發內部API服務，逐漸取代舊有系統表冊
- internal API部份設定防火牆僅限內部IP訪問
- 權限與驗證仍透過舊系統負責

```mermaid
flowchart LR

    subgraph origin
    web[ASP] 
    Account[account mangement]
    end
    web --> |call| InternalAPI
    subgraph InternalAPI
    internalA[report1]
    internalB[report2]
    internalC[more]
    end
```

**第二階段**

- 完成Identity API，解決狀態共享
- 逐漸將權限與驗證功能移至Identity API集中管理
- 內部功能移出舊系統

```mermaid
flowchart TD

    subgraph origin
    web[ASP] 
    end
    web --> |call| internal
    web --> |call| AnotherBackend
    subgraph IdentityServer
    Account[account mangement]
    end
    subgraph internal
    internalA[report1]
    internalB[report2]
    internalC[more]
    end
    subgraph AnotherBackend
    review
    user
    usergroup
    more...
    end
    origin --- |request & response| IdentityServer
    internal ---  |request & response| IdentityServer
    AnotherBackend ---  |request & response| IdentityServer
```

**第三階段**

- 完成Identity API前置作業後，導入Proxy機制
- 進行新系統前端開發，統一使用Internal API
- 新表冊透過Proxy轉址至新系統介面，其他仍維持導向舊系統介面

```mermaid
flowchart LR

    subgraph origin
    web[ASP] 
    end
    client
    client --- |request & response| proxy
    proxy --> |reroute| origin
    proxy --> |reroute| new
    subgraph new
    frontend
    end
    frontend --> |call| internal
    frontend --> |call| AnotherBackend
    web --> |call| internal
    web --> |call| AnotherBackend
    subgraph IdentityServer
    Account[account mangement]
    end
    subgraph internal
    internalA[report1]
    internalB[report2]
    internalC[more]
    end
    subgraph AnotherBackend
    review
    user
    usergroup
    more...
    end
    new --- |request & response| IdentityServer
    origin --- |request & response| IdentityServer
    internal ---  |request & response| IdentityServer
    AnotherBackend ---  |request & response| IdentityServer
```

**最終階段**

- 完成系統遷移後，移除舊系統
- 表冊API提供報表部份對外開放，提升與外部系統的資料交換效率，減少系統人員報表維運工作量。

```mermaid
flowchart LR

    client
    client --- |request & response| proxy
    proxy --> |reroute| new
    proxy --> |open api| internal
    subgraph new
    frontend
    end
    frontend --> |call| internal
    frontend --> |call| AnotherBackend
    subgraph IdentityServer
    Account[account mangement]
    end
    subgraph internal
    internalA[report1]
    internalB[report2]
    internalC[more]
    end
    subgraph AnotherBackend
    review
    user
    usergroup
    more...
    end
    new --- |request & response| IdentityServer
    internal ---  |request & response| IdentityServer
    AnotherBackend ---  |request & response| IdentityServer
```

## 職責說明與步驟

### 步驟

1. 特定單張表冊
2. 協助技專系統人員建立測試案例，後續需可自行撰寫
3. 協助技專系統人員遵循範例開發表冊並依照實際反應調整系統結構

### 權責

- 前期階段
    - 技專
提供表冊業務邏輯
審查測試案例是否符合需求
提供前端畫面UI，與操作順序與畫面功能需求，配合API 設計

    - 嘉揚
建立API Specification與建置Mock API Server
建立表冊範例物件與開發規範
建立單元測試


- 中期階段
    - 技專
新增測試分支，修改舊系統UI操作，轉移呼叫mock API，測試業務行為是否符合需求
每個系統人員依照開發規範撰寫表冊物件(含測試)

    - 嘉揚
協助技專系統人員自行開發表冊物件，並適時調整基礎類別提供系統人員使用
API實際串接舊系統UI，並使用假資料進行測試

## API Design

1. 以業務本身流程與行為設計，而不是CRUD，ex.如下圖所示 

- 如果是操作行為、命令會在url的後方加  : {action}，如查詢https://test/api/v1/report/categories/學生類/StatStudent:filter
- 一般取得屬性或唯讀資料，會以URL的方式做為標識符，如 https://test/api/v1/report/categories/學生類/StatStudent/headers
- API接管UI所需要的資料的產生，UI會只單純負責畫面上的更新、操作(著重在JS、CSS)

2. 確認 API request and response json structure
3. 建立 mock API server ，提供舊有系統測試連接，以利後續移轉
4. 實作 Report API
5. 測試回應結果與行為
6. 舊系統特定表冊改使用API，提供使用者操作

### 表冊狀態變化圖-範例

```mermaid
stateDiagram-v2
    [*] --> Initial
    Initial --> Upload
    Upload --> InReview
    InReview --> Pending
    Pending --> Upload
    InReview --> Approval
    Approval --> Close
    Close --> [*]
```

### API Specification 範例

| **Entity** | **URL**                                           | **HTTP Action** | **Return Type**                    | **Status**                                                                 | **Description**                                                                                                               |
|------------|---------------------------------------------------|-----------------|------------------------------------|-----------------------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------|
| Report     | `/v1/reports/{reportId}:submitUpload`             | POST            | JSON object of updated `Report`    | 200 OK<br>400 Bad Request<br>404 Not Found<br>401 Unauthorized              | Moves the `Report` from **Initial** to **Upload** (or re-uploads, if allowed).                                               |
| Report     | `/v1/reports/{reportId}:requestReview`            | POST            | JSON object of updated `Report`    | 200 OK<br>400 Bad Request<br>404 Not Found<br>401 Unauthorized              | Moves the `Report` from **Upload** to **InReview** (after the university finishes data input).                                |
| Report     | `/v1/reports/{reportId}:requestApproval`          | POST            | JSON object of updated `Report`    | 200 OK<br>400 Bad Request<br>404 Not Found<br>401 Unauthorized              | Moves the `Report` from **InReview** to **Approval**, indicating it has been fully reviewed.                                 |
| Report     | `/v1/reports/{reportId}:close`                    | POST            | JSON object of updated `Report`    | 200 OK<br>400 Bad Request<br>404 Not Found<br>401 Unauthorized              | Moves the `Report` from **Approval** to **Close**, marking the workflow complete.                                            |
| Report     | `/v1/reports/{reportId}:requestPending`           | POST            | JSON object of updated `Report`    | 200 OK<br>400 Bad Request<br>404 Not Found<br>401 Unauthorized              | Moves the `Report` from **InReview** to **Pending** (allows additional modifications).                                       |
| Report     | `/v1/reports/{reportId}:returnToUpload` (Pending) | POST            | JSON object of updated `Report`    | 200 OK<br>400 Bad Request<br>404 Not Found<br>401 Unauthorized              | Moves the `Report` from **Pending** back to **Upload** state (if more updates are required from the university).             |


### response format example

- 標題列
![標題列](https://hackmd.io/_uploads/ByXWQ0vHJe.jpg)


- 統計說明
![統計說明](https://hackmd.io/_uploads/SyUb7Cwr1l.jpg)

- 查詢條件
![查詢條件](https://hackmd.io/_uploads/rkyM7RPBkx.jpg)

- 查詢結果
![查詢結果](https://hackmd.io/_uploads/SkGzQAvHyx.jpg)

## Data change captured

採雙軌制，仍會儲存資料至技專原資料庫，另外寄送事件至kafka提供資訊公開測試用

1. 技專資料庫仍維持舊有資料表結構，配合event機制，新增Version欄位
2. Command處理完儲存至資料庫後，會傳遞事件(已完成的事實)至kafka，提供資料訊公開進行訂閱

以下為事件結構範例

```json
{
  "eventVersion": "v1",
  "eventId": "9f96e8f0-bd58-4dbb-9809-81e1310bf374",
  "eventTimestamp": "2024-12-24T10:15:30Z",
  "reportId": "report-123456",
  "reportState": "submitUpload",
  "completedAt": "2024-12-24T10:14:59Z",
  "payload": {
    "entityid":{},
    "version":{},
    "year": {},
    "semester": {},
    "universityid": {},
    "universityname": {},
    "departmentid": {},
    "departmentname":{},
    "educationsystemid":{},
    "educationsystemname":{},
    "detail":[
        {
            "gender":{},
            "grade":{},
            "count":{}
        },
        {
            "gender":{},
            "grade":{},
            "count":{}
        }
    ]
  }
}

```

## 系統設計

### 專案資料夾結構說明

- Application ：應用層
  
   > 主要提供Controller或頁面功能使用，為業務模型與基礎資料庫結合

  - Contract
  
   > API Request 與 API Reponse 訊息結構

  - Command
  
   > 執行命令，結合業務物件與資料存取物件完成完整業務行為

  - Dto
   
   > 資料轉換物件，做為資料庫與物件的中介物件，配合AutoMapping轉換

   - Queries

   > 無副作用，唯讀的read model，通常為報表

  - Service
    
   > 特定業務處理服務，但未有生命週期或狀態變化，例如牽涉複雜資料計算或轉換

- Domain ：業務模型

  - Entities
    
    > 具有生命週期與狀態變化、行為的物件，部份待確定
  
  - Primitive
    
    > 具有特定規格，自帶驗證與簡單行為的基礎物件，提供Entity組合所需要的屬性

  - Specification

    > 業務邏輯實作

  - Validator
    
    > 驗證器實作
    
- Infrastructure : 基礎功能物件

  > 基礎資料物件，與資料庫相關
  
  - DataAccess 

  > 過渡物件，透過物件將資料連接層與現行原始碼解耦利於撰寫測試

  - Repository

  > 資料存取物件，主要負責與資料庫存取，提供資料與物件之間的轉換

- Kernal : 核心基礎物件

    - Error

        > 收集物件錯誤訊息，並返回集合型別

    - Event

        > 事件註冊，主要配合DI與Command pattern來解耦不同業務行為相依關系

    - Result
     
        > 標準訊息回應物件，提供Controller 回應view 或API的行為結果

    - Seed

        > 基礎物件，提供Entity,ValueObject,AggregateRoot組合業務模型

    - Specification

        > 具現業務邏輯物件化，利單元測試，有別於一般欄位檢查，通常需要多物件複合狀態判斷，屬於核心業務運行主要規則

    - Validator

        > 抽象驗證器物件，提供實作業務物件驗證器，統一集中執行物件驗證

### 軟體架構


```mermaid
flowchart TB
    Frontend["ASP"] -- http reqeust and response --> API["API Specification"]
    Controllers -- implement --> API
    subgraph Backend
        Controllers
        subgraph Controllers
            direction TB
            AnyReport
        end
        Controllers -- express--> Contracts
        Controllers -- create --> Command
        Queries -- read --> Controllers
        subgraph Applications
            direction LR
            subgraph Contracts
                Response
                Request
            end
            Command -- delivery parameter-->CommandHandler
            subgraph Commands
                Command
                CommandHandler
            end
            Queries
        end
        CommandHandler -- use --> DomainModel
        CommandHandler -- use --> Repository
        CommandHandler -- use --> Queries
        CommandHandler -- return result--> Controllers
        CommandHandler -- use --> DomainService
        subgraph DomainService
            SomeService
        end
        subgraph DomainModel
            XXXReport
        end
        subgraph Infrastructure
            direction TB
            Context
            Repository -- access --> Mapper
            Mapper -- mapping --> Context
            Repository
        end
        Context -- Entity changed -->DataBase
    end
    DataBase -- no side effect query --> Queries
```

### 程式實作範例

以下片段程式並非連貫一致性，僅呈現實作概念，提供系統人員了解實作細節

- 將有標準規範的基礎欄位實作為Primitive物件，統一管理與自有驗證，提供表冊組合使用

```csharp
 public class AcademicYear:ValueObject
 {
     public int Value { get; private set; }

     protected AcademicYear() { }

     protected AcademicYear(int value)
     {
         Value = value;
     }

     public static AcademicYear NoYear() => new AcademicYear(-1);

     public static AcademicYear FromValue(int value) => new AcademicYear(value);

     public static AcademicYear FromValue(string value)
     {
         string pattern = @"^\d+$";
         if (!Regex.IsMatch(value, pattern))
             throw new ArgumentException(nameof(value), "學年度為數字格式，請確認欄位值");
         return new AcademicYear(int.Parse(value));
     }

     public IEnumerable<ValidationResult> Validate()
     {
         var result = new List<ValidationResult>();
         if (Value < 0)
             result.Add(ValidationResult.CreateNew(nameof(Male), "數字不可小於0"));
         return result;
     }

     public override IEnumerable<object> GetAtomicValues()
     {
         yield return Value;
     }

     public static implicit operator int (AcademicYear year) => year.Value;
 }
```


- 實作表冊模型，透過不同primitive object組合
```csharp
public enum SituationType
{
    None,Interscholastic,Minor,DoubleMajor,Coures
}
public class StudySituation
{
    public Period Period { get; private set; }
    public UniversityInfo University { get; private set; }
    public AcademicDepartment Department { get; private set; }
    public EduSystem EduSystem { get; private set; }
    public Gender Gender { get; private set; }
    public IEnumerable<Situation> Situations => _situations.AsReadOnly();
    private List<Situation> _situations;

    protected StudySituation()
    {
        _situations = new List<Situation>();
    }
    
    internal StudySituation(Period period
        ,UniversityInfo university
        ,AcademicDepartment department
        ,EduSystem eduSystem
        ,Gender gender
        ):this()
    {
        Period = period;
        University = university;
        Department = department;
        EduSystem = eduSystem;
        Gender = gender;
    }

    public static StudySituation CreateNew(Period period
        , UniversityInfo university
        , AcademicDepartment department
        , EduSystem eduSystem
        , Gender gender) => new StudySituation(period, university, department, eduSystem, gender);

    public void AddSituation(SituationType type,string count )
    {
        _situations.Add(Situation.CreateNew(type, int.Parse(count)));
    }
}
```

- 實作表冊特定業務邏輯

```csharp
/// <summary>
    /// 專科以上學校，新聘與廢止需填寫校內審查程序
    /// </summary>
    public class InternalReviewProcessSpecification : ISpecification<WorkPermit>
    {
        public ErrorCollection<WorkPermit> ValidateResult { get; private set; }

        public InternalReviewProcessSpecification()
        {
            ValidateResult = new ErrorCollection<WorkPermit>();
        }

        public bool IsSatisfiedBy(WorkPermit entity)
        {
            var application = entity.WorkInformation.EmploymentType.Id;
            var isNewHireOrCancellation = application == ApplicationType.NewHire || application == ApplicationType.Cancellation;

            if (isNewHireOrCancellation is false) return true;
            if (entity.WorkInformation.Employer.UserGroups.Id != UserGroupId.JuniorCollegeOrAbove) return true;

            var internalReviewProcess = entity.WorkInformation.InternalReviewProcess;
            var isEmptyInternalReviewProcess = string.IsNullOrWhiteSpace(internalReviewProcess);
            var WorkPermitType = entity.WorkInformation.EmploymentType.Value;

            if (isEmptyInternalReviewProcess)
            {
                switch (WorkPermitType)
                {
                    case "新聘": ValidateResult = ErrorResult.Validation(nameof(entity.WorkInformation.InternalReviewProcess), "校內審查程序不可空白"); break;
                    case "廢止": ValidateResult = ErrorResult.Validation(nameof(entity.WorkInformation.InternalReviewProcess), "法源依據不可空白"); break;
                }
                return false;
            }

            if (isEmptyInternalReviewProcess is false && Regex.IsMatch(internalReviewProcess, RegexPattern.ContentPattern(0, 500)) is false)
            {
                switch (WorkPermitType)
                {
                    case "新聘": ValidateResult = ErrorResult.Validation(nameof(entity.WorkInformation.InternalReviewProcess), "校內審查程序:請輸入500字內說明"); break;
                    case "廢止": ValidateResult = ErrorResult.Validation(nameof(entity.WorkInformation.InternalReviewProcess), "法源依據:請輸入500字內說明"); break;
                }
                return false;
            }

            return true;
        }
    }
```

- 實作表冊驗證器，統一收集與驗證表冊資料狀態與業務邏輯

```csharp
 public class WorkPermitValidator : IValidator<WorkPermit>
    {
        public Dictionary<string, ISpecification<WorkPermit>> Specifications { get; }

        public WorkPermitValidator()
        {
            Specifications = new Dictionary<string, ISpecification<WorkPermit>>
            {
                //專科以上或僑民學校，聘僱類別不可空白或無效格式
                {nameof(AppointmentCategorySpecification), new AppointmentCategorySpecification()},
                //聘僱開始日期不可大於或等於聘僱結束日期
                {nameof(EmploymentDateSpecification), new EmploymentDateSpecification()},
                //案件申請原因不可空白，續聘可空白
                {nameof(ReasonForApplicationSpecification), new ReasonForApplicationSpecification()},
                //專科以上學校，新聘與廢止需填寫校內審查程序
                {nameof(InternalReviewProcessSpecification), new InternalReviewProcessSpecification()},
                //教師年齡不得小於20歲，大於100歲
                {nameof(InstructorAgeLimitSpecification), new InstructorAgeLimitSpecification()},
                //聘僱開始日期不可小於開始申請的日期
                {nameof(AppointmentEmploymentStartDateSpecification), new AppointmentEmploymentStartDateSpecification()},
                //高中以下學校續聘時，聘僱開始日期應為上次聘用日隔日
                {nameof(HighSchoolReappointmentDateSpecification), new HighSchoolReappointmentDateSpecification()},
                //高中以下一般學校引進方式不可空白或無效格式
                {nameof(IntroductionMethodSpecification), new IntroductionMethodSpecification()},
                //申請廢止應填最後實際聘僱日期，其區間應小於前核定日期，起始日或結束日應有一個與前核定日期相同
                {nameof(CancellationDateRangeSpecification), new CancellationDateRangeSpecification()},

            };
        }

        public IEnumerable<IErrorResult> Validate(WorkPermit entity)
        {
            var employer = entity.WorkInformation.Employer;
            var errors = new List<IErrorResult>();
            errors.AddRange(entity.WorkPermitNo.Validate());
            errors.AddRange(entity.Type.Validate());
            errors.AddRange(entity.Process.Validate());

            errors.AddRange(entity.WorkInformation.EmploymentType.Validate());
            errors.AddRange(entity.WorkInformation.EmploymentCategory.Validate());

            errors.AddRange(entity.WorkInformation.PersonalDataConsent.Validate());
            errors.AddRange(employer.Validate());
            errors.AddRange(entity.WorkInformation.Instructor.Validate());
            errors.AddRange(entity.WorkInformation.Comments.Validate());

            foreach (var specification in Specifications.Values)
            {
                if (specification.IsSatisfiedBy(entity) is false)
                    errors.AddRange(specification.ValidateResult.Errors);
            }

            return errors;
        }
    }
```

- 實作表冊管理物件，控制表冊生命週期與狀態變化

```csharp
public class StudySituationReport : AbstractReport<StudySituation>
{
    internal StudySituationReport(string dataSource, string trxDate) : base(dataSource, trxDate)
    {
    }

    public static StudySituationReport CreateNew(string dataSource, string trxDate)
        => new StudySituationReport(dataSource, trxDate);
}
```

- 實作資料存取物件

```csharp
public class WorkPermitInformationRepository : GenericRepository<CaseInformation>, IWorkPermitInformationRepository
{
    public WorkPermitInformationRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
    {
    }

    public async Task<Option<WorkPermitInformation>> Get(string caseId)
    {
        string Sql = @"select 
        ci.CA_guid as CaseId,
        ci.CI_guid as Id,
        ci.CI_type as CaseType,
        ci.AD_user as CreatedBy,
        ci.UP_user as ModifiedBy,
        ci.UP_time as ModifiedDate,
        ci.UP_from as ModifiedFrom
        from D_Case_Application ca
        inner join D_Case_Information ci on ca.CI_guid=ci.CI_guid
        where ci.CA_guid=@caseId";
        var dynamicParameters = new DynamicParameters();
        dynamicParameters.Add("@caseId", caseId);
        var caseInformatioonOption = await Find(Sql, dynamicParameters);
        return caseInformatioonOption.Match(
                       Some: caseInformation =>
                       {
                           return Option<WorkPermitInformation>.Some(
                               WorkPermitInformation.FromString(
                                   caseInformation.Id.ToString(),
                                   caseInformation.CaseType,
                                   caseInformation.CreatedBy.ToString(),
                                   caseInformation.ModifiedBy.ToString(),
                                   caseInformation.ModifiedDate.ToString("yyyy-MM-dd HH:mm:ss.fff"),
                                   caseInformation.ModifiedFrom));
                       },
                       None: () => { return Option<WorkPermitInformation>.None; });
        }

    public async Task<int> UpdateAsync(WorkPermit @case, string lastModifiedDate)
    {
        string sql = @"update D_Case_Information 
            set CP_guid=@processId,
                UP_user=@modifiedBy,     
                UP_time=@modifiedDate
            where CI_guid=@informationId and UP_time=@lastModifiedDate";
        var dynamicParameters = new DynamicParameters();
        dynamicParameters.Add("@processId", @case.ProcessHistory.ProcessId);
        dynamicParameters.Add("@modifiedBy", Guid.Empty);
        dynamicParameters.Add("@modifiedDate", @case.Information.ModifiedDate);
        dynamicParameters.Add("@informationId", @case.Information.Id);
        dynamicParameters.Add("@lastModifiedDate", lastModifiedDate);
        var affected = await ExecuteAsync(sql, dynamicParameters);
        return affected;
    }
}
```

- 實作業務行為

```csharp
public class ReturnOutOfDateAwaitingDocumentsCommand : IEventRequest
{
    /// <summary>
    /// 案件編號
    /// </summary>
    public string CaseId { get;}
    /// <summary>
    /// 修改人員
    /// </summary>
    public string ModifiedBy { get; }
    /// <summary>
    /// 修改來源
    /// </summary>
    public string ModifiedFrom { get; }
    /// <summary>
    /// 逾期通知日
    /// </summary>
    public DateTime NotifyDate { get; }
    /// <summary>
    /// 退件截止日
    /// </summary>
    public DateTime ReturnDate { get; }

    public string Email { get; }
    public ReturnOutOfDateAwaitingDocumentsCommand(
        string caseId,
        string modifiedBy,
        string modifiedFrom,
        DateTime notifyDate,
        DateTime returnDate,
        string email)
    {
        CaseId = caseId;
        ModifiedBy = modifiedBy;
        ModifiedFrom = modifiedFrom;
        NotifyDate = notifyDate;
        ReturnDate = returnDate;
        Email = email;
    }
}

public class ReturnOutOfDateAwaitingDocumentsCommandHandler :
    IRequestHandler<ReturnOutOfDateAwaitingDocumentsCommand,OperationResult>
{
    private readonly ICaseManagementContext _caseManagement;
    private readonly IEventBus _eventBus;
    public ReturnOutOfDateAwaitingDocumentsCommandHandler(
        ICaseManagementContext caseManagement, IEventBus eventBus)
    {
        _caseManagement = caseManagement;
        _eventBus = eventBus;
        
    }

    public async Task<OperationResult> Handle(
        ReturnOutOfDateAwaitingDocumentsCommand request,
        CancellationToken cancellationToken = default)
    {
        var test = await request.RetrieveFromAsync(x=>x.CaseId,_caseManagement.GetCaseAsync);
        List<IErrorResult> results = new List<IErrorResult>();

        var @case = await _caseManagement.GetCaseAsync(request.CaseId);

        if (@case.IsNone)
            return OperationResult.ProcessFailed(
                @case,
                WorkPermitErrors.CaseErrors.CaseInformationIsEmpty.Description,
                new List<IErrorResult>{ErrorResult.NotFound(WorkPermitErrors.CaseErrors.CaseInformationIsEmpty.Code, WorkPermitErrors.CaseErrors.CaseInformationIsEmpty.Description) }
                );

        var existedCase = @case.ValueUnsafe();

        // 逾期通知日
        // to be expire
        var _awaitingDocumentExpirationSpec = new AwaitingDocumentExpirationSpecification(DateTime.Now, request.NotifyDate, request.ReturnDate);
        if (_awaitingDocumentExpirationSpec.IsSatisfiedBy(existedCase) is false)
        {
            SendDateExpirationNotification(existedCase, request.Email);
            return OperationResult.ProcessSucceed(@case, "寄送逾時通知",Enumerable.Empty<IErrorResult>());
        }
            
        // 退件截止日
        // over due
        if (CustomDate.IsGreater(DateTime.Now, request.ReturnDate))
        {
            var result = await ProcessCaseUpdateIfNeeded(request, existedCase);
            results.AddRange(result);
            SendReturnedNotification(existedCase, request.Email);
        }

        return results.Any() ? 
            OperationResult.ProcessFailed(@case, WorkPermitErrors.CaseErrors.ReturnedError.Description, results.AsEnumerable()) : OperationResult.ProcessSucceed(@case,"退件成功", Enumerable.Empty<IErrorResult>());
    }

    private async Task<IEnumerable<IErrorResult>> ProcessCaseUpdateIfNeeded(ReturnOutOfDateAwaitingDocumentsCommand request, WorkPermit @case)
    {
        var lastModifiedDate = @case.Information.ModifiedDate;
        var result = @case.ReturnAwaitingDocuments(request.ModifiedBy, request.ModifiedFrom);
        if (!result.Any())
        {
            await _caseManagement.UpdateCaseAsync(
                @case, 
                lastModifiedDate);
        }
        return result;
    }

    private void SendDateExpirationNotification(WorkPermit @case, string email)
    {
        var notification = new ExpirationNotificationIntegrationEvent(
                @case.CaseType,
                @case.CaseNo,
                email);
        _eventBus.PublishAsync(notification);
    }

    private void SendReturnedNotification(WorkPermit @case,string email)
    {
        var notification = new ReturnedNotificationIntegrationEvent(
                @case.CaseType,
                @case.CaseNo,
                email);
        _eventBus.PublishAsync(notification);
    }
}
```

- API 介面

```csharp
[Route("return:outofdate")]
[HttpPost]
[ProducesResponseType(typeof(ProcessResult), StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status204NoContent)]
[ProducesResponseType(StatusCodes.Status400BadRequest)]
public async Task<IActionResult> ReturnAsync(CancellationToken cancellationToken)
{
    var outOfDateAwaitingDocuments = await _caseQueryService.GetOutOfDateAwaitingDocumentsAsync();
    var processResults= new ProcessResult("待補件自動退件");

    if (outOfDateAwaitingDocuments.Any() is false)
        return Ok(processResults.WithUpdatedMessage("無任何補件資料"));

    foreach (var outOfDate in outOfDateAwaitingDocuments)
    {
        var result = await ProcessDocument(outOfDate, cancellationToken);
        processResults = processResults.AggregateResult(result);
    }
    if(processResults.IsSuccess)
        return Ok(processResults.WithUpdatedMessage("執行成功"));
    else
        return BadRequest(processResults.WithUpdatedMessage("執行失敗"));
}
```

### 單元測試

測試框架將採用以下技術
    - Xunit
    - NSubstitute

- primitive object

```csharp
public class GenderSpec
{
    [Theory]
    [InlineData("男","Male")]
    [InlineData("女","Female")]
    [InlineData("Male", "Male")]
    [InlineData("Female", "Female")]
    [InlineData("F", "Female")]
    [InlineData("M", "Male")]
    public void test_new_gender(string value,string id)
    {
        var gender = Gender.FromValue(value);

        Assert.Equal(id, gender.Id);
        Assert.Equal(value, gender.Value);
    }

    [Fact]
    public void test_gender_when_create_male()
    {
        var male = Gender.Male();

        Assert.Equal("男", male.Value);
        Assert.Equal("male", male.Id.ToLower());
    }

    [Fact]
    public void test_gender_when_create_female()
    {
        var female = Gender.Female();

        Assert.Equal("女", female.Value);
        Assert.Equal("female", female.Id.ToLower());
    }

    [Fact]
    public void test_error_when_gender_is_empty()
    {
        string fakeGender = string.Empty;
        var gender = Gender.FromValue(fakeGender);

        var errors = gender.Validate();

        Assert.True(errors.Count() > 0);
        Assert.Contains(errors, x => x.Message == "性別不可為空");
    }

    [Fact]
    public void test_error_when_gender_can_not_transfer_value()
    {
        string fakeGender = "-";
        var gender = Gender.FromValue(fakeGender);

        var errors = gender.Validate();

        Assert.True(errors.Count() > 0);
        Assert.Contains(errors, x => x.Message == "性別無法轉換為正確代碼");
    }
}
```

- 表冊模型

```csharp
public class StatTeacherReportSpec
{
    private IPeriodQueryService _periodQuery;

    private IUniversityQueryService _universityQuery;

    private IEduSystemQueryService _eduSystemQuery;

    private INationQueryService _nationQuery;

    public StatTeacherReport_Spec()
    {
        _periodQuery = new FakePeriodQueryService();
        _universityQuery = new FakeUniversityQueryService();
        _eduSystemQuery = new FakeEduSystemQueryService();
        _nationQuery = new FakeNationQueryService();
    }
    [Fact]
    public void test_new_stat_teacher_report()
    {
        int rowIndex = 1;
        var period = Period.FromString("109", "1", _periodQuery);
        var university = UniversityInfo.FromString("0001", _universityQuery);
        var department = AcademicDepartment.FromString("000001", "測試學系");
        var regular = EmploymentType.FromString("專任");
        var nation = Nation.FromString("1", "測試國");
        var organ = Organization.FromValue("編制內");
        var age = AgeGroup.FromValue("未滿25歲");
        var education = Education.FromValue("博士");
        var teacherType = TeacherType.FromValue("一般教師");
        var rank = ContractRank.FromValue("教授");
        var gender = Gender.FromValue("男");
        var count = Number.Create(Count.Parse("10"));

        var entity = StatTeacherIntegation.CreateNew(rowIndex
            , period, university, department,regular,
            nation, organ, age, education, teacherType, rank,
            gender, count);

        entity.Validate();

        Assert.NotNull(entity);
        Assert.True(entity.Errors.Count() == 0);
        Assert.Equal(rowIndex, entity.RowIndex);
        Assert.Equal(period, entity.Period);
        Assert.Equal(university, entity.University);
        Assert.Equal(department, entity.Department);
        Assert.Equal(regular, entity.Regular);
        Assert.Equal(nation, entity.Nation);
        Assert.Equal(organ, entity.Appointment);
        Assert.Equal(age, entity.Age);
        Assert.Equal(education, entity.Education);
        Assert.Equal(teacherType, entity.TeacherType);
        Assert.Equal(rank, entity.Rank);
        Assert.Equal(gender, entity.Gender);
        Assert.Equal(count, entity.Count);
    }
}
```

- 業務邏輯

```csharp
public class IntroductionMethodSpecificationTests
    {
        public IntroductionMethodSpecificationTests()
        {
        }

        [Theory]
        [InlineData("一般學校", "高中以下", "各級學校申請外國教師聘僱許可及管理辦法","" , true, "引進方式不可空白")]
        [InlineData("一般學校", "高中以下", "各級學校申請外國教師聘僱許可及管理辦法", "1", false, "")]
        [InlineData("一般學校", "高中以下", "", "", false, "")]
        [InlineData("一般學校", "高中以下", "語文中心", "", false, "")]
        [InlineData("一般學校", "專科以上", "各級學校申請外國教師聘僱許可及管理辦法", "", false, "")]
        public void Test_IsSatisfiedBy_When_IntroductionMethod_Returns_error(
            string organizationType,string userGroups,string processCategory
            ,string introductionMethod, bool inValid,string errorMessage)
        {
            //given
            var fakeEmployer = new FakeEmployerBuilder()
                .WithOrganizationType(organizationType)
                .WithUserGroups(userGroups)
                .Build();

            var fakeWorkInformation = new FakeWorkInformationBuilder()
                .WithIntroductionMethod(introductionMethod)
                .Build();

            var workPermitNo = "1234567890";
            var applicationType = "新聘";
            var preApprovedStartDate = "2021-01-01";
            var preApprovedEndDate = "2021-12-31";
            var fakeProcessNo = "1234567890";
            var fakeWorkPermit = WorkPermit.FromString(workPermitNo, applicationType, fakeProcessNo, processCategory, preApprovedStartDate, preApprovedEndDate);
            fakeWorkPermit.SetInformation(fakeWorkInformation);
            fakeWorkPermit.SetEmployer(fakeEmployer);

            //when
            var introductionMethodSpecification = new IntroductionMethodSpecification();
            introductionMethodSpecification.IsSatisfiedBy(fakeWorkPermit);

            var hasError = introductionMethodSpecification.ValidateResult.IsError;
            var errorResult = hasError ? introductionMethodSpecification.ValidateResult.FirstError.Description : string.Empty;

            //then
            Assert.Equal(inValid, introductionMethodSpecification.ValidateResult.IsError);
            Assert.Equal(errorMessage, errorResult);
        }
    }
```

- 業務行為

```csharp
public class ReturnOutOfDateAwaitingDocumentsCommandSpec
{

    public ReturnOutOfDateAwaitingDocumentsCommandSpec()
    {
        
    }

    [Fact]
    public async Task ShouldReturnErrorResultWhenCaseIsNotFound()
    {
        // Arrange
        var caseId = Guid.NewGuid().ToString();
        var modifiedBy = "test";
        var modifiedFrom = "test";
        var notifyDate = DateTime.Now.AddDays(-1);
        var returnDate = DateTime.Now.AddDays(-2);
        var email = "123@123.com";
        var command = new ReturnOutOfDateAwaitingDocumentsCommand(caseId, modifiedBy, modifiedFrom, notifyDate, returnDate, email);
        var caseManagement = Substitute.For<ICaseManagementContext>();
        caseManagement.GetCaseAsync(caseId).Returns(Option<WorkPermit>.None);
        var eventBus = Substitute.For<IEventBus>();
        var handler = new ReturnOutOfDateAwaitingDocumentsCommandHandler(caseManagement, eventBus);

        // Act
        var result = await handler.Handle(command);

        // Assert
        Assert.NotEmpty(result.Errors);
    }   
}
```
### 整合測試

- 針對API實作整合測試，盡量使用正面案例，因主要負面情境應已在單元測試實作，整合測試僅針對API Contract的輸出與輸入測試

- 使用記憶體資料庫，透過FakeDataBuilder建立假資料表，資料表來源於TestsData資料夾
> 使用SSMS將測試資料庫資料匯出成csv檔案
報表資料表: xxxx.csv
其他相關資料表: xxxx.csv

- API行為需要的測試情境與回應結果，僅針對200，404等狀態檢查回應訊息結構

```csharp
public class CheckStatusTests : IClassFixture<SharedContext.WorkPermitContext>
    {
        /// <summary>
        /// CheckStatus所需要的測試環境
        /// 透過FakeDataBuilder建立假資料表，資料表來源於TestsData資料夾
        /// 使用SSMS將測試資料庫資料匯出成csv檔案
        /// 資料表: view_case_information.csv
        /// 資料表: d_case_workprojects.csv
        /// 資料表: d_case_filerequired.csv
        /// 資料表: s_system_regex.csv
        /// </summary>
        private readonly SharedContext.WorkPermitContext _fixture;
        private readonly string WorkPermitInformationId = "3B1938FC-EBBD-4DEA-8947-3AAE4F95CD6A";

        public CheckStatusTests(SharedContext.WorkPermitContext fixture)
        {
            _fixture = fixture;
        }

        [Fact]
        public void test_empty_id_Should_Be_Return_Error()
        {
            // Arrange
            var expectedError = "查無案件相關資料!\\n";
            // Act

            var result = Case.Check_CaseB(string.Empty);
            // Assert
            Assert.Equal(expectedError, result);
        }
}
```
        
## reference

[asp.net core offical doc](https://learn.microsoft.com/en-us/aspnet/core/?view=aspnetcore-8.0)
[xunit](https://xunit.net/)
[unit-testing-with-dotnet-test](https://learn.microsoft.com/zh-tw/dotnet/core/testing/unit-testing-with-dotnet-test)
[NSubstitute](https://nsubstitute.github.io/)
[net8 best practice](https://github.com/PacktPublishing/ASP.NET-8-Best-Practices)
[google api guidelines](https://cloud.google.com/apis/design)

## 備註

作者: 石嘉揚

版本: 0.2

Changelog:

0.1: Initial proposed version
0.2: 新增職責說明與步驟、API Design、Data change captured區塊

