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