def get(self):
"""
Available tickets query API
**return**: A JSON dictionary with values:
- `code`: `int`, always equals to 0
- `result`: `list` of dictionaries of ticket information:
- `seat_type_id`: `int`
- `seat_type_name`: `str`
- `left_cnt`: `int`
- `price`: `float`
"""
session = DBSession()
try:
train_name = urllib.parse.unquote(request.args.get('train_name'))
first_interval = int(
urllib.parse.unquote(request.args.get('first_interval')))
last_interval = int(
urllib.parse.unquote(request.args.get('last_interval')))
interval_list = get_interval_list(train_name, session)
first_index = session.query(interval_list.c.interval_no) \
.filter(interval_list.c.interval_id == first_interval) \
.first() \
.interval_no
last_index = session.query(interval_list.c.interval_no) \
.filter(interval_list.c.interval_id == last_interval) \
.first() \
.interval_no
price_list = session.query(Price.seat_type_id, func.sum(Price.price).label('price')) \
.join(interval_list, Price.interval_id == interval_list.c.interval_id) \
.filter(interval_list.c.interval_no <= last_index, interval_list.c.interval_no >= first_index) \
.group_by(Price.seat_type_id) \
.subquery()
seats_left = session.query(Seat.seat_type_id, SeatType.name, func.count().label('left_cnt')) \
.join(SeatType, SeatType.seat_type_id == Seat.seat_type_id) \
.join(Train, Train.train_id == Seat.train_id) \
.filter(Train.train_name == train_name,
func.cast(func.substring(Seat.occupied, first_index, last_index - first_index + 1),
BIGINT) == 0) \
.group_by(Seat.seat_type_id, SeatType.name) \
.subquery()
resp = session.query(seats_left.c.seat_type_id, seats_left.c.name.label('seat_type_name'),
seats_left.c.left_cnt, price_list.c.price) \
.join(price_list, price_list.c.seat_type_id == seats_left.c.seat_type_id) \
.all()
resp = list(
sorted(map(lambda x: dict(zip(x.keys(), x)), resp),
key=lambda x: x['seat_type_id']))
return jsonify(result=resp, code=0)
except:
return jsonify(code=10, error='Query error')
finally:
session.close()
def get(self):
"""
Train information query API
**argument**:
- `train_name`: `str`
**return**: A JSON dictionary with values:
- `code`: `int`, always equals to 0
- `result`: `list` of dictionaries of passing station information:
- `id`: `int`
- `district`: `str`
- `station`: `str`
- `time`: `str`
"""
session = DBSession()
try:
train_name = request.args.get('train_name')
successive_train_rec = get_interval_list(train_name, session)
interval_list = session.query(successive_train_rec.c.interval_no.label('id'),
City.city_name.concat(',').concat(District.district_name).label('district'),
Station.station_name.label('station'),
func.cast(successive_train_rec.c.dep_datetime, String).label('time')) \
.join(Station, Station.station_id == successive_train_rec.c.dep_station) \
.join(District, Station.district_id == District.district_id) \
.join(City, District.city_id == City.city_id) \
.order_by(successive_train_rec.c.interval_id, Station.available == True) \
.all()
last_no = interval_list[-1].id
resp = list(map(lambda x: dict(zip(x.keys(), x)), interval_list))
last_station = session.query(func.cast(successive_train_rec.c.arv_datetime, String).label('time'),
City.city_name.concat(',').concat(District.district_name).label('district'),
Station.station_name.label('station'),
literal(last_no + 1).label('id')) \
.join(Station, Station.station_id == successive_train_rec.c.arv_station) \
.join(District, Station.district_id == District.district_id) \
.join(City, District.city_id == City.city_id) \
.filter(successive_train_rec.c.interval_no == last_no, Station.available == True) \
.first()
if last_station:
resp.append(dict(zip(last_station.keys(), last_station)))
return jsonify(result=resp, code=0)
except:
return jsonify(code=10, error='未找到线路')
finally:
session.close()
def get(self):
"""
Train line information query API for administrator, **JWT required**
**argument**:
- `train_name`: `str`
**return**: A JSON dictionary with values:
- `code`: `int`, equals to 0 if query is successful
- `error`: `str`, shown if `code != 0`
- `result`: `dict` containing:
- `available`: `boolean`
- `line`: `list` of dictionaries of interval information:
- `interval_id`: `int`
- `interval_no`： `int`
- `train_name`: `str`
- `dep_station`: `str`
- `arv_station`: `str`
- `dep_datetime`: `str`
- `arv_datetime`: `str`
- `price`: `dict` containing:
- `seat_type_1`, `str`
- `seat_type_2`, `str`
- `seat_type_3`, `str`
- `seat_type_4`, `str`
- `seat_type_5`, `str`
- `seat_type_6`, `str`
- `seat_type_7`, `str`
"""
session = DBSession()
try:
train_name = request.args.get('train_name')
interval_list = get_interval_list(train_name, session, True)
available = session.query(Train.available).filter(
Train.train_name == train_name).first().available
dep_s = aliased(Station, name='dep_s')
arv_s = aliased(Station, name='arv_s')
res_list = session.query(interval_list.c.interval_id, interval_list.c.interval_no, Train.train_name,
dep_s.station_name.label('dep_station'), arv_s.station_name.label('arv_station'),
func.cast(interval_list.c.dep_datetime, String).label('dep_datetime'),
func.cast(interval_list.c.arv_datetime, String).label('arv_datetime'), ) \
.join(Train, Train.train_id == interval_list.c.train_id) \
.join(dep_s, dep_s.station_id == interval_list.c.dep_station) \
.join(arv_s, arv_s.station_id == interval_list.c.arv_station) \
.order_by(interval_list.c.interval_no) \
.all()
res_list = list(map(lambda x: dict(zip(x.keys(), x)), res_list))
for i in res_list:
i['price'] = dict(
zip(map(lambda x: 'seat_type_' + str(x), range(1, 8)),
['x'] * 7))
price_list = session.query(Price).filter(
Price.interval_id == i['interval_id']).all()
i['price'].update(
dict(
map(
lambda x: ('seat_type_{}'.format(x.seat_type_id),
'{:.2f}'.format(x.price)), price_list)))
return jsonify(code=0,
result={
'line': res_list,
'available': available
})
except:
traceback.print_exc()
return jsonify(code=10, error='Query error')
finally:
session.close()
def delete(self):
"""
Ticket refund API, **JWT required**
**return**: A JSON dictionary with values:
- `code`: `int`, equals to 0 if deletion is successful
- `result`: `str`, shown if `code == 0`
- `error`: `str`, shown if `code != 0`
"""
session = DBSession()
try:
user_id = get_jwt_identity()
body = request.get_json()
order_id = body.get('order_id')
# The delete function is to set the order status "cancelled", set ticket available false,
# and released the occupied seat.
current_order: Order = session.query(Order).filter(
Order.order_id == order_id).first()
# print(current_order.user_id, user_id)
if int(user_id) != int(current_order.user_id):
return jsonify(code=100, error='非法退票操作！')
current_order.order_status = "cancelled"
session.commit()
session.flush()
current_ticket: Ticket = session.query(Ticket).filter(
Ticket.ticket_id == current_order.ticket_id).first()
current_ticket.available = False
session.commit()
session.flush()
first_interval = current_ticket.first_interval
last_interval = current_ticket.last_interval
current_seat: Seat = session.query(Seat).filter(
Seat.seat_id == current_ticket.seat_id).first()
train_id = session.query(Train.train_id).join(
Interval, Interval.train_id == Train.train_id).filter(
Interval.interval_id == first_interval,
Interval.available == True,
Train.available == True).first()
# Here to release the seat
train_name = session.query(Train.train_name).filter(
Train.train_id == train_id,
Train.available == True).first().train_name
interval_list = get_interval_list(train_name, session)
# successive_train_rec = get_interval_list(train_name, session)
# interval_list = session.query(successive_train_rec.c.interval_id) \
# .order_by(successive_train_rec.c.interval_id) \
# .all()
first_index = session.query(interval_list.c.interval_no) \
.filter(interval_list.c.interval_id == first_interval) \
.first() \
.interval_no
last_index = session.query(interval_list.c.interval_no) \
.filter(interval_list.c.interval_id == last_interval) \
.first() \
.interval_no
current_seat.occupied = bin(
int(current_seat.occupied, 2)
& int('0' * (last_index - first_index + 1), 2) <<
(40 - last_index))[2:].zfill(40)
session.commit()
session.flush()
return jsonify(code=0, result="操作成功，票已失效")
except:
session.rollback()
return jsonify(code=10, error='操作失败，请联系管理员')
finally:
session.close()
def post(self):
"""
Train order API, **JWT required**
**return**: A JSON dictionary with values:
- `code`: `int`, equals to 0 if order is successful
- `result`: `dict` with values, shown if `code == 0`:
- `order_id`: `int`
- `error`: `str`, shown if `code != 0`
"""
session = DBSession()
try:
user_id = get_jwt_identity()
body = request.get_json()
first_interval = int(body.get('first_interval'))
last_interval = int(body.get('last_interval'))
seat_class = body.get('seat_class')
train_name = body.get('train_name')
# successive_train_rec = get_interval_list(train_name, session)
interval_list = get_interval_list(train_name, session)
# interval_list = session.query(successive_train_rec.c.interval_id) \
# .order_by(successive_train_rec.c.interval_id) \
# .all()
first_index = session.query(interval_list.c.interval_no) \
.filter(interval_list.c.interval_id == first_interval) \
.first() \
.interval_no
last_index = session.query(interval_list.c.interval_no) \
.filter(interval_list.c.interval_id == last_interval) \
.first() \
.interval_no
seat = session.query(Seat) \
.join(Train, Train.train_id == Seat.train_id) \
.filter(Train.train_name == train_name,
Seat.seat_type_id == seat_class,
func.cast(func.substring(Seat.occupied, first_index, last_index - first_index + 1),
BIGINT) == 0) \
.first()
if seat is None:
return jsonify(code=404, error='当前区间无余票！')
seat.occupied = bin(
int(seat.occupied, 2)
| int('1' * (last_index - first_index + 1), 2) <<
(40 - last_index))[2:].zfill(40)
new_ticket = Ticket(first_interval=first_interval,
last_interval=last_interval,
seat_id=seat.seat_id,
available=False)
session.add(new_ticket)
session.commit()
session.flush()
price = session.query(func.sum(Price.price).label('price')) \
.join(interval_list, Price.interval_id == interval_list.c.interval_id) \
.filter(Price.seat_type_id == seat_class, interval_list.c.interval_no <= last_index,
interval_list.c.interval_no >= first_index) \
.first()
new_order = Order(order_status='unpaid',
user_id=user_id,
ticket_id=new_ticket.ticket_id,
price=price)
session.add(new_order)
session.commit()
session.flush()
return jsonify(code=0, result={'order_id': new_order.order_id})
except:
session.rollback()
return jsonify(code=10, error='订票失败')
finally:
session.close()