def data_status():
sql = """SELECT web FROM polls_search GROUP BY web"""
results = db.db_select(sql)
list_web = {}
for result in results:
list_web[result[0]] = {}
list_status = {"not-read": 0, "applied": 0, "inadequate": 0, "expired": 0, "other": 0}
for web in list_web:
list_web[web].update(list_status)
sql = """SELECT status, COUNT(status), polls_search.web FROM polls_ad
LEFT JOIN polls_search ON polls_ad.site_id = polls_search.id
GROUP BY status, polls_search.web"""
results = db.db_select(sql)
for result in results:
list_web[result[2]][result[0]] = result[1]
conn = db.db_connection()
for data in list_web:
total = list_web[data]["not-read"] + list_web[data]["applied"] + list_web[data]["inadequate"] + list_web[data]["expired"] + list_web[data]["other"]
sql = """INSERT INTO polls_stat (web, not_read, applied, inadequate, expired, other, total)
VALUES ('{}', {}, {}, {}, {}, {}, {})"""\
.format(data, list_web[data]["not-read"], list_web[data]["applied"], list_web[data]["inadequate"], list_web[data]["expired"], list_web[data]["other"], total)
insert = db.injection_sql(conn, sql)
if insert == "update":
sql = """UPDATE polls_stat SET not_read={}, applied={}, inadequate={}, expired={}, other={}, total={}
WHERE web = '{}'"""\
.format(list_web[data]["not-read"], list_web[data]["applied"], list_web[data]["inadequate"], list_web[data]["expired"], list_web[data]["other"], total, data)
db.injection_sql(conn, sql)
db.db_close(conn)
def get_attraction_by_id(attractionid):
if attractionid == '' or attractionid == '0':
return redirect('/api/attraction/1')
try:
attractionid = int(attractionid)
except:
abort(400, '景點編號不正確')
else:
result = {}
select = f'{select_spot} where id ={attractionid}'
try:
conn, mycursor = db_connect(db)
mycursor.execute(select)
data = list(list(mycursor)[0])
except:
db_close(conn, mycursor)
abort(500, '伺服器錯誤')
else:
column_names = mycursor.column_names #tuple
db_close(conn, mycursor)
spot = spot_handle(data, column_names)
result['data'] = spot #data:{spot}
return jsonify(result), 200
def main():
default_file_path = "todolist.db"
conn = database.db_connect(default_file_path)
root = tk.Tk()
app = ToDo(conn, master=root)
app.mainloop()
database.db_close(conn)
def book():
json_data = {}
if 'id' in session:
if request.method == 'GET':
print('get booking')
if 'data' in session:
json_data['data'] = session['data']
else:
json_data['data'] = None
print(json_data)
elif request.method == 'POST':
print('post booking')
print(request.json)
if 'data' in session:
session.pop('data')
if request.json['date'] == '' or request.json['price'] == None:
abort(400, '建立失敗')
try:
conn, mycursor = db_connect(db)
mycursor.execute(
f'select id,name,address,images from spot where id like {request.json["attractionId"]}'
)
except:
db_close(conn, mycursor)
abort(500)
db_spot = mycursor.fetchone()
column_name = mycursor.column_names
db_close(conn, mycursor)
book_data = {}
attraction = {}
for column, info in zip(column_name, db_spot):
if column == 'images':
attraction['image'] = info.split()[0]
else:
attraction[column] = info
book_data['attraction'] = attraction
book_data['date'] = request.json['date']
book_data['time'] = request.json['time']
book_data['price'] = request.json['price']
session['data'] = book_data
json_data['ok'] = True
elif request.method == 'DELETE':
print('delete booking')
session.pop('data')
print(session)
json_data['ok'] = True
else:
abort(500)
else:
abort(403, '未登入系統')
return jsonify(json_data), 200
def lk(result):
req = requests.get(result[2])
soup = BeautifulSoup(req.content, "html.parser")
ads = soup.find_all('li', class_="result-card")
conn = db.db_connection()
for ad in ads:
link = ad.a['href'].split("?")[0]
title = ad.h3.text.capitalize()
location = ad.find('span', class_="job-result-card__location").text
check_status(result[0], title, location, link, description, conn)
db.db_close(conn)
def ep(result):
req = requests.get(result[2])
soup = BeautifulSoup(req.content, "html.parser")
ads = soup.find_all('li', class_="result")
conn = db.db_connection()
for ad in ads:
link = "{}{}".format("https://candidat.pole-emploi.fr", ad.a['href'])
title = ad.h2.text.replace("\n", "").capitalize()
location = ad.find('p', class_="subtext").text.replace("\n", "")
description = ad.find('p', class_="description").text.replace('"', "")
check_status(result[0], title, location, link, description, conn)
db.db_close(conn)
def get_order(orderNumber):
if 'id' not in session:
abort(403, '未登入系統')
else:
json_data = {}
if orderNumber == '':
conn, mycursor = db_connect(db)
mycursor.execute('SELECT * FROM orders WHERE user_id=%s',
(session["id"], ))
get_all = mycursor.fetchall()
db_close(conn, mycursor)
# print(get_all[0])
print(get_all)
if get_all == []:
json_data['data'] = []
else:
order_list = []
for order in get_all:
data = {}
print('error', order)
trip = json.loads(order[2])
data['number'] = order[0]
data['price'] = trip['price']
data['trip'] = trip['trip']['attraction']['name']
data['date'] = trip['trip']['date']
data['status'] = order[3]
order_list.append(data)
order_list.reverse()
json_data['data'] = order_list
# print(json_data)
else:
conn, mycursor = db_connect(db)
mycursor.execute(
'SELECT number, trip_order, status FROM orders WHERE user_id=%s AND number=%s',
(session["id"], orderNumber))
get_one = mycursor.fetchone()
db_close(conn, mycursor)
if get_one == None:
json_data['data'] = None
else:
order = json.loads(get_one[1])
data = {}
data['number'] = get_one[0]
data['price'] = order['price']
data['trip'] = order['trip']
data['contact'] = order['contact']
data['status'] = get_one[2]
json_data['data'] = data
return jsonify(json_data), 200
def get_attraction():
result = {}
page = request.args.get('page', '')
keyword = request.args.get('keyword', '')
try:
page = int(page)
# 筆數
conn, mycursor = db_connect(db)
mycursor.execute(
f'select count(*) from spot where name like "%{keyword}%"')
num = mycursor.fetchone()[0]
select = select_spot
if keyword != '':
select += f' where name like "%{keyword}%"'
select += f' order by id limit {page*12}, 12'
mycursor.execute(select)
except:
db_close(conn, mycursor)
abort(500)
else:
data = list(mycursor)
column_names = mycursor.column_names #tuple
db_close(conn, mycursor)
spots = []
# print(data)
num_data = len(data)
for i in range(num_data):
dt = list(data[i])
spot = spot_handle(dt, column_names)
spots.append(spot)
result['data'] = spots #data:[{spot1},{spot2}]
if num - (page * 12 + num_data) > 0:
next_page = page + 1
else:
next_page = None
result['nextPage'] = next_page
return jsonify(result), 200
def getallloggedinuserdata():
"""Get the last 24h of data from loggedin users in steam"""
# Get Steam User statistics
jsoncontent = urllib.request.urlopen("http://store.steampowered.com/stats/userdata.json").read()
content = json.loads(jsoncontent)[0]
datapoints = content['data']
#start database connection
database.db_connect()
# deactivate autocommit so script can save the files faster
database.database.set_autocommit(False)
database.database.begin()
for datapoint in datapoints:
userdate = datetime.datetime.utcfromtimestamp(float(datapoint[0])/1000.)
try:
userlogged = database.UserLoggedIn.create(time=userdate,userloggedin=datapoint[1])
except Exception as e:
continue
database.database.commit()
# activate autocommit
database.database.set_autocommit(True)
#close database connection
database.db_close()
def post_slack():
while True:
if os.path.isfile(basPath + filePath + fileName):
jobList = scraping()
con, cur = db_connect()
slack = slackweb.Slack(url="https://hooks.slack.com/services/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")
for jobInfo in jobList:
sql = "SELECT id FROM job_info where url = %s"
cur.execute(sql, (jobInfo[3],))
cur.fetchone()
row = cur.rowcount
if row > 0:
print('重複')
else:
message = "【会社名】" + "\n" + jobInfo[0] + "\n\n" + "【求人タイトル】" + "\n" + jobInfo[1] + "\n\n""【求人内容】" + "\n" + jobInfo[2] + "\n\n" + jobInfo[3]
slack.notify(text=message)
sql = "INSERT INTO job_info (company_name, title, summary, url, created_at) VALUES (%s, %s, %s, %s, now())"
cur.execute(sql, (jobInfo[0],jobInfo[1],jobInfo[2],jobInfo[3],))
con.commit()
db_close(con, cur)
time.sleep(3600)
else:
break
def orders():
if 'id' not in session:
# print(session)
abort(403, '未登入系統')
else:
json_data = {}
if request.method == 'POST':
print('post orders')
number = time.strftime('%Y%m%d%H%M%S', time.localtime()) #訂單號碼
# print(request.json) prime,order
# check date and time
trip_time = request.json['order']['trip']['time']
print(trip_time != 'morning' and trip_time != 'afternoon')
print(compare_date(request.json['order']['trip']['date']))
if (trip_time != 'morning'
and trip_time != 'afternoon') or compare_date(
request.json['order']['trip']['date']):
abort(400, '訂單建立失敗')
# check email
if '@' not in request.json['order']['contact']['email']:
abort(400, '訂單建立失敗')
# create order
try:
conn, mycursor = db_connect(db)
session_id = session['id']
trip_order = str(request.json['order']).replace("'", '"')
mycursor.execute(
"INSERT INTO orders (number, user_id, trip_order, status) VALUES(%s,%s,%s,1)",
(number, session_id, trip_order))
except:
db_close(conn, mycursor)
abort(400, '訂單建立失敗')
else:
conn.commit()
# TapPay API
url = 'https://sandbox.tappaysdk.com/tpc/payment/pay-by-prime'
values = {
"prime":
request.json['prime'],
"partner_key":
partner_key,
"merchant_id":
"engineerProject_ESUN",
"details":
"台北一日遊：" + request.json['order']['trip']['attraction']['name'],
"amount":
request.json['order']['price'],
"cardholder": {
"phone_number": request.json['order']['contact']['phone'],
"name": request.json['order']['contact']['name'],
"email": request.json['order']['contact']['email'],
},
"order_number":
number
}
headers = {
'content-type': 'application/json',
'x-api-key': partner_key
}
data = json.dumps(values).encode('ascii') # data should be bytes
req = urllib.request.Request(url, data, headers)
with urllib.request.urlopen(req) as response:
pay_result = response.read().decode('utf8')
pay_result = json.loads(pay_result)
print(pay_result)
json_data['data'] = {
"number": number,
"payment": {
"status": pay_result['status'],
"message": pay_result['msg']
}
}
if pay_result['status'] == 0:
mycursor.execute(
'UPDATE orders SET status = 0 WHERE number = %s',
(number, ))
conn.commit()
session.pop('data')
print('付款成功')
else:
print('付款失敗')
db_close(conn, mycursor)
return jsonify(json_data), 200
else:
abort(500)
def update_sql():
conn = db.db_connection()
for id_ad in list_id_expired:
sql = """UPDATE polls_ad SET status='expired' WHERE id={}""".format(id_ad)
db.injection_sql(conn, sql)
db.db_close(conn)
def check_user():
json_data = {}
session_keys = ['id', 'name', 'email']
if request.method == 'GET':
print('get user')
if 'id' in session:
print(session)
json_data['data'] = dict(session)
else:
print(session)
json_data['data'] = None
elif request.method == 'POST':
print('post user')
conn, mycursor = db_connect(db)
name = request.json['name']
email = request.json['email']
password = request.json['password']
if '@' not in email:
abort(400, '帳號或密碼格式錯誤或其他原因')
try:
mycursor.execute(select_user + " where email like %s", (email, ))
except:
db_close(conn, mycursor)
abort(500, '伺服器錯誤') #return error
else:
get_first = mycursor.fetchone()
if get_first == None:
print('not exist')
mycursor.execute(
"INSERT INTO user (name,email,password) VALUES (%s,%s,%s)",
(name, email, password))
conn.commit()
#session紀錄
mycursor.execute(
select_user + " where email like %s and password like %s",
(email, password))
get_first = mycursor.fetchone()
for key, value in zip(session_keys, get_first[:3]):
session[key] = value
json_data['ok'] = True
else:
db_close(conn, mycursor)
abort(400, '信箱已有人使用或其他原因') #return error
elif request.method == 'PATCH':
print('patch user')
conn, mycursor = db_connect(db)
email = request.json['email']
password = request.json['password']
if '@' not in email:
db_close(conn, mycursor)
abort(400, '帳號或密碼錯誤或其他原因')
try:
mycursor.execute(
select_user + ' where email like %s and password like %s',
(email, password))
except:
db_close(conn, mycursor)
abort(500, '伺服器錯誤') #return error
else:
get_first = mycursor.fetchone()
db_close(conn, mycursor)
if get_first == None:
abort(400, '帳號或密碼錯誤或其他原因') #return error
else:
for key, value in zip(session_keys, get_first[:3]):
session[key] = value
print('session', session)
json_data['ok'] = True
elif request.method == 'DELETE':
print('delete user')
print(session)
for key in session_keys:
session.pop(key)
if 'data' in session:
session.pop('data')
print('delete', session)
json_data['ok'] = True
else:
abort(400, 'request method error') #return error
return jsonify(json_data), 200
