def sqlSelect(sqlQuery,
value,
oneId=False): #用oneId區別/api/attraction與/api/attractions 資料處理方式
try:
connection_object = connection_pool.get_connection()
with connection_object.cursor() as cursor:
cursor.execute(sqlQuery, value)
sqlresult = cursor.fetchall()
connection_object.close()
responseData = []
for result in sqlresult:
dictData = {}
dictData["id"] = result[0]
dictData["name"] = result[1]
dictData["category"] = result[2]
dictData["description"] = result[3]
dictData["address"] = result[4]
dictData["transport"] = result[5]
dictData["mrt"] = result[6]
dictData["latitude"] = float(result[7])
dictData["longitude"] = float(result[8])
dictData["images"] = json.loads(result[9])
if oneId == True:
return dictData
else:
responseData.append(dictData)
return responseData
except:
return {"error": True, "message": "伺服器錯誤"}
def sqlSelect(sqlQuery, value):
try:
connection_object = connection_pool.get_connection()
with connection_object.cursor() as cursor:
cursor.execute(sqlQuery, value)
sqlresult = cursor.fetchone()
connection_object.close()
return sqlresult
except:
return {"error":True, "message":"伺服器內部錯誤！"}
def insertData(sqlQuery, value):
try:
connection_object = connection_pool.get_connection()
with connection_object.cursor() as cursor:
cursor.execute(sqlQuery, value)
connection_object.commit()
connection_object.close()
return {"ok":True}
except:
return {"error":True, "message":"伺服器內部錯誤！"}
def updateStatus(status, order_serial_number):
sqlQuery = "UPDATE ordering SET payment_status = %s WHERE order_serial_number = %s"
value = (status, order_serial_number)
try:
connection_object = connection_pool.get_connection()
with connection_object.cursor() as cursor:
cursor.execute(sqlQuery, value)
connection_object.commit()
connection_object.close()
return {"ok":True}
except:
return {"error":True, "message":"伺服器內部錯誤！"}
def loginPage():
mydb = connection_pool.get_connection()
mycursor = mydb.cursor(buffered=True)
if request.method == "PATCH":
data = request.get_json()
sqlEmail = data.get('email')
sqlPassword = data['password']
mycursor.execute("SELECT * FROM member WHERE email = '%s'" %
(sqlEmail))
loginResult = mycursor.fetchone()
try:
if loginResult != None:
if sqlPassword == loginResult[3]:
session["memberEmail"] = loginResult[2]
session["memberName"] = loginResult[1]
mydb.close()
return jsonify({
"data": {
"id": loginResult[0],
"name": loginResult[1],
"email": loginResult[2]
}
}), 200
else:
mydb.close()
return jsonify({"error": True, "message": "密碼錯誤"}), 400
except:
mydb.close()
return jsonify({"error": True, "message": "無此帳號"}), 500
elif request.method == "POST":
data = request.get_json()
sqlName = data['name']
sqlEmail = data['email']
sqlPassword = data['password']
mycursor.execute("SELECT * FROM member WHERE email = '%s'" %
(sqlEmail))
registerResult = mycursor.fetchone()
try:
if registerResult == None:
if len(sqlName) == 0 or len(sqlEmail) == 0 or len(
sqlPassword) == 0:
mydb.close()
return jsonify({"error": True, "message": "請填妥所有資料"}), 400
else:
mycursor.execute(
"INSERT INTO member (name, email, password) VALUES (%s, %s, %s)",
(sqlName, sqlEmail, sqlPassword))
mydb.commit()
mydb.close()
return jsonify({"ok": True, "message": "註冊成功, 請重新登入"}), 200
else:
mydb.close()
return jsonify({
"error": True,
"message": "註冊失敗, Email重複註冊",
}), 400
except:
mydb.close()
return jsonify({"error": True, "message": "伺服器內部錯誤"}), 500
elif request.method == "GET":
if "memberEmail" in session:
mydb.close()
return jsonify({
"data": True,
})
else:
mydb.close()
return jsonify({
"data": None,
})
elif request.method == "DELETE":
session.pop("memberEmail", None)
mydb.close()
return jsonify({
"ok": True,
})
def bookingPage():
mydb = connection_pool.get_connection()
mycursor = mydb.cursor(buffered=True)
try:
if "memberEmail" in session:
if request.method == "POST":
result = request.get_json()
# print(result)
bookingId = result["attractionId"]
date = result["date"]
time = result["time2"]
price = result["price"]
email = session["memberEmail"]
# print(email)
if result["date"]:
mycursor.execute(
"INSERT INTO booking (booking_date, booking_time, booking_price, booking_id, member_email) VALUES (%s, %s, %s, %s, %s)",
(date, time, price, bookingId, email))
mydb.commit()
mydb.close()
return jsonify({
"ok": True,
}), 200
else:
mydb.close()
return jsonify({"error": True, "message": "請選取日期"}), 400
if request.method == "GET":
bookingEmail = session["memberEmail"]
mycursor.execute(
"SELECT * FROM booking WHERE member_email = '%s' ORDER BY id DESC LIMIT 1"
% (bookingEmail))
bookingData = mycursor.fetchone()
# print(bookingData)
if bookingData != None:
attId = bookingData[4]
mycursor.execute(
"SELECT * FROM information WHERE id = '%s'" % (attId))
bookingInfoResult = mycursor.fetchone()
mydb.close()
return jsonify({
"data": {
"attraction": {
"id": bookingInfoResult[0],
"name": bookingInfoResult[1],
"address": bookingInfoResult[4],
"image": bookingInfoResult[9].split(",")[0]
},
"date": bookingData[1],
"time": bookingData[2],
"price": bookingData[3]
}
}), 200
else:
mydb.close()
return jsonify({"error": True, "message": "目前沒有任何待預訂的行程"})
if request.method == "DELETE":
deleteEmail = session["memberEmail"]
mycursor.execute(
"DELETE FROM booking WHERE member_email = '%s'" %
(deleteEmail))
mydb.commit()
mydb.close()
return jsonify({"ok": True}), 200
else:
mydb.close()
return jsonify({"error": True})
else:
mydb.close()
return jsonify({"error": True, "message": "請先登入"}), 403
except:
mydb.close
return jsonify({"error": True, "message": "伺服器內部錯誤"}), 500
def orderNumber(orderNumber):
mydb = connection_pool.get_connection()
mycursor = mydb.cursor(buffered=True)
header = {
"content-type": "application/json",
"x-api-key": os.getenv("partnerKey")
}
body = {
"partner_key": os.getenv("partnerKey"),
"filters": {
"bank_transaction_id": orderNumber,
}
}
x = requests.post("https://sandbox.tappaysdk.com/tpc/transaction/query",
data=json.dumps(body), headers=header)
res = json.loads(x.text)
# print(res)
transactionDic = res["trade_records"][0]
# print(transactionDic)
# theOne = next(
# item for item in transactionList if item["bank_transaction_id"] == orderNumber)
# print(theOne)
orderId = json.loads(transactionDic["details"])["id"]
mycursor.execute("SELECT * FROM information WHERE id = '%s'" % (orderId))
orderResult = mycursor.fetchone()
# print(orderResult)
bookingDelete = session["memberEmail"]
mycursor.execute(
"DELETE FROM booking WHERE member_email = '%s'" % (bookingDelete)
)
mydb.commit()
if "memberEmail" in session:
mydb.close()
return jsonify({
"data": {
"price": transactionDic["amount"],
"trip": {
"id": orderId,
"name": orderResult[1],
"address": orderResult[4],
"image": orderResult[9].split(",")[0]
},
"date": json.loads(transactionDic["details"])["date"],
"time": json.loads(transactionDic["details"])["time"]
},
"contact": {
"name": transactionDic["cardholder"]["name"],
"email": transactionDic["cardholder"]["email"],
"phone": transactionDic["cardholder"]["phone_number"]
},
"status": transactionDic["record_status"]
}), 200
else:
mydb.close()
return jsonify({
"error": True,
"message": "未登入系統，拒絕存取"
}), 403
def orders():
mydb = connection_pool.get_connection()
mycursor = mydb.cursor(buffered=True)
data = request.get_json()
# print(data)
prime = data["prime"]
price = data["order"]["price"]
urlId = data["order"]["trip"]["attraction"]["id"]
date = data["order"]["trip"]["date"]
time = data["order"]["trip"]["time"]
name = data["order"]["contact"]["name"]
phone = data["order"]["contact"]["phone"]
email = data["order"]["contact"]["email"]
try:
if "memberEmail" in session:
header = {
"content-type": "application/json",
"x-api-key": os.getenv("partnerKey")
}
body = {
"prime": prime,
"partner_key": os.getenv("partnerKey"),
"merchant_id": os.getenv("merchantId"),
"details": json.dumps({
"id": urlId,
"date": date,
"time": time
}),
"amount": price,
"cardholder": {
"phone_number": phone,
"name": name,
"email": email,
},
}
r = requests.post("https://sandbox.tappaysdk.com/tpc/payment/pay-by-prime",
data=json.dumps(body), headers=header)
result = json.loads(r.text)
print(result)
session["transactionId"] = result["bank_transaction_id"]
if result["status"] == 0:
mydb.close()
return jsonify({
"data": {
"number": result["bank_transaction_id"],
"payment": {
"status": 0,
"message": "付款成功"
}
}
}), 200
else:
mydb.close()
return jsonify({
"error": True,
"message": "訂單建立失敗"
})
else:
mydb.close()
return jsonify({
"error": True,
"message": "未登入系統, 拒絕存取"
}), 403
except:
mydb.close()
return jsonify({
"error": True,
"message": "伺服器內部錯誤"
}), 500