# 110-1 資料庫助教課教材（高鐵票價搜尋）

## Table of Contents
[TOC]

:dart: 預期介面
--
參考：[高鐵官網](https://www.thsrc.com.tw/)

功能：
- 參考官網
- 輸入起迄站、日期、時間，返回當天晚於搜尋時間的車次、出發時間、抵達時間、行車時間及票價

:closed_book: Tasks
--
- [x] 8/1完成前端
- [x] 8/14完成後端及前後端串接
- [x] 8/2將資料存進 DB
- [x] 測試

:books: 資料表
--
**\*: primary key**

#### Station
|\*station_id|station_name|
|-|-|
|0990|南港|
|1000|台北|

#### Train

*0136班次的車從左營開到南港，只在週一、週四、週六有開車*

|\*train_id|==starting_station_id== |==ending_station_id== |mon |tue| wed |thu| fri| sat |sun|
|-|- |-|-|-|-|-|-|-|-|
|0136| 1070| 0990 |1| 0 |0 |1 |0 |1 |0|

#### Schedule

*0136（起站）是南港，發車時間是 14:55。
0136班次的第二站是台北站，抵達台北站的時間是15:37，並在15:39離開台北站。
依此類推。*

|\*schedule_id|==train_id==|stop_sequence|==station_id==|arrival_time|departure_time|
|-|-|- |-|-|-|
|1|0136| 1| 1070 | | 14:55|
|2| 0136| 2| 1040| 15:37| 15:39|

#### Fare

|\*fare_id| ==origin_station_id== |==destination_station_id==|business_fare| standard_fare|non_reserved_fare|
|-|-|- |-|-|-|
|1|0990|1000|260.0|40.0|35.0|

#### discount

|\*discount_id|type|discount_rate|
|-|-|- |
|1|concession|0.5|
|2|group|0.95|
|3|early_bird |0.8|
|4|undergraduate|0.75|

:closed_book: 規則
--
優惠票：直接顯示
團體票：直接顯示
早鳥票：早於5天前訂票
大學生優惠：出發時間晚於 21:00

:mag: API 格式
--
**GET /api/stations**
取得全部車站資訊

==Request==
Parameters: 無

==Response==
Content-Type: application/json
Status code: 200, 405(request method error)

[
    {
        "station_id": "0990",
        "station_name": "南港"
    },
    {
        "station_id": "1000",
        "station_name": "台北"
    },
    {
        "station_id": "1010",
        "station_name": "板橋"
    },
    {
        "station_id": "1020",
        "station_name": "桃園"
    },
    {
        "station_id": "1030",
        "station_name": "新竹"
    },
    {
        "station_id": "1035",
        "station_name": "苗栗"
    },
    {
        "station_id": "1040",
        "station_name": "台中"
    },
    {
        "station_id": "1043",
        "station_name": "彰化"
    },
    {
        "station_id": "1047",
        "station_name": "雲林"
    },
    {
        "station_id": "1050",
        "station_name": "嘉義"
    },
    {
        "station_id": "1060",
        "station_name": "台南"
    },
    {
        "station_id": "1070",
        "station_name": "左營"
    }
]

**POST api/timetable/search**
根據參數搜尋車次及資訊

==Request==
Parameters: 起站/訖站/日期/時間
Content-Type: application/json

# req parameters
{
    "StartStation": "南港",
    "EndStation": "左營",
    "OutWardSearchDate": "2021/10/20",
    "OutWardSearchTime": "12:30"
}

==Response==
Content-Type: application/json
Status code: 200, 400, 405
- 200(ok)
- 400(Search time is expired. Content type error. Parameters are mistakes.)
- 405(request method error)

出發時間/行車時間/抵達時間/車次/是否適用早鳥/是否適用大學生優惠
# response
[
    {
    key: '1',
    startTime: '20:00',
    timeSpend: 32,
    arrivalTime: '20:32',
    number: 522,
    earlyBird: true,
    undergraduate: false
  },
  {
    key: '2',
    startTime: '20:10',
    timeSpend: 42,
    arrivalTime: '20:52',
    number: 833,
    earlyBird: true,
    undergraduate: true
  },
  {
    key: '3',
    startTime: '20:20',
    timeSpend: 32,
    arrivalTime: '20:52',
    number: 333,
    earlyBird: false,
    undergraduate: true
  },,...
]

**POST api/ticket/search**
根據參數搜尋票價

==Request==
Parameters: 起站/訖站/日期
Content-Type: application/json

# parameters
{
    "StartStation": "南港",
    "EndStation": "左營",
    "OutWardSearchDate": "2021/08/12"
}

==Response==
Content-Type: application/json
Status code: 200, 400, 405
- 200(ok)
- 400(Search time is expired. Content type error. Parameters are mistakes.)
- 405(request method error)

全票/早鳥票/大學生票/(孩童票/敬老票/愛心票)/團體票/商務艙票價/自由座票價
# response
[
    {
      key: '1',
      type: '標準車廂',
      full: 1024,
      discount: 512,
      earlyBird: 800,
      undergraduate: 700,
      group: 822,
    },
    {
      key: '2',
      type: '商務車廂',
      full: 2048,
      discount: 1024,
      earlyBird: 900,
      undergraduate: 800,
      group: 933,
    },
    {
      key: '3',
      type: '自由座車廂',
      full: 909,
      discount: 455,
      earlyBird: 750,
      undergraduate: 750,
      group: 830,
    },
  ]

------底下為助教的亂亂筆記請自行忽略--------

:pencil: SQL
--

-- 查詢週二台北開到台中可能的班次(確認南下北上在判斷大於小於)
-- 台北：1000 台中：1040
SELECT T.train_id AS potential_tids 
FROM Train T
WHERE 
	T.starting_station_id <= '1000' AND
	T.ending_station_id >= '1040' AND 
	T.tue = true;
    
    
-- 查詢週二台北開到苗栗的班次的時刻表(train_id出現兩次以上才表示兩站都有經過)
-- 台北：1000 苗栗：1035

SELECT *
FROM Schedule S
WHERE 
	S.station_id IN ('1000','1035')
	AND
	S.train_id IN ( SELECT T.train_id
					FROM Train T
					WHERE 
						T.starting_station_id <= '1000' AND
						T.ending_station_id >= '1035' AND 
						T.tue = true)
                        
-- 查詢台北到台中的 標準車廂/商務車廂/自由座 的全票價格
-- 台北：1000 台中：1040

SELECT business_fare, standard_fare, non_reserved_fare
FROM Fare
WHERE 
	origin_station_id = '1000' AND
	destination_station_id = '1040';

:apple: Reference
--

- [Django tutorial for programmers](https://ithelp.ithome.com.tw/users/20091495/ironman/844)
- [Deployment Check List](https://docs.djangoproject.com/en/3.2/howto/deployment/checklist/)
- [RESTful API tutorial]
- [github example-forum](https://github.com/endiliey/rengorum)
- [加速你的 Django 網站開發 - Django 的好用套件](https://ithelp.ithome.com.tw/users/20012434/ironman/3357?page=1)
- [sql injection with Django](https://www.jbssolutions.com/resources/blog/sql-injection-attacks-and-django/)
- [使用 pyenv 和 virtualenv 打造 Python 環境](https://www.maxlist.xyz/2020/04/01/python-pyenv-virtualenv/)
- [Django Model/Migration](https://ithelp.ithome.com.tw/articles/10158250)
- [Django OneToOneField , ForeignKey ,ManyToManyField](https://github.com/twtrubiks/django-field-tutorial)