def signup(ctx, args_str):
"""Sign up for an event."""
user = ctx.message.author
args = args_str.split(DELIMITER)
print(args)
eid = args[0]
if (len(args) == 2):
print("2")
m_status = args[1]
else:
m_status = 1
db_user = session.query(MeetupUser).filter_by(userid=user.id,
event_id=eid).first()
if db_user:
print("if:" + str(db_user.id))
m_user = db_user
m_user.status = m_status
else:
m_user = MeetupUser(userid=user.id, event_id=eid, status=m_status)
m_event = session.query(MeetupEvent).filter_by(id=eid).first()
if not m_event:
return "No event found with ID " + eid
session.add(m_user)
session.commit()
retstring = "<@" + user.id + "> signed up for event ["
retstring += str(m_event.id) + "]: \n"
retstring += "Status: " + str(m_user.status)
return retstring
def edit_mooc(field_id, mooc_id):
"""Edit a MOOC"""
field = session.query(Field).filter_by(id=field_id).first()
mooc = session.query(MOOC).filter_by(id=mooc_id, field_id=field_id).first()
# Verify if he is the user who created it
if mooc.user_id == login_session['user_id']:
if request.method == 'POST':
if request.form.get('title'):
mooc.title = request.form.get('title')
if request.form.get('provider'):
mooc.provider = request.form.get('provider')
if request.form.get('creator'):
mooc.creator = request.form.get('creator')
if request.form.get('level'):
mooc.level = request.form.get('level')
if request.form.get('url'):
mooc.url = request.form.get('url')
if request.form.get('description'):
mooc.description = request.form.get('description')
if request.form.get('image'):
mooc.image = request.form.get('image')
session.add(mooc)
session.commit()
flash('MOOC {} Successfully Edited'.format(mooc.title))
return redirect(url_for('mooc.show_moocs', field_id=field_id))
return render_template('edit_mooc.html', mooc=mooc, field=field)
else:
return jsonify(error={'msg': "You are not the owner of that!!"}), 401
def update_train_rank(user_id):
logger.info('[Account] update_train_rank <User #{}>'.format(user_id))
user_info = session.query(UserInfo).filter_by(user_id=user_id).first()
if user_info is None:
logger.warn(
"[Account] update_train_rank => UserInfo of User #{} does't exists"
.format(user_id))
return
accounts = session.query(Account).filter_by(user_id=user_id)
ranks = []
for account in accounts:
solved, submitted = account.solved, account.submitted
oj_name = account.oj_name
top_account = session.query(Account).filter_by(oj_name=oj_name)\
.order_by(Account.solved.desc(), Account.submitted.desc())\
.first()
max_solved = max(top_account.solved, solved)
if max_solved == 0:
this_rank = 1000
else:
this_rank = (solved / max_solved) * 1000
ranks.append(this_rank)
logger.debug("[Account] update_train_rank <User #{}> {} => {}".format(
user_id, oj_name, this_rank))
end_rank = sum(ranks) / len(ranks)
user_info.train_rank = end_rank
user_info.save()
logger.info("[Account] update_train_rank success <User #{}> => {}".format(
user_id, end_rank))
def init_all():
logger.info(
"[AccountInit] all account which is UPDATING has changed into NOT_INIT"
)
session.query(Account).filter(Account.update_status != AccountStatus.RESET)\
.update({Account.update_status: AccountStatus.NOT_INIT})
session.commit()
def queryLink(request):
reqJson = requestBody(request)
keyStr = reqJson['key']
index = reqJson['index']
tpage = reqJson['tpage']
offset = (index - 1) * tpage
resJson = {"flag": "error", "msg": ""}
try:
querySQL = session.query(CLink).filter(
or_(CLink.l_desc.like('%' + keyStr + '%'),
CLink.l_tag.like('%' + keyStr + '%'),
CLink.l_title.like('%' + keyStr +
'%'))).limit(tpage).offset(offset).all()
totalList = session.query(CLink).filter(
or_(CLink.l_desc.like('%' + keyStr + '%'),
CLink.l_tag.like('%' + keyStr + '%'),
CLink.l_title.like('%' + keyStr + '%'))).count() # 总记录数
data = []
for item in querySQL:
data.append(item.to_json())
resJson['flag'] = "success"
resJson['msg'] = "查询成功"
resJson['data'] = data
resJson['totalList'] = totalList
except exc.InvalidRequestError:
session.rollback()
resJson['msg'] = "查询失败"
except Exception as e:
print(str(type(e)))
resJson['msg'] = repr(e)
session.rollback()
return resJson
def loadTable(self):
_user_bookings = []
# could use list comprehension to keep the syntax prettier but IDK how to do that with sql
# alchemy and I got no time to spend researching that
user_bookings = session.query(Booking).filter(
Booking.user_id == self.CURRENT_USER.id)
# join the tables
for book in user_bookings:
tool = session.query(Tools).filter_by(id=book.tool_id).first()
data = {
"id": book.id,
"booked_date": book.booked_date,
"duration_of_booking": book.duration_of_booking,
"tool_id": book.tool_id,
"user_id": book.user_id,
"delivery": book.delivery,
"tool_name": tool.name,
"tool_daily_price": tool.daily_price,
"tool_half_day_price": tool.half_day_price,
}
# if the customer books a tool for x days + half day we write in in db as x.5
# here we calculate the price
if '.' in book.duration_of_booking:
data['cost'] = (
int(book.duration_of_booking[:book.duration_of_booking.
find('.')]) *
float(tool.daily_price) + float(tool.half_day_price))
else:
data['cost'] = (int(book.duration_of_booking) *
float(tool.daily_price))
try:
return_date = session.query(Returns).filter_by(
booking_id=book.id).first()
if return_date.returned == True:
data['return_date'] = "Returned"
except:
data['return_date'] = datetime.strptime(book.booked_date, '%Y-%m-%d') +\
timedelta(round(float(book.duration_of_booking)))
if book.delivery == True:
data['cost'] += float(tool.delivery_cost)
_user_bookings.append(data)
for booking in _user_bookings:
self.treeview.insert('',
'end',
text=booking['id'],
values=(booking['tool_name'],
booking['booked_date'],
booking['return_date'],
booking['cost'], booking['delivery']))
def get_game(chat_id, game_id=None, timeslot=None) -> Game:
"""Returns Game model for current chat"""
if game_id:
return (session.query(Game).filter_by(id=game_id,
chat_id=chat_id,
expired=False).first())
elif timeslot:
return (session.query(Game).filter_by(timeslot=timeslot,
chat_id=chat_id,
expired=False).first())
def index():
"""Show all CS fields with latest MOOCs"""
fields = session.query(Field).order_by(asc(Field.name)).all()
moocs = session.query(MOOC).order_by(desc(MOOC.id)).all()
# Prevent unauthorized users from modification, They must login first
if 'username' not in login_session:
return render_template('public_index.html', fields=fields, moocs=moocs)
return render_template('index.html', fields=fields, moocs=moocs)
def show_mooc(field_id, mooc_id):
"""Show a MOOC"""
field = session.query(Field).filter_by(id=field_id).first()
mooc = session.query(MOOC).filter_by(id=mooc_id, field_id=field_id).first()
# Prevent unauthorized users from modification, They must login first
if 'username' not in login_session:
return render_template('public_mooc.html', field=field, mooc=mooc)
return render_template('mooc.html', field=field, mooc=mooc)
def categories_json():
"""Return all fields and moocs"""
fields = session.query(Field).all()
fields_list = []
for field in fields:
moocs = session.query(MOOC).filter_by(field_id=field.id).all()
moocs_list = [mooc.serialize for mooc in moocs]
field_moocs = field.serialize
field_moocs['items'] = moocs_list
fields_list.append(field_moocs)
return jsonify(Categories=fields_list)
def create_submit(data):
""" 新建提交数据 """
cur_account = data['account']
has = session.query(Submit)\
.filter_by(run_id=data['run_id'], oj_name=cur_account.oj_name)\
.first()
if has:
return False
new_submit = Submit()
new_submit.pro_id = data['pro_id']
new_submit.run_id = data['run_id']
new_submit.run_time = data['run_time']
new_submit.memory = data['memory']
new_submit.lang = data['lang']
new_submit.memory = data['memory']
new_submit.result = data['result']
new_submit.submitted_at = data['submit_time']
if 'code' in data and data['code']:
new_submit.code = data['code']
new_submit.status = SubmitStatus.GOOD
else:
new_submit.status = SubmitStatus.BROKEN
new_submit.oj_name = cur_account.oj_name
if 'origin_oj' in data and data['origin_oj']:
new_submit.origin_oj = data['origin_oj']
new_submit.user_id = cur_account.user.id
new_submit.user_name = cur_account.user.display_name
new_submit.created_at = datetime.now()
new_submit.save()
# 存入新提交的时候触发
achieve.trigger(new_submit.id)
return True
def get_latest_message(room_id, user_id):
last_message = session.query(Message).\
filter(Message.room_id == room_id).\
filter(Message.user_id == user_id).\
order_by(Message.created_at.desc()).first()
return last_message
def create_submit(data):
cur_account = data['account']
has = session.query(Submit)\
.filter_by(run_id=data['run_id'], oj_name=cur_account.oj_name)\
.first()
if not has:
new_submit = Submit()
new_submit.pro_id = data['pro_id']
new_submit.run_id = data['run_id']
new_submit.submit_time = data['submit_time']
new_submit.run_time = data['run_time']
new_submit.memory = data['memory']
new_submit.lang = data['lang']
new_submit.memory = data['memory']
new_submit.result = data['result']
if data['code']:
new_submit.code = data['code']
new_submit.update_status = SubmitStatus.GOOD
else:
new_submit.update_status = SubmitStatus.BROKEN
new_submit.oj_name = cur_account.oj_name
new_submit.user = cur_account.user
new_submit.user_name = cur_account.user.name
new_submit.save()
return True
def get_room_id_by_member(user_id, friend_user_id):
member = session.query(Member).\
filter((Member.user_id == user_id) | (Member.user_id == friend_user_id)).\
group_by(Member.room_id).\
having(func.count(Member.room_id) > 1).first()
return member.room_id if member else None
def create_data():
""" creates the necessary base data for workshop demo """
# create test_user
user = userStore.create_user('John Doe', '*****@*****.**', 'test_user', 'user123', activated='True')
print('created test user: {}'.format(user))
# load csv's into SQLite
breweries = os.path.join(thisDir, 'breweries.csv')
categories = os.path.join(thisDir, 'categories.csv')
styles = os.path.join(thisDir, 'styles.csv')
# call our function to load data to SQLite
load_csv(Category, categories)
load_csv(Style, styles)
load_csv(Brewery, breweries, created_by=user.id)
# find mankato brewrey and load beers from json file
mankatoBrewery = session.query(Brewery).filter(Brewery.name == 'Mankato Brewery').first()
for beer, photoBlob in get_mankato_beers():
# create new beer first
photo_name = beer.photo_name
del beer.photo_name
# add created by
beer['created_by'] = user.id
newBeer = Beer(**beer)
# create new beer photo
# newBeer.photos.append(create_beer_photo(beer_id=newBeer.id, photo_name=photo_name, data=photoBlob))
newBeer.photos.append(BeerPhotos(**create_beer_photo(photo_name, photoBlob)))
mankatoBrewery.beers.append(newBeer)
# commit db changes
session.commit()
def get_all_players_in_games(update) -> list:
"""Returns all Player objects for games in the current chat"""
all_data = (session.query(Player, Association,
Game).join(Player).join(Game).filter(
Game.chat_id == update.effective_chat.id))
players = set(data[0] for data in all_data)
return sorted(players, key=lambda player: str(player).casefold())
def create_submit(data):
""" 新建提交数据 """
cur_account = data['account']
has = session.query(Submit)\
.filter_by(run_id=data['run_id'], oj_name=cur_account.oj_name)\
.first()
if has:
return False
new_submit = Submit()
new_submit.pro_id = data['pro_id']
new_submit.run_id = data['run_id']
new_submit.run_time = data['run_time']
new_submit.memory = data['memory']
new_submit.lang = data['lang']
new_submit.memory = data['memory']
new_submit.result = data['result']
new_submit.submitted_at = data['submit_time']
if 'code' in data and data['code']:
new_submit.code = data['code']
new_submit.status = SubmitStatus.GOOD
else:
new_submit.status = SubmitStatus.BROKEN
new_submit.oj_name = cur_account.oj_name
if 'origin_oj' in data and data['origin_oj']:
new_submit.origin_oj = data['origin_oj']
new_submit.user_id = cur_account.user.id
new_submit.user_name = cur_account.user.display_name
new_submit.created_at = datetime.now()
new_submit.save()
# 存入新提交的时候触发
achieve.trigger(new_submit.id)
return True
def __get_followed_posts(self, limit, page):
posts = session.query(Posts).join(
followers, (followers.c.followed_id == Posts.user_id)).filter(
followers.c.follower_id == self.id).order_by(
Posts.timestamp.desc()).limit(limit).offset(limit *
int(page))
return posts
def __init__(self, root, *args, **kwargs):
self.FONT = 'Helvetica'
self.TITLE_SIZE = 24
# this is for testing purposes only
# grab the user with the specified id to query for his bookings
self.CURRENT_USER = session.query(Users).filter_by(
id=kwargs['user_id']).first()
# create a new frame
tk.Frame.__init__(self, root)
self.title_label = Label(self,
text="Bookings",
font=(self.FONT, self.TITLE_SIZE))
self.title_label.pack(side='top')
self.return_button = Button(self,
text="Return tool",
command=self.return_tool_frame)
self.return_button.pack(anchor='w')
self.report_button = Button(self, text="Report tool")
self.report_button.pack(anchor='w')
self.createTable()
self.loadTable()
def updateLink(request):
reqJson = requestBody(request)
resJson = {"flag": "error", "msg": "数据更新失败"}
l_id = reqJson.get("l_id")
l_title = reqJson.get("title")
l_url = reqJson.get("url")
l_tag = reqJson.get("tags")
l_desc = reqJson.get("desc")
l_rate = reqJson.get("rate")
l_userid = reqJson.get("userid")
if l_id == None or l_id == "":
resJson['msg'] = "数据ID不存在"
return resJson
if l_userid == None or l_userid == "":
resJson['msg'] = "用户标识ID不存在"
return resJson
try:
queryLink = session.query(CLink).filter(CLink.id == l_id).all()
if len(queryLink) != 0:
queryLink[0].l_title = l_title
queryLink[0].l_url = l_url
queryLink[0].l_tag = l_tag
queryLink[0].l_desc = l_desc
queryLink[0].l_rate = l_rate
session.commit()
resJson["flag"] = "success"
resJson["msg"] = "数据更新成功"
except exc.InvalidRequestError:
session.rollback()
resJson["msg"] = "数据更新失败"
except Exception as e:
print(str(type(e)))
resJson['msg'] = repr(e)
session.rollback()
return resJson
def xls():
workbook = xlsxwriter.Workbook('partners.xlsx')
worksheet = workbook.add_worksheet()
i = 0
worksheet.write(0, 0, 'ID пользователя')
worksheet.write(0, 1, 'Временная зона(МСК)')
worksheet.write(0, 2, 'Партенерская ссылка')
worksheet.write(0, 3, 'Баллы')
worksheet.write(0, 4, 'Баллы за утро')
worksheet.write(0, 5, 'Баллы за вечер')
worksheet.write(0, 6, 'Баллы за активность')
worksheet.write(0, 7, 'Дата начала игры')
worksheet.write(0, 8, 'email')
for us in session.query(User):
i+=1
worksheet.write(i, 0, us.uid)
worksheet.write(i, 1, us.timezone)
worksheet.write(i, 2, us.partner_id)
worksheet.write(i, 3, us.points)
worksheet.write(i, 4, us.points_mor)
worksheet.write(i, 5, us.points_eve)
worksheet.write(i, 6, us.points_act)
worksheet.write(i, 7, us.start_date)
worksheet.write(i, 8, us.email)
workbook.close()
return 'ok'
def mess():
api = current_app.vk_api
filepath = r"txt.csv"
arr1 = []
with open(filepath, "r",encoding='utf-8', newline="") as file:
#читаем файл целиком
reader = csv.reader(file)
for row in reader:
cur_arr = row[5].split(';')
arr1.extend([cur_arr])
l = int(request.args.get('arg'))
timezone = int(request.args.get('timezone'))
for us in session.query(User).filter(User.timezone==timezone):
if not us.uid == 0 and us.bot_activated:
if (datetime.now().hour + 3+us.timezone)>=24:
date = datetime.datetime.now().date()+datetime.timedelta(1)
elif (datetime.now().hour + 3+us.timezone)<0:
date = datetime.datetime.now().date()-datetime.timedelta(1)
else:
date = datetime.datetime.now().date()
k = (us.start_data-date).days
if k!=0 and (l!=6 or us.resp!=0):
api.messages.send(user_id=us.uid, message=arr1[((k-1)*6)+(l-1)+3][0])
if l==6 and k==7:
us.bot_activated = 0
if l==2:
us.resp = 0
return 'oi'
def update_train_rank(user_id):
user = session.query(User).filter_by(id=user_id).first()
logger.info('[Account] update_train_rank #{}'.format(user))
if user is None:
logger.warn("[Account] update_train_rank => UserInfo of #{} does't exists".format(user))
return
ranks = []
# normal_oj => sum(solved) / sum(top_solved)
normal_oj = ['bnu', 'hdu', 'poj', 'vj']
accounts = session.query(Account).filter_by(user_id=user_id)\
.filter(Account.oj_name.in_(normal_oj))
solved_sum = sum([account.solved for account in accounts])
top_account = session.query(func.sum(Account.solved), Account.user_id)\
.filter(Account.oj_name.in_(normal_oj))\
.group_by(Account.user_id)\
.order_by(func.sum(Account.solved).desc())\
.first()
if top_account:
top_solved_sum = int(top_account[0])
this_rank = (solved_sum / top_solved_sum) * 1000 if top_solved_sum > 0 else 1000
ranks.append(this_rank)
else:
ranks.append(1000)
# rating_oj => sum(rating / top_rating for every account)
rating_oj = ['cf', 'bc']
accounts = session.query(Account).filter_by(user_id=user_id) \
.filter(Account.oj_name.in_(rating_oj))
for account in accounts:
rating = account.solved
oj_name = account.oj_name
top_account = session.query(Account).filter_by(oj_name=oj_name)\
.order_by(Account.solved.desc(), Account.submitted.desc())\
.first()
if not top_account:
this_rank = 1000
else:
top_rating = max(top_account.solved, rating)
this_rank = (rating / top_rating) * 1000
ranks.append(this_rank)
# end_rank = sum(ranks)
print(ranks)
user.train_rank = sum(ranks)
user.save()
logger.info("[Account] update_train_rank success #{} => sum({}) => {}".format(user, ranks, sum(ranks)))
def delete_item(self):
id = self.treeview.item(self.treeview.selection(), "text")
session.delete(session.query(Checkout).filter_by(id=id).first())
session.commit()
self.treeview.delete(*self.treeview.get_children())
self.loadTable()
def delete_mooc(field_id, mooc_id):
"""Delete a MOOC"""
field = session.query(Field).filter_by(id=field_id).first()
mooc = session.query(MOOC).filter_by(id=mooc_id, field_id=field_id).first()
# Verify if he is the user who created it
if mooc.user_id == login_session['user_id']:
if request.method == 'POST':
session.delete(mooc)
session.commit()
flash('MOOC {} Successfully Deleted'.format(mooc.title))
return redirect(url_for('mooc.show_moocs', field_id=field_id))
return render_template('delete_mooc.html', mooc=mooc, field=field)
else:
return jsonify(error={'msg': "You are not the owner of that!!"}), 401
def insert_room():
room = Room(name="")
session.add(room)
session.commit()
room_id = session.query(Room.id).first()
return room_id
def deleteLink(request):
reqJson = requestBody(request)
idStr = reqJson['id']
resJson = {"flag": "error", "msg": ""}
try:
session.query(CLink).filter(CLink.id == idStr).delete(
synchronize_session=False)
resJson['flag'] = "success"
resJson['msg'] = "删除成功"
except exc.InvalidRequestError:
session.rollback()
resJson['msg'] = "删除失败"
except Exception as e:
print(str(type(e)))
resJson['msg'] = repr(e)
session.rollback()
return resJson
def get_user_data_by_user_name(owner, user_name):
current_users = []
users = session.query(User).filter(User.name.like(f'%{user_name}%')).all()
for user in users:
if get_friend_state(owner, user.id):
current_users.append(user)
return current_users
def parse():
l = []
for name in session.query(User):
l.append(name.uid)
r = requests.post('http://127.0.0.1:8765/get', json={'list':l})
logging.error(r.text)
return 'ok'
def check_user(uid):
try:
query = session.query(User).filter(User.uid == uid).first()
logging.error('exists '+str(query.uid))
return 'exists'
except AttributeError:
logging.error('ok')
return 'ok'
def add_points():
uid = int(request.args.get('uid'))
query = session.query(User).filter(User.uid == uid).first()
query.points_act=query.points_act+1
query.points = query.points+1
logging.error('ne zach '+str(query.uid))
return 'zachisleno'
def update_code(data):
""" 更新补充缺漏的代码数据 """
cur_account = data['account']
has = session.query(Submit) \
.filter_by(run_id=data['run_id'], oj_name=cur_account.oj_name) \
.first()
if not has:
return False
has.code = data['code']
has.status = SubmitStatus.GOOD
has.save()
return True
def get_available_account():
# 最近更新的忽略掉
all_open = get_all_open_spider()
if len(all_open) == 0:
return
deadline = datetime.now() - timedelta(minutes=settings.FETCH_TIMEDELTA)
cur_account = session.query(Account)\
.filter(Account.oj_name.in_(all_open))\
.filter(~Account.status.in_([AccountStatus.QUEUE,
AccountStatus.STOP,
AccountStatus.UPDATING,
AccountStatus.ACCOUNT_ERROR]))\
.filter(Account.updated_at < deadline)\
.order_by(Account.updated_at.asc())\
.with_for_update(nowait=True)\
.first()
if not cur_account:
session.commit()
return
cur_account.set_status(AccountStatus.QUEUE)
cur_account.save()
return cur_account
def submits(self):
return session.query(Submit)\
.filter_by(user_id=self.user_id, oj_name=self.oj_name) \
.all()
def init_all():
logger.info("[AccountInit] 所有非 [NOT_INIT, STOP] 账号已经重置为 NORMAL")
session.query(Account)\
.filter(~Account.status.in_([AccountStatus.NOT_INIT, AccountStatus.STOP]))\
.update({Account.status: AccountStatus.NORMAL}, synchronize_session=False)
session.commit()
def user(self):
return session.query(User).filter_by(id=self.user_id).first()
def dbtest():
try:
user = session.query(User).first()
except EnvironmentError, e:
print(e.message)
def get_error_submits(account):
return session.query(Submit.run_id, Submit.pro_id) \
.filter_by(user_id=account.user_id, oj_name=account.oj_name,
status=SubmitStatus.BROKEN) \
.all()