def admin_edit_cities_state(message, user, is_entry=False):
current_cities = session.query(City.name).all()
current_cities = [i for obj in current_cities for i in obj]
if is_entry:
bot.send_message(
message.chat.id,
'Щоб змінити список міст, скопіюйте наступне повідомлення, та відредагуйте його.\nПоточний список міст:',
reply_markup=get_back_keyboard(language=user.language))
bot.send_message(message.chat.id, '\n'.join(current_cities))
else:
if message.text == DICTIONARY[user.language]['back_btn']:
return True, 'admin_settings_state'
else:
new_cities = []
for city in message.text.split('\n'):
if city not in current_cities:
new_cities.append({
'name': city,
'is_regional_center': False
})
session.execute(
insert(City).values(new_cities).on_conflict_do_nothing())
session.commit()
bot.send_message(message.chat.id, 'Список міст оновлено')
return True, 'admin_settings_state'
return False, ''
def delete(self):
request = Category.parser.parse_args()
#delete에 필요한 값인 category pk가 없으면 400 반환
if request['category_pk'] == None:
return Response(status=400)
sql = f"delete from categories where category_pk = {request['category_pk']}"
session.execute(sql)
session.commit()
session.close()
return Response(status=200)
def delete(self):
request = Option.parser.parse_args()
#post에 필요한 값인 'option_pk' 가 없으면 400 반환
if request['option_pk'] == None:
return Response(status=400)
session.execute("DELETE FROM OPTIONS WHERE option_pk = {}".format(
request['option_pk']))
session.commit()
session.close()
return Response(status=200)
def delete(self):
request = Menu.parser.parse_args()
#delete에 필요한 값인 'pk'가 없으면 400 반환
if request['pk'] == None:
return Response(status=400)
sql = f"delete from menus where menu_pk = {request['pk']}"
session.execute(sql)
session.commit()
session.close()
return Response(status=200)
def delete(self):
request = Menu.parser.parse_args()
print(request)
if request['pk'] == None:
return Response(status=400)
sql = f"delete from menus where menu_pk = {request['pk']}"
session.execute(sql)
# menu = session.query(models.Menu).filter(models.Menu.menu_pk == request['pk']).first()
# session.delete(menu)
session.commit()
return Response(status=200)
def parce_events(html):
soup = BeautifulSoup(html, 'html.parser')
items = soup.find_all('article', class_='b-postcard')
events = []
for item in items:
url = item.find('h2', class_='title')
id_site = str(url.find('a').get('href')).split('/')[4]
name = item.find('a').get_text().rstrip().lstrip()
date = item.find('span', class_='date').get_text()
block = item.find('div', class_='when-and-where')
if len(block.find_all('span')) == 2:
price = block.find_all('span')[1].get_text().rstrip().lstrip()
else:
price = ''
event_type = block.get_text().replace(date, '').replace(
price, '').rstrip().lstrip()
description = item.find('p',
class_='b-typo').get_text().rstrip().lstrip()
block = item.find('div', class_='more')
if block.find('span') is not None:
tags = block.get_text().replace(block.find('span').get_text(),
'').rstrip().lstrip()
else:
tags = block.get_text().rstrip().lstrip()
tags = str(tags).split(', ')
event_type = str(event_type).split(', ')
# print(f'name: {name}')
# print(f'date: {date}')
# print(f'price: {price}')
# print(f'event_type: {event_type}')
# print(f'description: {description}')
# print(f'tags: {tags}')
# print(f'id_site: {id_site}')
events.append({
'id_site': id_site,
'name': name,
'date': date,
'price': price,
'type': event_type,
'description': description,
'tags': tags
})
session.execute(insert(Event).values(events).on_conflict_do_nothing())
session.commit()
def get(self):
data = Order.parser.parse_args()
result = None
if data['pk']: # 만약 특정 pk 쿼리라면
order_sql = """
SELECT
ORD.order_pk, DATE_FORMAT(order_time, '%Y-%m-%d %H:%i:%s') as order_time, completed, total_price, menu_name, quantity, option_name
FROM ORDERS ORD
JOIN ORDER_PRODUCTS ORD_PRD USING(order_pk)
LEFT JOIN ORDER_OPTIONS ORD_OP USING (product_pk)
JOIN MENUS M ON (M.menu_pk = ORD_PRD.order_menu_pk)
LEFT JOIN OPTIONS USING (option_pk)
WHERE ORD.order_pk = {pk}
""".format(pk=data['pk'])
else:
order_sql = """
SELECT
ORD.order_pk, DATE_FORMAT(order_time, '%Y-%m-%d %H:%i:%s') as order_time, completed, total_price, menu_name, quantity, ORD_PRD.product_pk, option_name
FROM ORDERS ORD
JOIN ORDER_PRODUCTS ORD_PRD USING(order_pk)
LEFT JOIN ORDER_OPTIONS ORD_OP USING (product_pk)
JOIN MENUS M ON (M.menu_pk = ORD_PRD.order_menu_pk)
LEFT JOIN OPTIONS USING (option_pk)
WHERE ORD.order_time between DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%Y-%m-%d') and DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 SECOND), '%Y-%m-%d %H:%i:%s')
ORDER BY ORD.order_pk, M.menu_pk;
"""
result = session.execute(order_sql).fetchall()
session.close()
result = query_to_dict(result)
if not result[0]: # 쿼리 결과가 없을 경우
return Response(status=404)
result = many_to_one(result)
return {'orderList': result}, 200
def present(username):
query = "SELECT * FROM user_by_username WHERE username = '******';".format(
username)
result = session.execute(query)
for a in result:
return True
return False
def cleanup_data():
try:
session.execute(text("SET FOREIGN_KEY_CHECKS=0;"))
session.commit()
session.execute(text("TRUNCATE TABLE comment;"))
session.commit()
session.execute(text("TRUNCATE TABLE post;"))
session.commit()
session.execute(text("TRUNCATE TABLE user;"))
session.commit()
session.execute(text("SET FOREIGN_KEY_CHECKS=1;"))
session.commit()
LOGGER.success("Successfully reset all data.")
except IntegrityError as e:
LOGGER.error(e.orig)
raise e.orig
except SQLAlchemyError as e:
LOGGER.error(f"Unexpected error when resetting data: {e}")
raise e
def get_stop():
query = request.args.get('stopid', type=int)
select = bus_stops.select(bus_stops.c.stop_id==query)
try:
stop = session.execute(select).fetchall()[0]
except IndexError:
return jsonify({'error': 'stopid not found'})
return jsonify(stop)
def get_poll(username, title):
# Performs a GET request for a poll
try:
query = "SELECT * FROM poll_by_username_and_title WHERE username = '******' AND title = '{}';".format(username, title)
result = session.execute(query)
return create_json_response(result)
except IndexError:
return create_response("Poll with username and title not found", 404)
except Exception:
return create_response("Internal Server Error", 500)
def get_total_people():
station_id = request.args.get("station_id")
from_instant = request.args.get("from")
to_instant = request.args.get("to")
last = request.args.get("last")
query = session.query(StationsHistory)
if station_id is not None:
if session.query(Stations).get(station_id) is None:
return "station does not exists", 404
query = query.filter_by(station_id=station_id)
if from_instant is not None:
try:
from_instant = int(from_instant)
except:
return "from instant is not a integer", 400
if from_instant > int(datetime.now().timestamp()):
return "from_instant in the future", 400
query = query.filter(
StationsHistory.instant > datetime.utcfromtimestamp(from_instant))
if to_instant is not None:
try:
to_instant = int(to_instant)
except:
return "to instant is not a integer", 400
if from_instant is not None and to_instant < from_instant:
return "from_instant is greater than to_instant", 400
query = query.filter(
StationsHistory.instant < datetime.utcfromtimestamp(to_instant))
current_app.logger.debug(query)
if last is not None and (last == 'true' or last == 'True'):
sql = text(
"SELECT station_id as s_id,instant as i,total_people as tp FROM `stations_history` GROUP BY station_id,instant,total_people HAVING instant = (SELECT instant FROM `stations_history` WHERE station_id = s_id ORDER BY instant DESC LIMIT 1) "
)
db_result = session.execute(sql)
result = []
for row in db_result:
result.append({
'instant': row[1],
'station_id': row[0],
'total_people': row[2]
})
return jsonify(result)
return jsonify([record.serialize() for record in query.all()])
def add_user():
# Performs a POST request to add a user
try:
json = request.json
print(json)
username = json["username"]
password = generate_password_hash(json["password"])
if present(username):
return create_response(
"User with provided username already exists", 401)
query = "INSERT INTO user_by_username (id, username, password) VALUES (uuid(), '{}', '{}');".format(
username, password)
print(query)
session.execute(query)
return create_response("User added successfully", 201)
except (AttributeError, KeyError, TypeError):
return create_response("Bad request with improper/incomplete fields",
403)
except Exception:
return create_response("Internal server serror", 500)
def add_poll():
# Performs a POST request for adding a poll
try:
json = request.json
username = json["username"]
title = json["title"]
options = json["options"]
votes = json["votes"]
if present(username, title):
return create_response("Poll with provided username and title already exists", 401)
query = "INSERT INTO poll_by_username_and_title (username, title, id, options, votes) "
query = query + "VALUES ('{}', '{}', uuid(), {}, {});".format(username,
title, options, votes)
session.execute(query)
return create_response("Poll Successfully added", 201)
except (KeyError, AttributeError, TypeError):
return create_response("Bad request with improper/incomplete fields", 403)
except Exception:
return create_response("Internal Server Error", 500)
def update_db(now, pos_now, neg_now):
p_col = "pos" + "{0:02d}".format(now.minute)
n_col = "neg" + "{0:02d}".format(now.minute)
now_id = int(now.strftime("%Y%m%d%H"))
timedata = session.query(TimeData).filter(TimeData.id == now_id).scalar()
if timedata is None:
new_timedata = TimeData()
new_timedata.id = now_id
new_timedata.__dict__[p_col] = pos_now
new_timedata.__dict__[n_col] = neg_now
session.add(new_timedata)
session.commit()
else:
str_sql_pos = "UPDATE negaposi SET " + p_col + " = " + str(
pos_now) + "WHERE id = " + str(now_id)
str_sql_neg = "UPDATE negaposi SET " + n_col + " = " + str(
neg_now) + "WHERE id = " + str(now_id)
update_pos = text(str_sql_pos)
update_neg = text(str_sql_neg)
session.execute(update_pos)
session.execute(update_neg)
session.commit()
return
def get(self):
data = Statistic.parser.parse_args()
# 조회 날짜 설정
start_date = data['startDate']
end_date = data['endDate']
# 전체 주문 데이터 통계
stat_sql = f"""
SELECT
DATE_FORMAT(order_time, '%Y-%m-%d') AS '날짜', count(order_pk) AS '주문 건수', CAST(SUM(total_price) AS signed integer) AS '매출'
FROM ORDERS ORD
WHERE ORD.order_time between DATE_FORMAT(\'{start_date}\', '%Y-%m-%d 00:00:00') and DATE_FORMAT(\'{end_date}\', '%Y-%m-%d 23:59:59')
AND ORD.completed = TRUE
GROUP BY DATE_FORMAT(order_time, '%Y-%m-%d') WITH ROLLUP;
"""
if data['menu']:
# 메뉴 데이터 통계
stat_sql = f"""
SELECT
menu_name AS '메뉴', CAST(SUM(quantity) AS signed integer) AS '개수', CAST(AVG(menu_price) AS signed integer) * CAST(SUM(quantity) AS signed integer) AS '매출'
FROM ORDERS ORD
JOIN ORDER_PRODUCTS ORD_PRD USING(order_pk)
JOIN MENUS M ON (M.menu_pk = ORD_PRD.order_menu_pk)
WHERE ORD.order_time between DATE_FORMAT(\'{start_date}\', '%Y-%m-%d 00:00:00') and DATE_FORMAT(\'{end_date}\', '%Y-%m-%d 23:59:59')
GROUP BY menu_name WITH ROLLUP;
"""
elif data['option']:
# 옵션 데이터 통계
stat_sql = f"""
SELECT
option_name AS '메뉴', CAST(SUM(quantity) AS signed integer) AS '개수', CAST(AVG(option_name) AS signed integer) * CAST(SUM(quantity) AS signed integer) AS '매출'
FROM ORDERS ORD
JOIN ORDER_PRODUCTS ORD_PRD USING(order_pk)
JOIN MENUS M ON (M.menu_pk = ORD_PRD.order_menu_pk)
JOIN ORDER_OPTIONS ORD_OP USING (product_pk)
JOIN OPTIONS USING (option_pk)
WHERE ORD.order_time between DATE_FORMAT(\'{start_date}\', '%Y-%m-%d 00:00:00') and DATE_FORMAT(\'{end_date}\', '%Y-%m-%d 23:59:59')
AND ORD.completed = TRUE
GROUP BY option_name WITH ROLLUP;
"""
result = session.execute(stat_sql).fetchall()
session.close()
result = query_to_dict(result)
return {'data': result}, 200
def get_all_stations():
#result = [skipass.serialize() for skipass in session.query(Stations).all()]
stations = session.query(Stations).all()
sql = text(
"SELECT station_id as s_id,instant as i,total_people as tp FROM `stations_history` GROUP BY station_id,instant,total_people HAVING instant = (SELECT instant FROM `stations_history` WHERE station_id = s_id ORDER BY instant DESC LIMIT 1) "
)
db_result = session.execute(sql)
db_result = list(db_result)
result = []
for station in stations:
temp = None
for s in db_result:
if station.id == s[0] and s[1].date() >= date.today():
temp = station.serialize()
temp['totalPeople'] = s[2]
if temp is None:
temp = station.serialize()
temp['totalPeople'] = 0
result.append(temp)
return jsonify({"data": result})
def verify_user():
# Performs a POST request to verify a user
try:
json = request.json
username = json["username"]
password = json["password"]
query = "SELECT * FROM user_by_username WHERE username = '******';".format(
username)
result = session.execute(query)
for entry in result:
if check_password_hash(entry.password, password):
return create_response("User Verfied", 200)
else:
return create_response("User not verfied", 404)
return create_response("User not found", 404)
except (KeyError, AttributeError, TypeError):
return create_response("Bad request with improper/incomplete fields",
403)
except Exception:
return create_response("Internal Server Error", 500)
def put():
schema = {
'type': 'object',
'properties': {
'walk_id': {
'type': 'integer',
'minimum': 0
}
},
'required': ['walk_id']
}
# return jsonify({'hoge': request.json['walk_id']}), 200
try:
validate(request.json, schema)
except ValidationError as e:
return jsonify({'msg': e.message}), 400
walk = session.query(Walk).filter(
Walk.id == request.json['walk_id']).one_or_none()
if walk is None:
return jsonify({'msg': 'Walk Not Found'}), 404
walk.ended_at = datetime.now()
session.commit()
session.close()
user_id = walk.user_id
sql = 'select from_unixtime(round(unix_timestamp(created_at) div(5 * 60)) *(5 * 60)) as timekey, count(*) from pull, walk where pull.user_id = %s and pull.created_at BETWEEN (SELECT started_at FROM `walk` WHERE user_id = %s order by started_at desc LIMIT 1) and (SELECT ended_at FROM `walk` WHERE user_id = %s order by ended_at desc LIMIT 1) group by timekey;' % (
user_id, user_id, user_id)
sql_res = session.execute(sql)
results = []
for res in sql_res:
results.append({'time': str(res[0]), 'count': res[1]})
return jsonify({'results': results}), 200
def get_detail(id):
if request.args.get('user_id') is None:
return jsonify({'msg': 'user_idがない!!'}), 400
user = session.query(User).filter(User.id == request.args.get('user_id')).one_or_none()
if user is None:
return jsonify({'msg': 'User Not Found'}), 404
walk = session.query(Walk).filter(Walk.id == id).one_or_none()
if walk is None:
return jsonify({'msg': 'Walk Not Found'}), 404
sql = 'select from_unixtime(round(unix_timestamp(created_at) div(5 * 60)) *(5 * 60)) as timekey, count(*) from pull, walk where pull.user_id = %s and pull.created_at BETWEEN (SELECT started_at FROM `walk` WHERE user_id = %s AND id = %s) and (SELECT ended_at FROM `walk` WHERE user_id = %s AND id = %s) group by timekey;' % (user.id, user.id, walk.id, user.id, walk.id)
sql_res = session.execute(sql)
results = []
for res in sql_res:
results.append({'time': '{0:%H:%M}'.format(res[0]), 'count': int(res[1] / 10)})
return jsonify({'results': results}), 200
from database import Base, engine, session
import csv
csv_file = 'event_log.csv'
CsvFile = csv.writer(open(csv_file, 'w'), 'excel')
for row in session.execute('SELECT * FROM event_log'):
print(row)
CsvFile.writerow(row)
csv_file = 'event_types.csv'
CsvFile = csv.writer(open(csv_file, 'w'), 'excel')
for row in session.execute('SELECT * FROM event_types'):
print(row)
CsvFile.writerow(row)
csv_file = 'repository_list.csv'
CsvFile = csv.writer(open(csv_file, 'w'), 'excel')
for row in session.execute('SELECT * FROM repository_list'):
print(row)
CsvFile.writerow(row)
def get_7days_from(start_id):
sql = "SELECT * FROM negaposi WHERE id >= " + str(
start_id) + " ORDER BY id ASC FETCH FIRST 168 ROWS ONLY"
rows = session.execute(sql)
res = json.dumps([(dict(row.items())) for row in rows])
return res
def present(username, title):
query = "SELECT * FROM poll_by_username_and_title WHERE username = '******' AND title = '{}';".format(username, title)
result = session.execute(query)
for a in result:
return True
return False
def wrap(*args, **kwargs):
result = func(*args, **kwargs)
session.execute(result)
session.commit()
session.rollback()
# ------------- Set event types into db -----------
event_types = ['commit', 'issue', 'pull_request', 'comment', 'review']
for i in range(0, len(event_types)):
event_type = Event_type()
event_type.event_type = event_types[i]
try:
session.add(event_type)
session.commit()
except exc.IntegrityError as err:
session.rollback()
# --------------Load events from DB into a dictionary------------------------
select_event = select([Event_type])
event_result = session.execute(select_event)
event_dict = {}
for event_rows in event_result:
event_dict[event_rows['event_type']] = event_rows['id']
# --------------- Fetch all repo from database--------------------------
select_repo = select([Repo])
repo_result = session.execute(select_repo)
# --------- Loop through fetched repos--------------
for repo_row in repo_result:
repo_name = repo_row['name']
repo_organization = repo_row['organization']
