def getAttraction(attractionId):
mydb = connection_pool.get_connection()
#將sql指令取得的資料 由tuple改成dict
mycursor = mydb.cursor(dictionary=True)
try:
if mydb.is_connected():
responseData = {}
try:
#join 資料及資料照片資料表 當id=網址參數才被join 其餘資料table 為 null 取出不為null的cloumn
mycursor.execute(
f"SELECT img_url FROM attractions A LEFT JOIN attractions_img B ON A.id = B.id and A.id={attractionId} WHERE B.img_url IS NOT NULL"
)
imgResult = mycursor.fetchall()
imgArray = []
for img in imgResult:
imgArray.append(img["img_url"])
#取得指定id的資料 依照指定格式將資料存放至responseData 準備return
mycursor.execute(
f"SELECT * FROM attractions WHERE id = {attractionId}")
result = mycursor.fetchone()
responseData["data"] = result
responseData["data"]["images"] = imgArray
if (responseData["data"] == None):
return jsonify({"error": True, "message": "景點編號錯誤"}), 400
return jsonify(responseData), 200
except:
return jsonify({"error": True, "message": "伺服器內部錯誤"}), 500
finally:
# closing database connection.
closeConnect(mydb, mycursor)
def historyOrder():
user = session.get("user")
if not user:
return jsonify({
"error": True,
"message": "自未登入系統，拒絕存取"
}), 403
try:
mydb = connection_pool.get_connection()
mycursor = mydb.cursor(dictionary=True)
if mydb.is_connected():
sql = f"""SELECT * FROM orders
JOIN attractions ON orders.attraction_id = attractions.id
WHERE user_id = {user['id']}
ORDER BY order_id desc
"""
mycursor.execute(sql)
result = mycursor.fetchall()
return jsonify({
'data':result
})
finally:
closeConnect(mydb, mycursor)
def order():
user = session.get("user")
if not user:
return jsonify({"error": True, "message": "自未登入系統，拒絕存取"}), 403
mydb = connection_pool.get_connection()
mycursor = mydb.cursor(dictionary=True)
number = request.args.get("number")
try:
if mydb.is_connected():
orderDataSQL = f"""SELECT number, orders.phone, orders.name, orders.email, status, price, date, time,
attractions.id, attractions.name as attraction_name, attractions.address, attractions_img.img_url
FROM orders
JOIN attractions ON orders.attraction_id = attractions.id
JOIN attractions_img ON orders.attraction_id = attractions_img.id
JOIN users ON orders.user_id = users.id
WHERE orders.number = {number} AND orders.user_id = {user['id']} limit 1;
"""
# 避免queryString包含非數字會出現錯誤
try:
mycursor.execute(orderDataSQL)
responseData = mycursor.fetchone()
except:
return jsonify({"data": None})
if responseData:
return jsonify({
"data": {
"number": responseData["number"],
"price": responseData["price"],
"trip": {
"attraction": {
"id": responseData["id"],
"name": responseData["attraction_name"],
"address": responseData["address"],
"image": responseData["img_url"]
},
"date": responseData["date"],
"time": responseData["time"]
},
"contact": {
"name": responseData["name"],
"email": responseData["email"],
"phone": responseData["phone"]
},
"status": responseData["status"]
}
})
else:
return jsonify({"data": None})
finally:
closeConnect(mydb, mycursor)
def getAttractions():
mydb = connection_pool.get_connection()
#將sql指令取得的資料 由tuple改成dict
mycursor = mydb.cursor(dictionary=True)
try:
if mydb.is_connected():
#取出query string
qsPage = request.args.get("page")
qsKeyword = request.args.get("keyword")
if qsPage == 'null':
return {"nextPage": None, "data": None}
try:
#取得資料長度
dataLen = 0
if (not qsKeyword):
mycursor.execute("SELECT count(*) FROM attractions")
dataLen = mycursor.fetchone()["count(*)"]
else:
mycursor.execute(
f"SELECT count(*) FROM attractions WHERE name LIKE '%{qsKeyword}%'"
)
dataLen = mycursor.fetchone()["count(*)"]
#判斷是否有關鍵字查詢
if (qsKeyword):
mycursor.execute(
f"SELECT * FROM attractions WHERE name LIKE '%{qsKeyword}%' limit { int(qsPage)*12 }, 12"
)
elif (not qsKeyword):
mycursor.execute(
f"SELECT * FROM attractions limit { int(qsPage)*12 }, 12"
)
result = mycursor.fetchall()
responseData = {}
data = []
#依照response格式存放資料並在for迴圈結束後return
for attraction in result:
#取得img table中對應id的圖片網址 存放到list中
mycursor.execute(
f"SELECT img_url FROM attractions LEFT JOIN attractions_img ON attractions.id = attractions_img.id WHERE attractions.id = {attraction['id']}"
)
result = mycursor.fetchall()
imgArray = []
for img in result:
imgArray.append(img["img_url"])
#將要response的資料存放到data list中
data.append({
'id': attraction["id"],
'name': attraction["name"],
'category': attraction["category"],
'description': attraction["description"],
'address': attraction["address"],
'transport': attraction["transport"],
'mrt': attraction["mrt"],
'latitude': attraction["latitude"],
'longitude': attraction["longitude"],
'images': imgArray
})
#判斷是否有下一頁 並將結果存放至responseData['nextPage']中準備return
if ((int(qsPage) + 1) * 12 > dataLen):
responseData['nextPage'] = None
else:
responseData['nextPage'] = int(qsPage) + 1
responseData['data'] = data
return jsonify(responseData), 200
except:
return jsonify({"error": True, "message": "資料讀取錯誤"}), 500
finally:
closeConnect(mydb, mycursor)
def orders():
user = session.get("user")
if not user:
return jsonify({"error": True, "message": "自未登入系統，拒絕存取"}), 403
mydb = connection_pool.get_connection()
mycursor = mydb.cursor(dictionary=True)
try:
if mydb.is_connected():
frontEndData = request.get_json()
timestamp = int(datetime.datetime.now().timestamp())
phone = frontEndData["order"]["contact"]["phone"]
name = frontEndData["order"]["contact"]["name"]
email = frontEndData["order"]["contact"]["email"]
if not phone or not name or not email:
return jsonify({
"error": True,
"message": "聯絡資訊不完全，付費流程失敗"
}), 400
# 新增order
try:
sql = f"""
INSERT INTO orders SET
user_id = {user["id"]},
attraction_id = {frontEndData["order"]["trip"]["attraction"]["id"]},
phone = '{phone}',
name = '{name}',
email = '{email}',
number ='{timestamp}',
date = '{frontEndData["order"]["trip"]["date"]}',
time = '{frontEndData["order"]["trip"]["time"]}',
price ={frontEndData["order"]["price"]},
status = 1
"""
mycursor.execute(sql)
mydb.commit()
except:
return jsonify({"error": True, "message": "建立訂單失敗"}), 400
#付款
try:
url = 'https://sandbox.tappaysdk.com/tpc/payment/pay-by-prime'
requestHeaders = {
'Content-Type':
'application/json',
'x-api-key':
'partner_DjKyVCcmswmRao7HqsuTJG8ptWeq8ichqSEJJElaDMTwlFRNLe7CgtiV'
}
values = {
"prime": frontEndData["prime"],
"partner_key": os.getenv("TAPPAY_PARTNER_KEY"),
"merchant_id": "aru0828_CTBC",
"details": "TapPay Test",
"amount": frontEndData["order"]["price"],
"cardholder": {
"phone_number":
frontEndData["order"]["contact"]["phone"],
"name": frontEndData["order"]["contact"]["name"],
"email": frontEndData["order"]["contact"]["email"],
},
"remember": True
}
requestData = json.dumps(values)
response = requests.post(url,
data=requestData,
headers=requestHeaders)
result = response.json()
if not result["status"]:
updatePayStatus = f"UPDATE orders SET orders.status = 0 WHERE orders.number = {timestamp}"
mycursor.execute(updatePayStatus)
mydb.commit()
deleteBookingsSQL = f"DELETE FROM bookings WHERE bookings.user_id = {user['id']}"
mycursor.execute(deleteBookingsSQL)
mydb.commit()
return jsonify({
"data": {
"number": timestamp,
"payment": {
"status": 0,
"message": "付款成功"
}
}
}), 200
else:
return jsonify({
"data": {
"number": timestamp,
"payment": {
"status": 1,
"message": "付款失敗"
}
}
})
except:
return jsonify({"error": True, "message": "伺服器內部錯誤"}), 500
finally:
closeConnect(mydb, mycursor)
def function():
# 取得使用者資訊
if request.method == 'GET':
sessionUser = session.get("user")
if sessionUser:
return jsonify({"data": sessionUser}), 200
else:
return jsonify({"data": None})
# 註冊
elif request.method == 'POST':
mydb = connection_pool.get_connection()
mycursor = mydb.cursor(dictionary=True)
try:
if mydb.is_connected():
emailRegex = '^(\w|\.|\_|\-)+[@](\w|\_|\-|\.)+[.][A-Za-z]{2,3}$'
pdRegex = '^[A-Za-z0-9]{6,16}'
name = request.form.get('name')
email = request.form.get('email')
password = request.form.get('password')
if re.fullmatch(emailRegex, email) and re.fullmatch(
pdRegex, password):
try:
sql = f"SELECT * FROM users WHERE email = '{email}'"
mycursor.execute(sql)
result = mycursor.fetchone()
# 判斷帳號未被註冊 且 欄位都有填寫 就 註冊
if (not result):
sql = f"INSERT INTO users set name = '{name}', email = '{email}', password = '******'"
mycursor.execute(sql)
mydb.commit()
return jsonify({'ok': True}), 200
else:
return jsonify({
"error": True,
"message": "Email已經被註冊"
}), 400
except:
return jsonify({
"error": True,
"message": "程式內部錯誤"
}), 500
else:
return jsonify({"error": True, "message": "輸入資料格式錯誤"}), 400
finally:
# closing database connection.
closeConnect(mydb, mycursor)
#登入
elif request.method == 'PATCH':
mydb = connection_pool.get_connection()
#將sql指令取得的資料 由tuple改成dict
mycursor = mydb.cursor(dictionary=True)
try:
if mydb.is_connected():
email = request.form.get('email')
password = request.form.get('password')
sql = f"SELECT * FROM users WHERE email = '{email}' AND password = '******'"
mycursor.execute(sql)
user = mycursor.fetchone()
try:
if user:
session["user"] = {
"id": user["id"],
"email": user['email'],
"name": user['name']
}
return jsonify({"ok": True}), 200
else:
return jsonify({
"error": True,
"message": "帳號或密碼錯誤"
}), 400
except:
return jsonify({"error": True, "message": "伺服器錯誤"}), 500
finally:
# closing database connection.
closeConnect(mydb, mycursor)
#登出
elif request.method == 'DELETE':
session["user"] = False
return jsonify({"ok": True})
def booking():
mydb = connection_pool.get_connection()
mycursor = mydb.cursor(dictionary=True)
try:
if mydb.is_connected():
# 未登入時 return 403 error
user = session.get("user")
if not user:
return jsonify({"error": True, "message": "未登入系統，拒絕存取"}), 403
# 依照userid取得booking
if request.method == 'GET':
# 將資料表join後取出要的column 並依照api格式response
sql = f"""SELECT bookings.attraction_id, attractions.name, attractions.address,
attractions_img.img_url, bookings.date, bookings.time, bookings.price
FROM bookings
INNER JOIN attractions ON bookings.attraction_id = attractions.id
INNER JOIN attractions_img ON attractions.id = attractions_img.id
WHERE bookings.user_id = {user['id']} LIMIT 1"""
mycursor.execute(sql)
booking = mycursor.fetchone()
if booking:
return jsonify({
"data": {
"attraction": {
"id": booking["attraction_id"],
"name": booking["name"],
"address": booking["address"],
"image": booking["img_url"]
},
"date": booking["date"],
"time": booking["time"],
"price": booking["price"]
}
}), 200
else:
return jsonify({"data": None}), 200
# 新增booking資料
elif request.method == 'POST':
getFrom = request.form.get
attractionId = getFrom('attractionId')
date = getFrom("date")
time = getFrom("time")
price = getFrom("price")
# 驗證日期範圍為 今天起三個禮拜內且今天不可選
today = datetime.date.today()
dayEnd = today + datetime.timedelta(days=21)
if (today < datetime.date.fromisoformat(date) <= dayEnd):
sql = f"SELECT * FROM bookings WHERE user_id = {user['id']}"
mycursor.execute(sql)
result = mycursor.fetchone()
if result:
sql = f"UPDATE bookings SET attraction_id = {attractionId}, date = '{date}', time = '{time}', price = {price} WHERE user_id = {user['id']}"
else:
sql = f"INSERT INTO bookings set attraction_id = {attractionId}, user_id = {user['id']}, date = '{date}', time = '{time}', price = {price}"
# 回傳response
try:
mycursor.execute(sql)
mydb.commit()
return jsonify({"ok": True}), 200
except:
return jsonify({
"error": True,
"message": "建立失敗，輸入不正確或其他原因"
}), 400
else:
return jsonify({
"error": True,
"message": "請依照規定選擇範圍內日期，今天起三個禮拜內且當天不可選"
}), 400
# 刪除booking資料
elif request.method == 'DELETE':
sql = f"DELETE FROM bookings WHERE user_id = {user['id']}"
try:
mycursor.execute(sql)
mydb.commit()
return jsonify({"ok": True}), 200
except:
return jsonify({"error": True, "message": "刪除失敗"}), 400
finally:
# closing database connection.
closeConnect(mydb, mycursor)