owned this note
owned this note
Published
Linked with GitHub
# 110-1 資料庫助教課教材(高鐵票價搜尋)
## Table of Contents
[TOC]
:dart: 預期介面
--
參考:[高鐵官網](https://www.thsrc.com.tw/)
![](https://i.imgur.com/8SaDYZB.png)
![](https://i.imgur.com/dsHcvIl.png)
![](https://i.imgur.com/ebtPpBA.png)
功能:
- 參考官網
- 輸入起迄站、日期、時間,返回當天晚於搜尋時間的車次、出發時間、抵達時間、行車時間及票價
: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)
```json
[
{
"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
```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)
```json
出發時間/行車時間/抵達時間/車次/是否適用早鳥/是否適用大學生優惠
# 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
```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)
```json
全票/早鳥票/大學生票/(孩童票/敬老票/愛心票)/團體票/商務艙票價/自由座票價
# 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
--
```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)