# Kanban


這是一個工作流程管理系統

## Functional Requirments
1. 工作流程可以分成需求等待區（RequestQueue）、Top 5 需求（Top5Request）、需求（Request）、分析（Analysis）、開發（Development）、測試（Test）、上線（Release），七種階段（Stage）。
2. 使用「卡片（Card）」作為從需求規劃階段到上線階段中，需被執行的工作事項。
3. 每個階段都有各自同時能夠負荷的任務上限（Limit）數量，如上圖所示。
4. 每個階段中分別有進行中（Processing）與完成（Completed）兩種子階段（Substage），每通過該子階段時都需要紀錄時間。
5. 每個卡片都有一個優先處理的順序（CardOrder），並且在每個子階段中的所有卡片要依照此順序執行。
6. 圖片中的兩條綠色線分別為卡片的起點與終點，此外需要知道每個卡片從起點流動到終點所需的時間（Lead time）。
7. 需要知道每張卡片在各階段中從進行中移動到完成階段所需的時間（Cycle time）。
8. 在各階段中可能會遇到阻礙（Block）的情況，需要等待某些事情完成後才能繼續往下進行。
9. 如遇到緊急情況，有需要立即修復的 Bug 時，建立一個緊急通道且設定 WIP=1（讓所有流程的 Limit=1），優先執行此卡片。

## Non-Functional Requirments
1. Controller 傳入參數檢查
2. 使用 filter 印出 Request & Response 資訊
3. 自行生成憑證並掛上 HTTPS，再更改本機 host file 模擬提供 domain 服務

## ER Model


## ER Schema


[連結](https://editor.ponyorm.com/user/zacharyzhuo/kanban/designer)

## Class Diagram


## Flow Chart


## Use Case Specification

**Card Order**

| 順序 | 備註 |
|:----:|:--------:|
| -1 | 預設 |
| 0 | 緊急任務 |
| 1 | 順位 1 |
| ... | ... |
| n | 順位 n |

## Localhost on https://\<my domain>

[傳送門](https://hackmd.io/7g8zKNZdQTyov4kaFBaoEQ?both#Localhost-on-httpsltmy-domaingt)

## API

### Member

[GET] `/members`
Response

[
    {
        "id": 1,
        "name": "Zachary",
        "email": "[e-mail]"
    },
    {
        "id": 2,
        "name": "Derek",
        "email": "[e-mail]"
    }
]

[GET] `/member/{id}`
Response

{
    "id": 1,
    "name": "Zachary",
    "email": "[e-mail]"
}

[POST] `/member`
Request

{
    "name": "somebody",
    "email": "[e-mail]"
}

[PUT] `/member/{id}`
Request

{
    "name": "somebody",
    "email": "[e-mail]"
}

[DELETE] `/member/{id}`

### Card

[GET] `/cards`
Response

[
    {
        "id": 1,
        "member": {
            "id": 1,
            "name": "Zachary",
            "email": "[e-mail]"
        },
        "title": "test",
        "content": "test",
        "cardOrder": 1,
        "emergent": false,
        "createdTime": "2021-08-12T03:51:42.000+00:00",
        "completedTime": null
    },
    {
        "id": 2,
        "member": {
            "id": 1,
            "name": "Zachary",
            "email": "[e-mail]"
        },
        "title": "todo",
        "content": "66666",
        "cardOrder": 0,
        "emergent": true,
        "createdTime": "2021-08-12T03:55:09.000+00:00",
        "completedTime": "2021-08-12T06:04:31.000+00:00"
    }
]

[GET] `/card/{id}`
Response

{
    "id": 1,
    "member": {
        "id": 1,
        "name": "Zachary",
        "email": "[e-mail]"
    },
    "title": "test",
    "content": "test",
    "cardOrder": 1,
    "emergent": false,
    "createdTime": "2021-08-12T03:51:42.000+00:00",
    "completedTime": null
}

[POST] `/card`
Request

{
    "title": "todo",
    "content": "very emergent",
    "emergent": true
}

[PUT] `/card/{id}`
Request

{
    "title": "todo",
    "content": "very emergent",
    "emergent": true,
    "cardOrder": 0
}

[DELETE] `/card/{id}`

### Task

[GET] `/tasks`
Response

[
    {
        "id": 1,
        "card": {
            "id": 1,
            "member": {
                "id": 1,
                "name": "Zachary",
                "email": "[e-mail]"
            },
            "title": "test",
            "content": "test",
            "cardOrder": 1,
            "emergent": false,
            "createdTime": "2021-08-12T03:51:42.000+00:00",
            "completedTime": null
        },
        "stage": {
            "id": 1,
            "name": "RequestQueue",
            "stageLimit": 0
        },
        "substage": {
            "id": 1,
            "stage": {
                "id": 1,
                "name": "RequestQueue",
                "stageLimit": 0
            },
            "status": null
        },
        "member": {
            "id": 1,
            "name": "Zachary",
            "email": "[e-mail]"
        },
        "block": false,
        "startTime": "2021-08-12T03:51:42.000+00:00",
        "endTime": "2021-08-12T03:59:46.000+00:00"
    },
    {
        "id": 2,
        "card": {
            "id": 2,
            "member": {
                "id": 1,
                "name": "Zachary",
                "email": "[e-mail]"
            },
            "title": "todo",
            "content": "66666",
            "cardOrder": 0,
            "emergent": true,
            "createdTime": "2021-08-12T03:55:09.000+00:00",
            "completedTime": "2021-08-12T06:04:31.000+00:00"
        },
        "stage": {
            "id": 1,
            "name": "RequestQueue",
            "stageLimit": 0
        },
        "substage": {
            "id": 1,
            "stage": {
                "id": 1,
                "name": "RequestQueue",
                "stageLimit": 0
            },
            "status": null
        },
        "member": {
            "id": 1,
            "name": "Zachary",
            "email": "[e-mail]"
        },
        "block": false,
        "startTime": "2021-08-12T03:56:42.000+00:00",
        "endTime": "2021-08-12T03:59:25.000+00:00"
    }
]

[GET] `/task/{id}`
Response 略

[GET] `/task/cardid/{id}`：查詢該卡片底下所有的任務
Response 略

[GET] `/task/substageid/{id}`：查詢該子階段底下所有的任務
Response 略

[GET] `/task/stageid/{id}`：查詢該階段底下所有的任務
Response 略

[PUT] `/card/{id}/next`：將卡片並移動至下個子階段

[PUT] `/card/{id}/finish`：將卡片目前子階段標示完成

[PUT] `/card/{id}/block`：將卡片變更為阻礙 (block) 狀態

### Substage

[GET] `/substages`
Response

[
    {
        "id": 1,
        "stage": {
            "id": 1,
            "name": "RequestQueue",
            "stageLimit": 0
        },
        "status": null
    },
    {
        "id": 2,
        "stage": {
            "id": 2,
            "name": "Top5Request",
            "stageLimit": 5
        },
        "status": null
    },
    {
        "id": 3,
        "stage": {
            "id": 3,
            "name": "Request",
            "stageLimit": 3
        },
        "status": "Processing"
    },
    {
        "id": 4,
        "stage": {
            "id": 3,
            "name": "Request",
            "stageLimit": 3
        },
        "status": "Completed"
    },
    {
        "id": 5,
        "stage": {
            "id": 4,
            "name": "Analysis",
            "stageLimit": 4
        },
        "status": "Processing"
    },
    {
        "id": 6,
        "stage": {
            "id": 4,
            "name": "Analysis",
            "stageLimit": 4
        },
        "status": "Completed"
    },
    {
        "id": 7,
        "stage": {
            "id": 5,
            "name": "Development",
            "stageLimit": 5
        },
        "status": "Processing"
    },
    {
        "id": 8,
        "stage": {
            "id": 5,
            "name": "Development",
            "stageLimit": 5
        },
        "status": "Completed"
    },
    {
        "id": 9,
        "stage": {
            "id": 6,
            "name": "Test",
            "stageLimit": 3
        },
        "status": "Processing"
    },
    {
        "id": 10,
        "stage": {
            "id": 6,
            "name": "Test",
            "stageLimit": 3
        },
        "status": "Completed"
    },
    {
        "id": 11,
        "stage": {
            "id": 7,
            "name": "Release",
            "stageLimit": 0
        },
        "status": null
    }
]

[GET] `/substage/{id}`
Response

{
    "id": 1,
    "stage": {
        "id": 1,
        "name": "RequestQueue",
        "stageLimit": 0
    },
    "status": null
}

### Stage

[GET] `/stages`
Response

[
    {
        "id": 1,
        "name": "RequestQueue",
        "stageLimit": 0
    },
    {
        "id": 2,
        "name": "Top5Request",
        "stageLimit": 5
    },
    {
        "id": 3,
        "name": "Request",
        "stageLimit": 3
    },
    {
        "id": 4,
        "name": "Analysis",
        "stageLimit": 4
    },
    {
        "id": 5,
        "name": "Development",
        "stageLimit": 5
    },
    {
        "id": 6,
        "name": "Test",
        "stageLimit": 3
    },
    {
        "id": 7,
        "name": "Release",
        "stageLimit": 0
    }
]

[GET] `/stage/{id}`
Response

{
    "id": 1,
    "name": "RequestQueue",
    "stageLimit": 0
}

## SQL

CREATE TABLE `MEMBER` (
  `ID` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `NAME` VARCHAR(255) UNIQUE NOT NULL,
  `EMAIL` VARCHAR(255) NOT NULL
);

CREATE TABLE `CARD` (
  `ID` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `MEMBER_ID` INTEGER NOT NULL REFERENCES MEMBER(ID) ON DELETE CASCADE,
  `TITLE` VARCHAR(255) NOT NULL,
  `CONTENT` VARCHAR(255) NOT NULL,
  `CARD_ORDER` INTEGER NOT NULL,
  `EMERGENT` BOOLEAN NOT NULL,
  `CREATED_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `COMPLETED_TIME` DATETIME 
);

CREATE TABLE `STAGE` (
  `ID` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `NAME` VARCHAR(255) NOT NULL,
  `STAGE_LIMIT` INTEGER NOT NULL
);

CREATE TABLE `SUBSTAGE` (
  `ID` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `STAGE_ID` INTEGER NOT NULL REFERENCES STAGE(ID) ON DELETE CASCADE,
  `STATUS` VARCHAR(255)
);

CREATE TABLE `TASK` (
  `ID` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `CARD_ID` INTEGER NOT NULL REFERENCES CARD(ID) ON DELETE CASCADE,
  `STAGE_ID` INTEGER NOT NULL REFERENCES STAGE(ID) ON DELETE CASCADE,
  `SUBSTAGE_ID` INTEGER NOT NULL REFERENCES SUBSTAGE(ID) ON DELETE CASCADE,
  `MEMBER_ID` INTEGER NOT NULL REFERENCES MEMBER(ID) ON DELETE CASCADE,
  `BLOCK` BOOLEAN NOT NULL,
  `START_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `END_TIME` DATETIME
);
	
INSERT INTO MEMBER (ID, NAME, EMAIL)
values (0, 'Zachary', '[e-mail]');

INSERT INTO STAGE (ID, NAME, STAGE_LIMIT)
values (0, 'RequestQueue', 0), (0, 'Top5Request', 5), (0, 'Request', 3), 
(0, 'Analysis', 4), (0, 'Development', 5), (0, 'Test', 3), (0, 'Release', 0);

INSERT INTO SUBSTAGE (ID, STAGE_ID, STATUS)
values (0, (SELECT ID from STAGE WHERE ID=1), null),
(0, (SELECT ID from STAGE WHERE ID=2), null),
(0, (SELECT ID from STAGE WHERE ID=3), 'Processing'),
(0, (SELECT ID from STAGE WHERE ID=3), 'Completed'),
(0, (SELECT ID from STAGE WHERE ID=4), 'Processing'),
(0, (SELECT ID from STAGE WHERE ID=4), 'Completed'),
(0, (SELECT ID from STAGE WHERE ID=5), 'Processing'),
(0, (SELECT ID from STAGE WHERE ID=5), 'Completed'),
(0, (SELECT ID from STAGE WHERE ID=6), 'Processing'),
(0, (SELECT ID from STAGE WHERE ID=6), 'Completed'),
(0, (SELECT ID from STAGE WHERE ID=7), null);

INSERT INTO CARD (ID, MEMBER_ID, TITLE, CONTENT, CARD_ORDER, EMERGENT)
values (0, (SELECT ID from MEMBER WHERE ID=1), 'test', 'test', 1, FALSE);

INSERT INTO TASK (ID, CARD_ID, STAGE_ID, SUBSTAGE_ID, MEMBER_ID, BLOCK)
values (0, (SELECT ID from CARD WHERE ID=1), (SELECT ID from STAGE WHERE ID=1), (SELECT ID from SUBSTAGE WHERE ID=1), (SELECT ID from MEMBER WHERE ID=1), FALSE);

## Note
1. ```@NotNull``` 任何物件的 value 不能為 null
2. ```@NotEmpty``` 集合物件的元素不為 0，即集合不為空，也可以用於字串不為 null
3. ```@NotBlank``` 只能用於字串不為 null，並且字串 trim() 以後 length 要大於 0
4. SQL 建表時欄位有設定初值且非空，但建立實體時沒有給值會報錯，可使用 ```@Column(name="CREATED_TIME", insertable=false)``` 讓此欄位不會插入新的值。
5. Repository 中定義取資料表中第一筆資料```findFirstByOrderByIdAsc();```

#log SQL
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true

logging.level.org.hibernate.type=trace

## Reference

[[1] 了解看板(Kanban)方法的核心方法，活化你的管理思維](https://progressbar.tw/posts/66)

###### tags: `project`